Copy 1st X Rows To Another Worksheet

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Apologies if this has been posted before but I need some help on a macro I am currently working on.

    There is a sales sheet which basically produces a sales receipt and a report sheet which should keep track of all the items sold.

    so i was wondering how a simple do loop or equivalent command could be used to copy about 15 rows (6 cells from each row) from the sales sheet to the report sheet (technically starting from the first available empty row in the report sheet) each time a sales receipt is produced.

    any help will be greatly appreciated!


  • Re: Copy Rows Between Sheets

    Hi you didnt say which 15 rows or whether it would change and you didnt say which 6 cells, however this macro copies the first 15 rows x 6 columns from the Sales sheet and puts them on the Report sheet.

    Sub Macro2()
    Dim Rng As Range
    Set Rng = Sheets("Sales").Range(Cells(1, 1), Cells(15, 6))
    Rng.Copy Destination:=Sheets("Report").Range("A65536").End(xlUp).Offset(1, 0)
    End Sub
  • Re: Copy Rows Between Sheets

    Maybe so but it will only ever copy the first 15 rows! if you have more rows they will be left out, if you anticipate more rows use:

    Set Rng = Sheets("Sales").Range("A2:F" & Range("F" & Rows.Count).End(xlUp).Row)

    this will now copy as many rows as there are, i set it from A2 to allow for a header row.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!