Copy paste rows (with numbers under column A) from one sheet to another

[sn79]

Dropdowns.pngtoc.png

As shown in the picture, I have a tab TableofContents and a tab Sheet1. I need to copy all rows which have a number under column A, or in this case row 5 (1.01), row 28 (1.02),...in sheet 1 and paste it in that order in the Table of Contents tab starting from cell A5. I don't want any empty rows in between.

Since sheet 1 has all these different tests, is it possible to page break sheet 1 and assign page numbers to the rows pasted in TableofContents tab under column G(eg., starting with cell G5).

[stnkynts]

Try:

Please Login or Register  to view this content.

[sn79]

Thanks for the code. Though, the code only pastes the numbers and not other content in that row. Can I use copy.entirerow in the same code?

[stnkynts]

Yes. Like this

Please Login or Register  to view this content.

[sn79]

Thanks so much stnkynts.
It did work. Now I did realize I have a small problem at hand. The issue is that Sheet 1 is the latest sheet that is generated by a different set of codes using Sheets.Add after:=Sheets(Sheets.Count) and hence for the above code its not necessary that the sheet from which rows have to be copied will be sheet 1. It can be sheet 2 or 3. How do I tell the code to set ws1 to latest worksheet or the worksheet on the extreme right of the workbook. Tried Worksheets(Worksheets.Count).Activate but throws error.

[stnkynts]

Like This:

Please Login or Register  to view this content.

[sn79]

Unfortunately throws a "1004" error on

For Each icell In ws1.Range("A1:A" & LR).SpecialCells(xlCellTypeConstants, xlNumbers)

[stnkynts]

It threw an error because it did not find any numbers in that range. You have done something wrong. You can bypass it by adding the line [On Error Resume Next], just incase there are instances where your source sheet will not have any numbers in column A, but I would not recommend that. Look carefully at the placement of your sheets and make sure that you really want to be accessing the last one.

[sn79]

Hi Stnkynts,
you're absolutely right. I made a small mistake, and moved the sheet somehow and did not notice. It works perfectly. Thanks so much for all your help. I will try to come up with a code to enter page breaks and page numbers in the last sheet (sheet 1 in this case) and then combine it with the matching text (test) on the Table of contents, since I would also need to show page numbers. If I get stuck I will have to bother you again. Thanks again for all your time.