Copying 3 rows of data one under the other in a stacked manner

[qwerty_logic]

Hi All,

I need to create a VBA code to execute the following task:

Current scenario -

• In Sheet1, I have data in column Z to column AQ
• 3 columns starting Column Z forms 1 set, i.e. Col Z, AA, AB is one set; AC, AD, AE will be set 2 and so on (total 6 sets).
• Data in one set has to be copy pasted one under the other (except the heading in row1)
• Final output required in Sheet2 from Cell A2

Thanks in advance !!

 

[Fishboy]

Hi All,

I need to create a VBA code to execute the following task:

Current scenario -

• In Sheet1, I have data in column Z to column AQ
• 3 columns starting Column Z forms 1 set, i.e. Col Z, AA, AB is one set; AC, AD, AE will be set 2 and so on (total 6 sets).
• Data in one set has to be copy pasted one under the other (except the heading in row1)
• Final output required in Sheet2 from Cell A2

Thanks in advance !!

Hi qwerty_logic, welcome to the boards.

If I have understood correctly, then try the following:

Sub TransposeAndStack()
' Defines variables
Dim x As Long, LastRow As Long


' Defines LastRow as the first blank row of data on Sheet2 (assumes headers already exist in row 1)
LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1


' From column Z to AQ, in steps of 3
For x = 26 To 43 Step 3
    ' Copy the firsat 3 rows of each set to the next blank row of Sheet2
    Sheets("Sheet1").Range(Cells(2, x), Cells(4, x + 2)).Copy Sheets("Sheet2").Range("A" & LastRow)
    ' Increase LastRow by 3 to account for the new data
    LastRow = LastRow + 3
' Next set of 3 columns
Next x


End Sub

 

[oldbrewer]

a loop eg for j=26 to 41 step 3 goes to col Z then AC then AF etc
you could store every cell in the row in an array
then move to sheet 2 cell A2 (cells(2,1)
and using a loop from 1 to 18 put them whare you want them

is this broadly what you want ?

 

[oldbrewer]

once again fishboy beats me to it lol

 

[qwerty_logic]

Hi Fishboy,

Thanks for the code.

The code is not running beyond row 19.
Moreover, the entries in every column of Sheet1 contain formula, so I want to use "paste special" option.
May be because of the formulas only I was not able to get output beyond row19.

 

[Fishboy]

Hi Fishboy,

Thanks for the code.

The code is not running beyond row 19.
Moreover, the entries in every column of Sheet1 contain formula, so I want to use "paste special" option.
May be because of the formulas only I was not able to get output beyond row19.

Hi again qwerty_logic,

It is possible I have misunderstood and therefore my code is not quite doing what you are expecting.

Your original description made it sound like you wanted to copy rows 2:4 of each "set" and paste it to sheet 2. By my reckoning that is 18 rows plus the header, which is why it stops at row 19. How many rows of data are on sheet 1? Are we supposed to be copying Z2:AB2, then AC2:AE2, then AF2:AH2 and so on to the last set, then move to row 3, copying each set, then row 4?

Using PasteSpecial is a simple enough tweak to my code, but I would rather get the grouping right at the same time if possible.

 

[Fishboy]

once again fishboy beats me to it lol

I pretty much camp the boards in any quiet time during my working day

 

[qwerty_logic]

Ahh !! My bad..

This is a long dataset.. containing 8027 entries and will increase further in next run

 

[Fishboy]

Ahh !! My bad..

This is a long dataset.. containing 8027 entries and will increase further in next run

OK, and are we copying and stacking the 6 sets from each row (however many that may be)?

Also if this is what the plan is, do you want each set of 6 separated from the next 6 by a blank row, or just continuous?

 

[qwerty_logic]

Yes, We need to copy and stack each row one under the other. The data is similar and we need to apply pivot on the final 3 columns. So no need to add separators after each set.