Excel 2003 macro that copies a line in a newly created workbook

Question

I would like to copy the first line of each worksheet from workbook A to a newly created workbook(workbook B). And the second line in the second worksheet of workbook B and so on until all the lines are copied. Can you tell me how to do that?

Answer 1

This is the code that you get when you use the 'Record Macro' function in Excel.

Sub Macro1()
    Windows("Book1").Activate
    Sheets("Sheet1").Select
    Rows("1:1").Select
    Selection.Copy

    Windows("Book2").Activate
    Range("A1").Select
    ActiveSheet.Paste

    Windows("Book1").Activate
    Sheets("Sheet2").Select
    Rows("1:1").Select
    Selection.Copy

    Windows("Book2").Activate
    Range("A2").Select
    ActiveSheet.Paste
End Sub

I only did this for two worksheets in the source, so two lines in the destination. You can see the steps that need to occur. From this point, it's pretty easy to turn this code into a loop and do it for as many sheets/rows as you want. If you want to do it for all sheets in the source workbook, you can use the For each loop.

Dim sht As Worksheet
For Each sht In Worksheets
//do stuff
Next

When you select the next lower row in your destination workbook, don't use the absolute reference style:

Range("A1").Select

Use the Row,Column reference style, then it is easy to increment the rows with a counter:

Cells(1,1).Select

Then just increment the first '1' to drop down to the next row.