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.


[TABLE="width: 341"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Path of article[/TD]
[TD]Copy to sheet[/TD]
[/TR]
[TR]
[TD]C:\\Test\Partlist\Article1.xlsx[/TD]
[TD]Article 1[/TD]
[/TR]
[TR]
[TD]C:\\Test\Partlist\Article2.xlsx[/TD]
[TD]Article 2[/TD]
[/TR]
[TR]
[TD]C:\\Test\Partlist\Article3.xlsx[/TD]
[TD]Article 3[/TD]
[/TR]
[TR]
[TD]C:\\Test\Partlist\Article4.xlsx[/TD]
[TD]Article 4[/TD]
[/TR]
[TR]
[TD]C:\\Test\Partlist\Article5.xlsx[/TD]
[TD]Article 5[/TD]
[/TR]
[TR]
[TD]C:\\Test\Partlist\Article6.xlsx[/TD]
[TD]Article 6[/TD]
[/TR]
[TR]
[TD]C:\\Test\Partlist\Article7.xlsx[/TD]
[TD]Article 7[/TD]
[/TR]
[TR]
[TD]C:\\Test\Partlist\Article8.xlsx[/TD]
[TD]Article 8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rules VBA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Colom A is the path of the file[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Colom B is the sheet (in this file) were it has to copy to.[/TD]
[/TR]
[TR]
[TD="colspan: 2"]When button is pushed the article sheets must renew (do delete and rebuild)[/TD]
[/TR]
[TR]
[TD="colspan: 2"](Good to know that every file in path has only 1 sheetname is standard)
All the files are xlsx.


[/TD]
[/TR]
</tbody>[/TABLE]
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