Copy and Paste routine in VBA with two arrays

[SBF12345]

Greetings,

I have a fairly simple copy and paste subroutine to bring some numbers into an excel spreadsheet. The Sub inserts a row on each sheet, copies from another sheet, and then pastes back into the initial sheet. I have about 30 sheets from which to do this with and am confused as to how to structure the code to do this. I would like the array values to be paired by their position so that 'name1' and '123' run on the same loop and 'name2' and '124' run on the same loop.

Dim A As Variant
Dim B as Variant
Dim C as Variant
Dim D as Variant

B = Array("name1","name2"...etc)
D = Array("123","124"...etc)

For Each A in B
Windows("bigbook.xlsx").Activate
Sheets(A).Select
Range("A2").Select
Selection.EntireRow.Insert
Next A

For Each B In D
Windows(B & ".xls").Activate
Range("A2:B2").Select
Selection.Copy

Windows("bigbook.xlsx").Activate
Sheets(A).Select
Range("A2").Select
ActiveSheet.Paste

Next A

Any ideas? Is an array the right tool to use or is there something more effective?

Thanks!

 

[DanteAmor]

Greetings,

I have a fairly simple copy and paste subroutine to bring some numbers into an excel spreadsheet. The Sub inserts a row on each sheet, copies from another sheet, and then pastes back into the initial sheet. I have about 30 sheets from which to do this with and am confused as to how to structure the code to do this. I would like the array values to be paired by their position so that 'name1' and '123' run on the same loop and 'name2' and '124' run on the same loop.

Thanks!

I have some doubts

In Array B you have sheets of the book "bigbook.xlsx"
B = Array("name1","name2"...etc)

But, In Array D, What do you have, are they sheets or books?
D = Array("123","124"...etc)

If they are books, are you going to have all the books open? and from which sheet the data will be copied.
If they are sheets what is the book?

 

[MoshiM]

Greetings,

I have a fairly simple copy and paste subroutine to bring some numbers into an excel spreadsheet. The Sub inserts a row on each sheet, copies from another sheet, and then pastes back into the initial sheet. I have about 30 sheets from which to do this with and am confused as to how to structure the code to do this. I would like the array values to be paired by their position so that 'name1' and '123' run on the same loop and 'name2' and '124' run on the same loop.

Any ideas? Is an array the right tool to use or is there something more effective?

Thanks!

Inside your second loop which sheet is the data being taken from or is there only 1 worksheet in the specified workbook?

Windows(B & ".xls").Activate
Range("A2:B2").Select
Selection.Copy

 

[SBF12345]

To clarify array B is the sheet names in bigbook.xlsx. Array D is the workbook names that correspond to each sheet in array B. What I am attempting to do is to copy material from each workbook (defined in array D) and paste it into the corresponding sheet in bigbook.xlsx(defined in array B). The two loop structure in the code probably won't work that way, I need to be able to incorporate elements from Array B and D in chronological order on the same pass from initial copy to paste. I am struggling to comprehend the architecture and/or methods to do this.

Also, yes, the values in array D are workbook names and the data to be copied all exists on sheet1 of these workbooks.

 

[DanteAmor]

To clarify array B is the sheet names in bigbook.xlsx. Array D is the workbook names that correspond to each sheet in array B. What I am attempting to do is to copy material from each workbook (defined in array D) and paste it into the corresponding sheet in bigbook.xlsx(defined in array B). The two loop structure in the code probably won't work that way, I need to be able to incorporate elements from Array B and D in chronological order on the same pass from initial copy to paste. I am struggling to comprehend the architecture and/or methods to do this.

Also, yes, the values in array D are workbook names and the data to be copied all exists on sheet1 of these workbooks.

Missed this:

are you going to have all the books open?

 

[MoshiM]

To clarify array B is the sheet names in bigbook.xlsx. Array D is the workbook names that correspond to each sheet in array B. What I am attempting to do is to copy material from each workbook (defined in array D) and paste it into the corresponding sheet in bigbook.xlsx(defined in array B). The two loop structure in the code probably won't work that way, I need to be able to incorporate elements from Array B and D in chronological order on the same pass from initial copy to paste. I am struggling to comprehend the architecture and/or methods to do this.

Also, yes, the values in array D are workbook names and the data to be copied all exists on sheet1 of these workbooks.

Try something like this

Sub LOOP9()


    Dim B As Variant
    Dim D As Variant
    Dim WB1 As Workbook, WB2 As Workbook, element As Long
    B = Array("name1", "name2"...etc)
    D = Array("123", "124"...etc)


Set WB1=workbooks("bigbook.xlxs")


    For element = LBOUND(B) To ubound(B)


        WB1.Sheets(B(element)).Range("A2").EntireRow.Insert


        Set WB2=Workbooks(D(element) & ".xls")   


        WB1.Sheets(B(element)).range("A2") = WB2.sheets(1).Range("A2:B2")


    Next element


End Sub

 

[SBF12345]

Yes, all of the workbooks will be open.

Is LBound(B) to UBound(B) an index range? for instance is the first value of LBound(B) = 0 (option base 0)?

 

[DanteAmor]

Yes, all of the workbooks will be open.

Is LBound(B) to UBound(B) an index range? for instance is the first value of LBound(B) = 0 (option base 0)?

Sub test()
    Dim A As Variant, B As Variant, wb As Workbook
    
    Set wb = Workbooks("bigbook.xlsx")
    B = Array("name1", "name2") ' ...etc)
    D = Array("123", "124")             ' ...etc)
    For i = 0 To UBound(B)             'From the initial value of the array to the end
        wb.Sheets(B(i)).Rows(2).Insert
        wb.Sheets(B(i)).Range("A2:B2").Value = Workbooks(D(i) & ".xlsx").Sheets(1).Range("A2:B2").Value
    Next
End Sub

 

[Fluff]

Another option

Sub SBF12345()
    Dim B As Variant, wb As Workbook
    
    Set wb = Workbooks("bigbook.xlsx")
    B = Array("name1", "123", "name2", "124")
    For i = 0 To UBound(B) Step 2
        wb.Sheets(B(i)).Rows(2).Insert
        wb.Sheets(B(i)).Range("A2:B2").Value = Workbooks(B(i + 1) & ".xlsx").Sheets(1).Range("A2:B2").Value
    Next
End Sub

 

[DanteAmor]

I believe that maintaining 2 arrays of 30 items or an array of 60 items will be very complicated within the macro, it is convenient to have the relationship on a sheet as shown below in Custom sheet:

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Custom</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:104.55px;" /><col style="width:124.51px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Sheets</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Books</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >name1</td><td style="text-align:right; ">123</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >name2</td><td style="text-align:right; ">124</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >…</td><td >…</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Until sheet 30</td><td >Until book 30</td></tr></table>

Try with this code:

Sub test()
    Dim sh As Worksheet, wb As Workbook, c As Range
    Set wb = Workbooks("bigbook.xlsx")
    Set sh = ThisWorkbook.Sheets("Custom")
    For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
        wb.Sheets(c.Text).Rows(2).Insert
        wb.Sheets(c.Text).Range("A2:B2").Value = Workbooks(c.Offset(, 1).Text & ".xlsx").Sheets(1).Range("A2:B2").Value
    Next
End Sub