VBA to copy data from files on a list and paste to specific sheet

[aholmbach]

Hi and Happy New Year
I'm trying to create a macro with the purpose as follows:

Open a specific file based on a path in a list (Path, coloum B), copy data in the file from an area (A1:AO5000), paste it to a specific sheet in the Masterfile, sheetname comes from SheetName, coloum A, same row as the path, close file without saving

Continue to next row, untill the macro has looped through all rows on the list

The number of sheets/files varies from month to month.

A PowerQuery creates the list on which SheetName and Path are mentioned.
Based on the list, a macro creates the sheets in which data should be copied to.

I have managed to create a macro which opens all files on the list, but are missing the knowledge of the copy/paste part to the correct sheet, and closing the opened files without saving them

Hopefully someone knows a lot more about VBA than I done

Thanks a lot in advance

Happy New Year

 

[JEC]

I assumed that:

- This table is on sheet 1
- The sheetnames from which you have to copy are the same as where to paste

Sub jec()
 Dim ar As Variant, i As Long
 ar = Sheets(1).Cells(1, 1).CurrentRegion
  
  For i = 2 To UBound(ar)
    With GetObject(ar(i, 2)).Sheets(ar(i, 1))
       ThisWorkbook.Sheets(ar(i, 1)).Range("A1:AO5000").Value = .Range("A1:AO5000").Value
      .Parent.Close 0
    End With
  Next
End Sub

 

[aholmbach]

I assumed that:

- This table is on sheet 1
- The sheetnames from which you have to copy are the same as where to paste

Sub jec()
 Dim ar As Variant, i As Long
 ar = Sheets(1).Cells(1, 1).CurrentRegion
 
  For i = 2 To UBound(ar)
    With GetObject(ar(i, 2)).Sheets(ar(i, 1))
       ThisWorkbook.Sheets(ar(i, 1)).Range("A1:AO5000").Value = .Range("A1:AO5000").Value
      .Parent.Close 0
    End With
  Next
End Sub

Hi JEC
Correct assumptions.
It works as it should, thank you very-very-very much for your help, its highly appreciated

 

[JEC]

Nice! You're welcome!