Alistair has attended:
Excel Advanced course
Loop through a list copying each item on the list to a new works
Hi,
Please let me know how I can loop through a list copying each list item to its own individual worksheet.
I have tried using this the below formula to locate the last row, then tried to cycle through the list from cell A1 to cell A"row" copying each to cell value to its own spreadsheet. Even without the below (say just going from 1 to 5) it doesn't work.
Please, please, please help. I can't even get Excel to recognise what I mean by Cell A "row", or even copying and pasting which should be fairly simple.
Cheers
ALi.
Public Sub LastCellsWithData()
' ExcelLastCell is what Excel thinks is the last cell
Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
' Determine the last row with data in it (must also copy above para for this to work)
LastRowWithData = ExcelLastCell.Row
Row = ExcelLastCell.Row
Do While Application.CountA(ActiveSheet.Rows(Row)) = 0 And Row <> 1
Row = Row - 1
Loop
LastRowWithData = Row' Row number
RE: Loop through a list copying each item on the list to a new w
Something along these lines is what I want to do. (this isn't copying to a new sheet but one step at a time eh!)
Sub Macro9()
Number = 1
Do
Range("A,Number").Select
Selection.Copy
Range("C,Number").Select
ActiveSheet.Paste
Number = Number + 1
Loop Until Number = 5
End Sub
RE: Loop through a list copying each item on the list to a new w
Alistair
Your error is trying to use a variable in a string. "A,Number" is telling the computer to find a row called "number" and not 1.
When using variables you need to use Cells(Row, Column) instead of Range.
To get your loop to work
Number = 1
Do
Cells(Number, 1).Select 'Where 1 is the column "A" and Number the Row
Selection.Copy
Cells(Number, 3).Select 'Where 3 is the column "C"
Selection.Copy
ActiveSheet.Paste
Number = Number + 1
Loop Until Number = 5
To copy to a new sheet You need a counter for the new sheet rows and a line selecting the new sheet.
Regards
Carlos
