Change range reference from fixed to last visible entry in copy/paste macro

[shotgun1]

Hi

I'm quite new to VBA coding and have got some code working to copy and paste data from all my workbooks in a folder to a master sheet. However I can't figure out how to adjust the range that is selects to be only the visible filled cells rather than whole "C3:R13" range in each workbook which is a table.

Do I need to define the range as a combination of xlEnd and and xLRight from C3 first?

Any help would be much appreciated

Set SummarySheet = Workbooks("Master Template").Worksheets(1)
 
' Modify this folder path to point to the files you want to use.
FolderPath = "C:\\My Documents\"
 
' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 3
 
' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.xl*")
 
' Loop until Dir returns an empty string.
Do While FileName <> ""
    ' Open a workbook in the folder
   Set WorkBk = Workbooks.Open(FolderPath & FileName)
  
    ' Set the cell in column A to be the file name.
   SummarySheet.Range("A" & NRow).Value = FileName
  
   Set SourceRange = WorkBk.Worksheets("Pricing Summary").Range("C3:R13")
  

   Set DestRange = SummarySheet.Range("A" & NRow)
    Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
    SourceRange.Columns.Count)
  
   DestRange.Value = SourceRange.Value
  
   DestRange.Offset(0, DestRange.Columns.Count).Resize(, 1).Value = WorkBk.Name
 
   NRow = NRow + DestRange.Rows.Count
  
   WorkBk.Close savechanges:=False
  
    ' Use Dir to get the next file name.
   FileName = Dir()
Loop
 
SummarySheet.Columns.AutoFit
End Sub

 

[portews]

Do you have one particular column that ALWAYS has data in it? If so, you can use the following in place of your Set SourceRange line (assuming column D in this example):

Set SourceRange = WorkBk.Worksheets("Pricing Summary").Range("C3:R" & Range("D" & Rows.Count).End(xlUp).Row)

If you can't be sure about any of the columns, you may have to go to a something similar to Chip Pearson's GetLastCell function that will find the last cell to the right and the last cell down and give you that range.

Last Cell In Range

Good Luck!

 

[shotgun1]

Many thanks! That worked perfectly

Do you have one particular column that ALWAYS has data in it? If so, you can use the following in place of your Set SourceRange line (assuming column D in this example):

Set SourceRange = WorkBk.Worksheets("Pricing Summary").Range("C3:R" & Range("D" & Rows.Count).End(xlUp).Row)

If you can't be sure about any of the columns, you may have to go to a something similar to Chip Pearson's GetLastCell function that will find the last cell to the right and the last cell down and give you that range.

Last Cell In Range

Good Luck!