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

Pettel

New Member
Joined
Jan 23, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,705
Office Version
  1. 2013
Platform
  1. Windows
Missing a period in front of Range
Code:
           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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Pettel

New Member
Joined
Jan 23, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
You need a full stop at the start of this line
Rich (BB code):
.Range("C4:R4").Copy
Thank you!🤩
It worked!🥳
But why do I need a full stop there?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
Because it's part of the With Statement, if you don't include the . then it will look at the active sheet
 

Pettel

New Member
Joined
Jan 23, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
ooo...it's like saying

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That's right :)
 

Pettel

New Member
Joined
Jan 23, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It needs to be like
VBA Code:
      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

New Member
Joined
Jan 23, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Yay!!
It works!
Thank you SO MUCH!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,705
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,118,279
Messages
5,571,285
Members
412,375
Latest member
BRJoeyMelo
Top