How to write from multiple Tkinter GUI windows to an Excel file at a time?

As I explained in a comment, the data writing problem is because you need to store the StringVars and related information is lists instead of overwriting the previous values by only using a single-value variables.

While fixing that would be possible without doing so, your use of so many global variables makes the code hard to understand, debug, and modify—which are all reason the common wisdom is to avoid them as much as possible.

Here's a list the ones the code in your question uses:

cluster_name, clustername_entry, vm_template, vmdetails_screen,invalid_value_screen, numofvm_screen, vmnumber, vmnumber_entry,N, main_screen

Since that's a quite a few, I decided to also eliminate them as well in the code below to make the design "cleaner" besides only showing how to store the data in lists as suggested. Hopefully this will provide you with a better foundation for the continued development of your application.

To avoid the globals, most of them have been turned into attributes of an class representing your entire application—which is a design based on this answer to anther tkinter question by tkinter guru @Bryan Oakley.

from tkinter import *import tkinter.messagebox as tkMessageBoximport osimport xlwtclass MyApp(Tk):    LABEL_BG = "light blue"    def __init__(self):        Tk.__init__(self)        self.geometry("300x250")        self.title("Welcome")        Label(text="Select Your Choice", bg=self.LABEL_BG, width="300", height="2",              font=("Calibri", 13)).pack()        Label(text="").pack()        Button(text="Enter", height="2", width="30", command=self.numofvm).pack()        Label(text="").pack()    def numofvm(self):        """ Designing window to provide the number of VMs to be created. """        self.numofvm_screen = Toplevel(self)        self.numofvm_screen.title("VM_NUM")        self.numofvm_screen.geometry("300x250")        self.vmnumber = StringVar()        Label(self.numofvm_screen,              text="Please enter the Number of VMs you wishes to create",              bg=self.LABEL_BG).pack()        Label(self.numofvm_screen, text="").pack()        vmnumber_lable = Label(self.numofvm_screen,                               text="Number of VMs to be created:")        vmnumber_lable.pack()        self.vmnumber_entry = Entry(self.numofvm_screen, textvariable=self.vmnumber)        self.vmnumber_entry.pack()        Button(self.numofvm_screen, text="Submit", width=10, height=1,               command=self.screen_duplicate).pack()        self.numofvm_screen.focus_set()    def screen_duplicate(self):        try:            self.N = int(self.vmnumber.get())        except ValueError:            self.N = 0        if self.N > 0:            self.vm_details()    def vm_details(self):        """ Create designing windows for VM details.        """        # Preallocate and then create VM detail screens and data.        self.cluster_names = [None for _ in range(self.N)]        self.clustername_entries = [None for _ in range(self.N)]        self.vm_templates = [None for _ in range(self.N)]        self.vmdetails_screen = [None for _ in range(self.N)]        for i in range(self.N):            numb = str(i+1)            self.vmdetails_screen[i] = Toplevel()            self.vmdetails_screen[i].title("VM Details " + numb)            self.vmdetails_screen[i].geometry("400x950")            # Set text variables            self.cluster_names[i] = StringVar()            self.vm_templates[i] = StringVar()            # Set label for user's instruction            Label(self.vmdetails_screen[i], text="Please enter details below:",                  bg=self.LABEL_BG).pack()            Label(self.vmdetails_screen[i], text="").pack()            Cluster_Name_lable = Label(self.vmdetails_screen[i],                                       text="Cluster Name "+ numb + ":")            Cluster_Name_lable.pack()            # Set textvariables entry            self.clustername_entries[i] = Entry(self.vmdetails_screen[i],                                                textvariable=self.cluster_names[i])            self.clustername_entries[i].pack()            Button(self.vmdetails_screen[i], text="Submit", width=10, height=1,                   command=self.validate).pack()    def validate(self):        """ Check values of ALL cluster name entries and save them to excel            file if they're all valid.        """        if not all(cluster_name.get() for cluster_name in self.cluster_names):            self.invalid_value()        else:            self.write_to_xls()            tkMessageBox.showinfo("Info", '"%s" file written' % self.xls_filepath)            # Get rid of all data entry screens.            for i in range(self.N):                self.vmdetails_screen[i].destroy()            self.numofvm_screen.destroy()    def invalid_value(self):        """ Display error message screen.        """        self.invalid_value_screen = Toplevel()        self.invalid_value_screen.title("Invalid Entry")        self.invalid_value_screen.geometry("400x100")        Label(self.invalid_value_screen,              text="Please enter valid values for the fields in ALL\n"                   "VM Detail windows before clicking Submit button").pack()        Button(self.invalid_value_screen, text="OK",               command=lambda: self.invalid_value_screen.destroy()).pack()    def write_to_xls(self):        """ Export all user's inputs to an excel sheet. """        # Create new workbook.        wb = xlwt.Workbook()        for i in range(self.N):            numb = str(i+1)            # Add sheet using given name.            ws = wb.add_sheet("VM_" + numb + "_DETAILS")            # Write text to cell.            ws.write(0, 0, "Cluster_Name")            ws.write(1, 0, self.cluster_names[i].get())        # Save excel file in same directory as script.        self.xls_filepath = os.path.join(os.path.dirname(__file__), 'my_file.xls')        wb.save(self.xls_filepath)if __name__ == '__main__':    root = MyApp()    root.mainloop()