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!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,883
Office Version
2007
Platform
Windows
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

Active Member
Joined
Jan 31, 2018
Messages
273
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:

SBF12345

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,883
Office Version
2007
Platform
Windows
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

Active Member
Joined
Jan 31, 2018
Messages
273
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
 

SBF12345

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

Well-known Member
Joined
Dec 3, 2018
Messages
7,883
Office Version
2007
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,475
Office Version
365
Platform
Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,883
Office Version
2007
Platform
Windows
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
 

Forum statistics

Threads
1,078,340
Messages
5,339,646
Members
399,318
Latest member
kryten68

Some videos you may like

This Week's Hot Topics

Top