Copy sheet from pat to specific sheet

Hi all,

This is a lot to ask but i am really stuck at this point.
I almost completed my file but I am looking for a solution.
I have a partlist for every product (named as Aricle 1,2,3,etc). This file contains 1 sheet with like 20 rows of data. I am looking for a way to copy the data from those files to a sheet in this file.

So: C:\\Test\Partlist\Article1.xlsx must copy to this file, sheet: article 1.


Path of article - Copy to sheet
C:\\Test\Partlist\Article1.xlsx - Article 1
C:\\Test\Partlist\Article2.xlsx - Article 2
C:\\Test\Partlist\Article3.xlsx - Article 3
C:\\Test\Partlist\Article4.xlsx - Article 4
C:\\Test\Partlist\Article5.xlsx - Article 5
C:\\Test\Partlist\Article6.xlsx - Article 6
C:\\Test\Partlist\Article7.xlsx - Article 7
C:\\Test\Partlist\Article8.xlsx - Article 8
Rules VBA
Colom A is the path of the file
Colom B is the sheet (in this file) were it has to copy to.
When button is pushed the article sheets must renew (do delete and rebuild)
(Good to know that every file in path has only 1 sheetname is standard)
All the files are xlsx.


Can someone help me please.

Gives an error on ThisWorkbook.Sheets(strSheet).Select
Should i fill in the path in column A and the sheetname in comlum B?
Should/Can i use header info?

Hi Wouter,

I fixed my problem for 50%...
I found a way to do all what i need to do, but i need a loop.
Do you know how.

Sub openAndCopyPartlist()
    Dim wbCopy As Workbook
    Dim wsCopy As Worksheet
    Dim rngCopy As Range
    Dim wbPaste As Workbook
    Dim wsPaste As Worksheet
    Dim rngPaste As Range
    
    Set wbCopy = Workbooks.Open(ThisWorkbook.Sheets("Master").Range("H2").Value) 'This is Path location C:\\.....xlsx
    Set wsCopy = wbCopy.Worksheets("Blad1") 'Is name of Sheet1
    Set rngCopy = wsCopy.Range("a1:m100").EntireColumn 'Is range to copy
    Set wbPaste = Workbooks("Voorraad beheer.xlsm") 'Is name of the paste location (workbook name)
    Set wsPaste = wbPaste.Worksheets(ThisWorkbook.Sheets("Master").Range("I2").Value)  'This is Path location for the sheet. like test.
    Set rngPaste = wsPaste.Range("a1")  'Past at cell 1
    
    rngCopy.Copy
    rngPaste.PasteSpecial
    wbCopy.Close savechanges:=False
    
    'now i am looking for a way to do this for the complete list H2 to H99 for sheet names: I2 to I99.
    ' Can someone help me how to make it a loop?
    
End Sub

Hi Wouter,

I fixed my problem for 50%...
I found a way to do all what i need to do, but i need a loop.
Do you know how.

Sub openAndCopyPartlist()
    Dim wbCopy As Workbook
    Dim wsCopy As Worksheet
    Dim rngCopy As Range
    Dim wbPaste As Workbook
    Dim wsPaste As Worksheet
    Dim rngPaste As Range
    
    Set wbCopy = Workbooks.Open(ThisWorkbook.Sheets("Master").Range("H2").Value) 'This is Path location C:\\.....xlsx
    Set wsCopy = wbCopy.Worksheets("Blad1") 'Is name of Sheet1
    Set rngCopy = wsCopy.Range("a1:m100").EntireColumn 'Is range to copy
    Set wbPaste = Workbooks("Voorraad beheer.xlsm") 'Is name of the paste location (workbook name)
    Set wsPaste = wbPaste.Worksheets(ThisWorkbook.Sheets("Master").Range("I2").Value)  'This is Path location for the sheet. like test.
    Set rngPaste = wsPaste.Range("a1")  'Past at cell 1
    
    rngCopy.Copy
    rngPaste.PasteSpecial
    wbCopy.Close savechanges:=False
    
    'now i am looking for a way to do this for the complete list H2 to H99 for sheet names: I2 to I99.
    ' Can someone help me how to make it a loop?
    
End Sub