VBA copy data from multiple worksheets and paste in a specific, preformatted sheet.

[Pettel]

Hi everyone,
I'm trying to gather information from multiple worksheets into a single preformatted table.
I've started with a dialog folder picker and I know the range that i need to copy from each sheet,but how do I paste what I've copied into a particular range and still have it offset.
For example I copy range "D4:R4" from "sheet1" and Paste it into "E5:S5" of "Sheet1_master", then I open another file and copy range "D4:R4" from "sheet1" but now i need to paste it to "E6:S6" of "Sheet1_master".

This is what I have so far:

Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
    xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
    xFileName = Dir(xFdItem & "*.xls*")
    Do While xFileName <> ""
    With Workbooks.Open(xFdItem & xFileName)
     Sheets("Scotopic A").Select
     Range("D4:R4").Copy Destination:=Sheets("Scotopic A_Master").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
     End With
     xFileName = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

How do I proceed from here?

 

[JLGWhiz]

Missing a period in front of Range

           With wb.Sheets("Scotopic A")
                    .Range("C4:R4").Copy Destination:=Workbooks("Master_ERG.xlsm").Sheets("Scotopic A_Master").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
                    wb.Close False
            End With

 

[Pettel]

You need a full stop at the start of this line

.Range("C4:R4").Copy

Thank you!?
It worked!?
But why do I need a full stop there?

 

[Fluff]

Because it's part of the With Statement, if you don't include the . then it will look at the active sheet

 

[Pettel]

ooo...it's like saying

wb.Sheets("Scotopic A").Range("C4:R4").Copy

 

[Fluff]

That's right

 

[Pettel]

So now I want to duplicate this action and have the code look at the other worksheets and retrieve information from them.
But I get a "Loop without Do" error.

Dim xFd As FileDialog, wb As Workbook
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
        If xFd.Show = -1 Then
            xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
            xFileName = Dir(xFdItem & "*.xls*")
        End If
        Do While xFileName <> ""
        Set wb = Workbooks.Open(xFdItem & xFileName)
        With wb.Sheets("Scotopic A")
        .Range("C4:R4").Copy Destination:=Workbooks("Master_ERG.xlsm").Sheets("Scotopic A_Master").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0)
        wb.Close False
        With wb.Sheets("Scotopic B")
        .Range("C4:R4").Copy Destination:=Workbooks("Master_ERG.xlsm").Sheets("Scotopic B_Master ").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0)
        wb.Close False
                End With
            xFileName = Dir
        Loop
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

It needs to be like

      Do While xFileName <> ""
         Set wb = Workbooks.Open(xFdItem & xFileName)
         With wb.Sheets("Scotopic A")
            .Range("C4:R4").Copy Destination:=Workbooks("Master_ERG.xlsm").Sheets("Scotopic A_Master").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0)
         End With
         With wb.Sheets("Scotopic B")
            .Range("C4:R4").Copy Destination:=Workbooks("Master_ERG.xlsm").Sheets("Scotopic B_Master ").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0)
         End With
         wb.Close False
         xFileName = Dir
      Loop

 

[Pettel]

Yay!!
It works!
Thank you SO MUCH!

 

[JLGWhiz]

Each 'For' must have a 'Next', each 'With' must have an 'End With', each 'While' must have a 'Wend', each 'Do' must have a 'Loop' and each 'If" that uses more than one line must have an 'End If'. Where the 'End' part of the statement occurs in the code will determine if the code in between executes. True will execute all the code in between and False will prevent any of the code from executing. It is all based on logical process. But when one is missing, the VBA error message might indicate the worng one so all of the stanements have to be checked to see which is missing the 'End' statement.

 

[Fluff]

Glad we could help & thanks for the feedback