loop with in loops

[hazer000]

Sheet1 of workbook A, containes a column listing file names stored in a specific folder and two columns over the corresponding row contains a number. This number represents the number of times i need to copy and paste the data in the corresponding file onto sheet 2 of workbook A.

A B C
1 filexyz.xlsm 2
2 fileabc.xlsm 3
3 fileefg.xlsm 1

So in the example above i need to open the first file "filexyz.xlsm" copy the data then go to sheet2 of workbook A and paste the data twice (say into cell A1 and paste and then paste again below the lastrow). Then move to the next file name in the list except this data would get pasted in 3 times. And continue in this way until ive done all the files. I think this requires some sort of loop within a loop but i cant figure out how to put it all together. Thanks for your help.

 

[lrobbo314]

Try This. The code assumes that each of the workbooks that you are opening have a sheet called 'Sheet1'. The code is copying the data from 'Sheet1' in the workbook that is opened, and pasting it to sheet2 of the workbook that has the code in it. You will also need to adjust the 'Path' variable to the directory where the files are located.

Sub Loopy()
Application.ScreenUpdating = False
Dim Path As String: Path = "C:\Users\USERNAME\Desktop\Files\" 'Change this line
Dim r As Range, tmp As Range
Dim main As Worksheet: Set main = Sheets("Sheet1")
Dim ws As Worksheet: Set ws = Sheets("Sheet2")
Dim AR() As Variant: AR = main.Range("A1:C" & main.Range("A" & Rows.Count).End(xlUp).Row).Value
Dim wb As Workbook


For i = 1 To UBound(AR)
    Set wb = Workbooks.Open(Path & AR(i, 1))
    For j = 1 To AR(i, 2)
        Set tmp = wb.Sheets("Sheet1").UsedRange
        ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(tmp.Rows.Count, tmp.Columns.Count).Value = tmp.Value
    Next j
    wb.Close False
Next i
        
Application.ScreenUpdating = True
End Sub