VBA code to copy various ranges of data multiple times

[ak94]

Hello-

I need help creating vba code that will move, transpose, and stack data from D2:F5 and B9:F12 into B19:H26 based on number of rows and columns in A1:B2.

Any help or suggestions would be appreciated.

 

[Joe4]

Welcome to the Board!

Please show us some sample data and your expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ak94]

I'm confined to using phone so can only upload image

 

[Joe4]

Can you provide an explanation to go along with your images?
What are you starting with?
What is your desired result?
What is the logic for getting there?

 

[ak94]

Can you provide an explanation to go along with your images?
Please see below:
What are you starting with?
We are starting with items in cells A1:F12
What is your desired result?
Desired result is what is in cells B19:H26
What is the logic for getting there?
Logic is as follows:
The Amounts(E9:F12) should be copied(preferably via loop/dynamically based on columns value in B2) and stacked (D19:D26)
The Ref/Type should be stacked as many times as there are columns in B19:C26
The Zip, City, State and Country should be stacked based on rows (B4), but should correspond to column in which they appear

View attachment 110838

 

[Joe4]

Will there always be exactly 2 rows and 4 columns, or is that variable?

 

[ak94]

It will vary

 

[Joe4]

OK, try this:

Sub MyMacro()

    Dim lr As Long, lc As Long
    Dim r As Long, c As Long
    Dim dr As Long
    
    Application.ScreenUpdating = False
    
'   Set initial data row at row 19
    dr = 19
    
'   Get number of row and columns
    lc = Range("B1").Value
    lr = Range("B2").Value
    
'   Loop through all columns starting in column E
    For c = 5 To (lc + 4)
'       Loop through all rows starting at row 9
        For r = 9 To (lr + 8)
'           Populate rows in grid
            Cells(dr, "B").Value = Cells(r, "B").Value
            Cells(dr, "C").Value = Cells(r, "C").Value
            Cells(dr, "D").Value = Cells(r, c).Value
            Cells(dr, "E").Value = Cells(2, c).Value
            Cells(dr, "F").Value = Cells(3, c).Value
            Cells(dr, "G").Value = Cells(4, c).Value
            Cells(dr, "H").Value = Cells(5, c).Value
'           Increment data row counter
            dr = dr + 1
        Next r
    Next c
    
    Application.ScreenUpdating = True
    
End Sub

And here is the proof-of-concept. Everything in black was there initially, and everything in red is what the VBA code created:

 

[alansidman]

Here is a no code method of consolidating with Power Query

 

[ak94]

OK, try this:

Sub MyMacro()

    Dim lr As Long, lc As Long
    Dim r As Long, c As Long
    Dim dr As Long
   
    Application.ScreenUpdating = False
   
'   Set initial data row at row 19
    dr = 19
   
'   Get number of row and columns
    lc = Range("B1").Value
    lr = Range("B2").Value
   
'   Loop through all columns starting in column E
    For c = 5 To (lc + 4)
'       Loop through all rows starting at row 9
        For r = 9 To (lr + 8)
'           Populate rows in grid
            Cells(dr, "B").Value = Cells(r, "B").Value
            Cells(dr, "C").Value = Cells(r, "C").Value
            Cells(dr, "D").Value = Cells(r, c).Value
            Cells(dr, "E").Value = Cells(2, c).Value
            Cells(dr, "F").Value = Cells(3, c).Value
            Cells(dr, "G").Value = Cells(4, c).Value
            Cells(dr, "H").Value = Cells(5, c).Value
'           Increment data row counter
            dr = dr + 1
        Next r
    Next c
   
    Application.ScreenUpdating = True
   
End Sub

And here is the proof-of-concept. Everything in black was there initially, and everything in red is what the VBA code created:
View attachment 110859

thank you x 1000