Hi all,
I'm new to the forum and have a problem with my VBA code. Please could someone kindly provide some support with my VBA code, thank you. I have been trying for weeks.
Please see my details below:
Objective:
I have multiple workbooks (~50) each with 50 sheets of data. So these are my source workbooks.
I would like to copy/paste data from each of the sheets within the workbook into a Masterfile with the same sheet names. So this is my destination workbook.
Essentially, copy the data under the last row of each data row per sheet within my Masterfile.
I need the option to open the file first, so I can select it.
So basically I need to:
Copy data from range A2:[Last Column with data] in sheet1 of workbook1 into sheet 1 of Masterfile
Copy data from range A2:[Last Column with data] in sheet2 of workbook1 into sheet 2 of Masterfile
Copy data from range A2:[Last Column with data] in sheet50 of workbook1 into sheet 50 of Masterfile
....
Copy data from range A2:[Last Column with data] in sheet1 of workbook2 into sheet 1 of Masterfile
Copy data from range A2:[Last Column with data] in sheet2 of workbook2 into sheet 2 of Masterfile
Copy data from range A2:[Last Column with data] in sheet50 of workbook1 into sheet 50 of Masterfile
....
Copy data from range A2:[Last Column with data] in sheet1 of workbook50 into sheet 1 of Masterfile
Copy data from range A2:[Last Column with data] in sheet2 of workbook50 into sheet 2 of Masterfile
Copy data from range A2:[Last Column with data] in sheet50 of workbook50 into sheet 50 of Masterfile
Current Status:
So far, my code works very well to copy/paste data into one sheet for one workbook. But I need it to loop through all the sheets in the source and paste them into my Masterfile's corresponding sheets for multiple workbooks.
Please let me know your thoughts or if you need any further information. Thank you Excel community!
My code:
Sub IngestData()
Dim copiedData As Worksheet
Dim Masterfile As Worksheet
Dim lDestLastRow As Long
Dim lCopyLastRow As Long
Dim Ret1
' Get the file from my computer
Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "Please select file")
If Ret1 = False Then Exit Sub
Set copiedData = Workbooks.Open(Ret1).Worksheets("Sheet1")
Set Masterfile = Workbooks("Masterfile").Worksheets("Sheet1")
'Find last used row in the copy range based on data in column A
lCopyLastRow = copiedData.Cells(copiedData.Rows.Count, "A").End(xlUp).Row
'Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = Masterfile.Cells(Masterfile.Rows.Count, "A").End(xlUp).Offset(1).Row
If copiedData.Range("A1") = "Date" Then
copiedData.Range("A2:zz" & lCopyLastRow).Copy _
Masterfile.Range("A" & lDestLastRow)
Workbooks.Open(Ret1).Close SaveChanges:=False
Else
Workbooks.Open(Ret1).Close SaveChanges:=False
MsgBox "No file has been specified. Sorry.", vbExclamation, "Tool"
End If
End Sub
I'm new to the forum and have a problem with my VBA code. Please could someone kindly provide some support with my VBA code, thank you. I have been trying for weeks.
Please see my details below:
Objective:
I have multiple workbooks (~50) each with 50 sheets of data. So these are my source workbooks.
I would like to copy/paste data from each of the sheets within the workbook into a Masterfile with the same sheet names. So this is my destination workbook.
Essentially, copy the data under the last row of each data row per sheet within my Masterfile.
I need the option to open the file first, so I can select it.
So basically I need to:
Copy data from range A2:[Last Column with data] in sheet1 of workbook1 into sheet 1 of Masterfile
Copy data from range A2:[Last Column with data] in sheet2 of workbook1 into sheet 2 of Masterfile
Copy data from range A2:[Last Column with data] in sheet50 of workbook1 into sheet 50 of Masterfile
....
Copy data from range A2:[Last Column with data] in sheet1 of workbook2 into sheet 1 of Masterfile
Copy data from range A2:[Last Column with data] in sheet2 of workbook2 into sheet 2 of Masterfile
Copy data from range A2:[Last Column with data] in sheet50 of workbook1 into sheet 50 of Masterfile
....
Copy data from range A2:[Last Column with data] in sheet1 of workbook50 into sheet 1 of Masterfile
Copy data from range A2:[Last Column with data] in sheet2 of workbook50 into sheet 2 of Masterfile
Copy data from range A2:[Last Column with data] in sheet50 of workbook50 into sheet 50 of Masterfile
Current Status:
So far, my code works very well to copy/paste data into one sheet for one workbook. But I need it to loop through all the sheets in the source and paste them into my Masterfile's corresponding sheets for multiple workbooks.
Please let me know your thoughts or if you need any further information. Thank you Excel community!
My code:
Sub IngestData()
Dim copiedData As Worksheet
Dim Masterfile As Worksheet
Dim lDestLastRow As Long
Dim lCopyLastRow As Long
Dim Ret1
' Get the file from my computer
Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "Please select file")
If Ret1 = False Then Exit Sub
Set copiedData = Workbooks.Open(Ret1).Worksheets("Sheet1")
Set Masterfile = Workbooks("Masterfile").Worksheets("Sheet1")
'Find last used row in the copy range based on data in column A
lCopyLastRow = copiedData.Cells(copiedData.Rows.Count, "A").End(xlUp).Row
'Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = Masterfile.Cells(Masterfile.Rows.Count, "A").End(xlUp).Offset(1).Row
If copiedData.Range("A1") = "Date" Then
copiedData.Range("A2:zz" & lCopyLastRow).Copy _
Masterfile.Range("A" & lDestLastRow)
Workbooks.Open(Ret1).Close SaveChanges:=False
Else
Workbooks.Open(Ret1).Close SaveChanges:=False
MsgBox "No file has been specified. Sorry.", vbExclamation, "Tool"
End If
End Sub