Step down a Sheet for Copy

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
 
B

Bernie Deitrick

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
 
Top