Trying to copy/paste multiple grouped columns across a workbook to another worksheet

Good afternoon. I am trying to write a macro that will take grouped data in columns A&B, C&D, E&F, G&H, ... and paste it one under the other in a new workbook. The data consists of Product Number and Inventory. An example of the data:

Product Inventory Product Inventory Product Inventory
A12314 112 A5588 11 B5587 10
B1158 0 C55478 100 D5587 5

I want to transpose the data onto a new workbook so I see:
Product Inventory
A12314 112
B1158 0
A5588 11
C55478 100
B5587 10
D5587 5

Current vba copies data to new worksheet, but stacks Col A,B,C,D,E,F... in same column.

Sub SortandGroupData()
Application.ScreenUpdating = 0
Dim LC&, i&, ms As Worksheet, j&
Set ms = Sheets("Test2")
 j = ms.Range("IV2").End(xlToLeft).Column  'Finds last column
 With Sheets("Test")

    LC = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    
    For i = 1 To LC

'.Cells(2,i) will remove the top row (title row)
      .Cells(2, i).Resize(.Cells(.Rows.Count, i).End(xlUp).Row).Copy
       ms.Cells(Rows.Count, j).End(xlUp).Offset(0).PasteSpecial xlValues
       
    Next i
 End With
Application.CutCopyMode = 0
Application.ScreenUpdating = True
End Sub

I appreciate any and all help.
Regards,
Marc

But try this:

Sub MReisbe(): Dim r As Long, i As Long, j As Long, ws As Worksheet, wo As Worksheet, O
Set ws = ActiveSheet: r = ws.UsedRange.Rows.count * 4
Set wo = Workbooks.Add(1).Worksheets(1)
ActiveWorkbook.Name = "Output Book": ActiveSheet.Name = "Output" 'or your names
O = wo.Range("A1:B" & r): O(1, 1) = "Product": O(1, 2) = "Inventory": j = 2
For i = 2 To r / 4
O(j, 1) = ws.Range(i, 1): O(j, 2) = ws.Range(i, 2): j = j + 1
O(j, 1) = ws.Range(i, 3): O(j, 2) = ws.Range(i, 4): j = j + 1
O(j, 1) = ws.Range(i, 5): O(j, 2) = ws.Range(i, 6): j = j + 1
O(j, 1) = ws.Range(i, 7): O(j, 2) = ws.Range(i, 8): j = j + 1
Next i
wo.Range("A1:B" & r) = O
End Sub

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

And, you should be ready to go

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)