Aug-20-2019, 02:26 PM
I have to copy data from 6 workbooks and paste it into a master workbook. All the workbooks are located in a folder on my desktop: C:\Users\f6565\Desktop\data
The workbooks contain a sheet named 'Main Data', I have to open each workbook, go to sheet 'Main Data’, select columns range A to GJ starting from row 5 to row 'x' (end of the rows), then copy and paste the data range into the master worksheet. In the master worksheet (named MAIN DATA), I paste the data into Column A row 5 till the end and continue pasting/appending the data as I copy data from more workbooks. Eventually, the master workbook has the data in columns A row 5 to GJ from every workbook in one sheet.
The columns range A to GJ and starting from row 5 always remains constant in all the sheets (Main Data) of every workbook. Each workbook contains several sheets, but I am only interested in sheet ‘Main Data’. I have to repeat the same steps for the 6 workbooks and continue pasting/appending the data into master sheet. So, I was wondering if someone could please help me to create a python code for this?
I was able to do this with VB and it works. However I need to be able to do this with python entirely!. Here is my VBA code:
Please let me know if you require any clarification.
Many thanks!
The workbooks contain a sheet named 'Main Data', I have to open each workbook, go to sheet 'Main Data’, select columns range A to GJ starting from row 5 to row 'x' (end of the rows), then copy and paste the data range into the master worksheet. In the master worksheet (named MAIN DATA), I paste the data into Column A row 5 till the end and continue pasting/appending the data as I copy data from more workbooks. Eventually, the master workbook has the data in columns A row 5 to GJ from every workbook in one sheet.
The columns range A to GJ and starting from row 5 always remains constant in all the sheets (Main Data) of every workbook. Each workbook contains several sheets, but I am only interested in sheet ‘Main Data’. I have to repeat the same steps for the 6 workbooks and continue pasting/appending the data into master sheet. So, I was wondering if someone could please help me to create a python code for this?
I was able to do this with VB and it works. However I need to be able to do this with python entirely!. Here is my VBA code:
Sub CopyRange() Application.ScreenUpdating = False Dim wkbDest As Workbook Dim wkbSource As Workbook Set wkbDest = ThisWorkbook Dim LastRow As Long Const strPath As String = "C:\Users\f6565\Desktop\data\" ChDir strPath strExtension = Dir("*.xlsx*") Do While strExtension <> "" Set wkbSource = Workbooks.Open(strPath & strExtension) With wkbSource LastRow = .Sheets("Main Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row .Sheets("Main Data").Range("A5:GJ" & LastRow).Copy wkbDest.Sheets("MAIN DATA").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) .Close savechanges:=False End With strExtension = Dir Loop Application.ScreenUpdating = True End SubI'm not so good with python and would really appreciate your help!
Please let me know if you require any clarification.
Many thanks!