Step down a Sheet for Copy

  • Thread starter Thread starter Gerard Goodland
  • Start date Start date
G

Gerard Goodland

Hi all,

I have a workbook with a sheet that has contact info in A1 B1 C1 etc
which my macro copies to another sheet in same workbook and then saves
the sheet to a new workbook and closes. I know need to go back to the
contact worksheet and move down to A2 B2 C2 etc and redo the copy ,
paste and save. The last row in the contact sheet will not be known
because additional contacts may be added. Thanks
 
Gerard,

In general, the techinique is

Dim myRow As Long

With Worksheets("Sheet1")
For myRow = 1 to .Range("A65536").End(xlUp).Row
'column A copied to the second sheet, to cell B2
..Cells(myRow, 1).Copy Worksheets("Sheet2").Range("B2")
'column B copied
..Cells(myRow, 2).Copy Worksheets("Sheet2").Range("C2")
'column C copied
..Cells(myRow, 3).Copy Worksheets("Sheet2").Range("D2")
'When done copying, copy the worksheet to a new file and save, then close it
Worksheets("Sheet2").Copy
Activeworkbook.SaveAs "C:\Folder\Filename" & myRow & ".xls"
Activeworkbook.Close
Next myRow

End With

HTH,
Bernie
MS Excel MVP
 
Back
Top