Copy sheet from pat to specific sheet

[martinus1988]

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.

<colgroup><col><col></colgroup><tbody>
</tbody>

Can someone help me please.

 

[Akuini]

Hi, martinus
The sheets in the list col B, they are already exist, right?
Try this code:

Sub a924855a()
Dim ws As Worksheet
Dim wb1 As Workbook

Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook

For Each r In Range("A2", Cells(Rows.count, "A").End(xlUp))
  Workbooks.Open r.Value
  sheets(1).Cells.Copy wb1.sheets(r.Offset(0, 1).Value).Cells
  Application.CutCopyMode = False
  Debug.Print ActiveWorkbook.Name
  ActiveWorkbook.Close False
Next

Application.ScreenUpdating = True
End Sub