- Press Windows Logo Key (
) + R to open Run Dialog box.
- Type Excel and Hit on OK button to open Microsoft Excel Application
- Press ALT + F11 (function key) to Open Visual Basic for Application Editor
- Select Insert from Menu bar then select Module from Pop up Window
- then Copy & Paste Below written code in the module
- Place Cursor any where in the code and click on run button (you can also press F5 to run this code).
VBA Code
Option Explicit
Public Sub ConsolidateSheets()
Dim wrkBkDump, wrkBkNewDump As Workbook
Dim LastRow As Long, i As Long, FileDump
Application.DisplayAlerts = False
Application.ScreenUpdating = False
FileDump = Application.GetOpenFilename("Microsoft Excel workbook(*.xl*),(*.xl*)", MultiSelect:=False)
If FileDump <> False Then
Set wrkBkDump = Application.Workbooks.Open(FileDump, ReadOnly:=True)
Set wrkBkNewDump = Excel.Workbooks.Add
With wrkBkNewDump
'.Sheets(Array(1, 2)).Delete
.Sheets(1).Name = "Dump"
End With
For i = 1 To wrkBkDump.Sheets.Count
wrkBkDump.Worksheets(i).AutoFilterMode = False
wrkBkDump.Worksheets(i).Range("A3").CurrentRegion.Copy
With wrkBkNewDump.Worksheets("dump")
LastRow = .Range("B1048576").End(xlUp).Row
If LastRow <> 1 Then
.Range("A" & LastRow + 1).PasteSpecial (xlPasteAll)
Else
.Range("A1").PasteSpecial (xlPasteAll)
End If
Application.CutCopyMode = False
End With
Next
wrkBkNewDump.SaveAs (Replace(FileDump, ".", "_Copy."))
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
consolidate Excel worksheets in python as well
Python Code
import win32com.client as win32
class xlconsolidator():
xlapp = None
xlenum = None
def __init__(self):
self.xlapp = win32.gencache.EnsureDispatch("Excel.Application")
self.xlenum = win32.constants
self.xlapp.Visible = True
def getfile(self):
return self.xlapp.GetOpenFilename("Microsoft Excel workbook(*.xl*),(*.xl*)", MultiSelect=False)
def openworkbook(self, filename):
wb = None
try:
wb = self.xlapp.Workbooks.Open(filename, ReadOnly=True)
except Exception as ex:
print ex.message
return wb
def createworkbook(self):
return self.xlapp.Workbooks.Add()
def consolidatesheets(self, xlwbsource, xlwbtarget):
xlwktarget = xlwbtarget.Worksheets(1)
for xlwksheet in xlwbsource.Worksheets:
lastrow = xlwktarget.Range("A" + str(xlwktarget.Rows.Count)).End(self.xlenum.xlUp).Row
#Remove filter from sheet
xlwksheet.AutoFilterMode = False
#by assuming data is in structred format
xlwksheet.Range("A1").CurrentRegion.Copy
if lastrow != 1:
xlwksheet.Range("A1").CurrentRegion.Copy(xlwktarget.Range("A" + lastrow + 1))
else:
xlwksheet.Range("A1").CurrentRegion.Copy(xlwktarget.Range("A1"))
self.xlapp.CutCopyMode = False
xlwbsource.Close(False)
def saveworkbook(self, wb, newfilename):
wb.SaveAs(newfilename)
def __del__(self):
self.xlapp.Quit()
#self.xlapp.DisplayAlerts = True
#self.xlapp.ScreenUpdating = True
if __name__ == '__main__':
xl = xlconsolidator()
filename = xl.getfile()
wbsrc = xl.openworkbook(filename)
if wbsrc != None:
wbtarget = xl.createworkbook()
xl.consolidatesheets(wbsrc, wbtarget)
xl.saveworkbook(wbtarget, 'c:\\consolidatted Workbook.xlsx')
#filenamewithoutext = os.path.pardir()
) + R to open Run Dialog box.
