Copy and Paste routine in VBA with two arrays

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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.

Code:
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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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?
 
Upvote 0
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?
Code:
Windows(B & ".xls").Activate
Range("A2:B2").Select
Selection.Copy
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
Code:
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
 
Upvote 0
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)?
 
Upvote 0
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)?


Code:
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
 
Upvote 0
Another option
Code:
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
 
Upvote 0
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:

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
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top