sharc316
03-26-2017, 06:30 PM
Hi, I have some code below that consolidates my workbooks and worksheets. The line below in bold (myWS.Range("A2:Z100").Copy) selects a range from each worksheet and pastes it in the master file. I would like this to copy until the last cell of data since the rows are different with each file. Currently it's defined at row 100.
Any help with be apprecited. Thank you.
Option Explicit
Sub ConsolidateDataKarpExprs()
'
Dim MyPath As String
Dim SumPath As String
Dim MyName As String
Dim SumName As String
Dim MyTemplate As String
Dim SumTemplate As String
Dim myWS As Worksheet
Dim sumWS As Worksheet
Dim Last_Row As Long
'Define folders and filenames
MyPath = "C:\Users\Kate\Desktop\FedEx Reporting\Karpathian Express Test\"
SumPath = "C:\Users\Kate\Desktop\FedEx Reporting\"
MyTemplate = "*.xls" 'Set the template.
SumTemplate = "Karpathian Express Master.xlsm"
'Open the template file and get the Worksheet to put the data into
SumName = Dir(SumPath & SumTemplate)
Workbooks.Open SumPath & SumName
Set sumWS = ActiveWorkbook.Worksheets("Linehaul Trips")
'Open each source file, copying the data from each into the template file
MyName = Dir(MyPath & MyTemplate) 'Retrieve the first file
Do While MyName <> ""
'Open the source file and get the worksheet with the data we want.
Workbooks.Open MyPath & MyName
Set myWS = ActiveWorkbook.Worksheets("Linehaul Trips")
'Copy the data from the source and paste at the end of Summary sheet
myWS.Range("A2:Z100").Copy
sumWS.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
'Close the current sourcefile and get the next
Workbooks(MyName).Close SaveChanges:=False 'close
MyName = Dir 'Get next file
Loop
Set sumWS = ActiveWorkbook.Worksheets("Other Settlements Adjustments")
'Open each source file, copying the data from each into the template file
MyName = Dir(MyPath & MyTemplate) 'Retrieve the first file
Do While MyName <> ""
'Open the source file and get the worksheet with the data we want.
Workbooks.Open MyPath & MyName
Set myWS = ActiveWorkbook.Worksheets("Other Settlements Adjustments")
'Copy the data from the source and paste at the end of Summary sheet
myWS.Range("A2:Z106").Copy
sumWS.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
'Close the current sourcefile and get the next
Workbooks(MyName).Close SaveChanges:=False 'close
MyName = Dir 'Get next file
Loop
'Now all sourcefiles are copied into the Template file. Close and save it
'Workbooks(SumName).Close SaveChanges:=True
End Sub
Any help with be apprecited. Thank you.
Option Explicit
Sub ConsolidateDataKarpExprs()
'
Dim MyPath As String
Dim SumPath As String
Dim MyName As String
Dim SumName As String
Dim MyTemplate As String
Dim SumTemplate As String
Dim myWS As Worksheet
Dim sumWS As Worksheet
Dim Last_Row As Long
'Define folders and filenames
MyPath = "C:\Users\Kate\Desktop\FedEx Reporting\Karpathian Express Test\"
SumPath = "C:\Users\Kate\Desktop\FedEx Reporting\"
MyTemplate = "*.xls" 'Set the template.
SumTemplate = "Karpathian Express Master.xlsm"
'Open the template file and get the Worksheet to put the data into
SumName = Dir(SumPath & SumTemplate)
Workbooks.Open SumPath & SumName
Set sumWS = ActiveWorkbook.Worksheets("Linehaul Trips")
'Open each source file, copying the data from each into the template file
MyName = Dir(MyPath & MyTemplate) 'Retrieve the first file
Do While MyName <> ""
'Open the source file and get the worksheet with the data we want.
Workbooks.Open MyPath & MyName
Set myWS = ActiveWorkbook.Worksheets("Linehaul Trips")
'Copy the data from the source and paste at the end of Summary sheet
myWS.Range("A2:Z100").Copy
sumWS.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
'Close the current sourcefile and get the next
Workbooks(MyName).Close SaveChanges:=False 'close
MyName = Dir 'Get next file
Loop
Set sumWS = ActiveWorkbook.Worksheets("Other Settlements Adjustments")
'Open each source file, copying the data from each into the template file
MyName = Dir(MyPath & MyTemplate) 'Retrieve the first file
Do While MyName <> ""
'Open the source file and get the worksheet with the data we want.
Workbooks.Open MyPath & MyName
Set myWS = ActiveWorkbook.Worksheets("Other Settlements Adjustments")
'Copy the data from the source and paste at the end of Summary sheet
myWS.Range("A2:Z106").Copy
sumWS.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
'Close the current sourcefile and get the next
Workbooks(MyName).Close SaveChanges:=False 'close
MyName = Dir 'Get next file
Loop
'Now all sourcefiles are copied into the Template file. Close and save it
'Workbooks(SumName).Close SaveChanges:=True
End Sub