VBA copy range and paste it to another tab

[Cele]

Hi,
I just typed a macro but for some reason, its not working properly. Can someone help me fix it?

I want to copy what's on the second tab, OrderForm. The range is B1 through F60, and paste it on the first tab Report. And put the cursor on the next available blank cell.

Can someone help me? Thanks.

Celeste

 

[Monty]

Hello Cele.

Quite interesting question..killed my 4 hours of time but happy it's working for "B" column copy and pasting in Report sheet..need to see how to move to d and so on to copy and transpose..

Sub Cele()
Dim LastRow As Long
Dim NextRow As Long
Dim i As Long
    Application.ScreenUpdating = False
    'With Worksheets("OrderForm")
        LastRow = Worksheets("OrderForm").Cells(Worksheets("OrderForm").Rows.Count, "b").End(xlUp).Row
        For i = 1 To LastRow Step 6
            Worksheets("OrderForm").Activate
            Worksheets("OrderForm").Cells(i, "B").Resize(6).Copy
            lrow = Worksheets("Report").Cells(Worksheets("Report").Rows.Count, "A").End(xlUp).Row + 1
            Worksheets("Report").Activate
            Range("A" & lrow).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        Next i
      
Application.ScreenUpdating = True
End Sub

 

[Monty]

Dear Cele.

Finally done..
Just open the workbook and click on the below button in REPORT sheet as shown in the screenshot



Let me know any challenges...Happy to help you.

 

[jamesexcel1970]

Hello Champ.

How did you do that tiny button please.

 

[Monty]

James

That is not a button...I always use shapes for graphical presentation..

You can find in Insert Menu Shapes..

 

[jamesexcel1970]

Oh i See..

 

[Cele]

Thank you!