Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

microsoft-excel-courses - loop through list copying

Forum home » Delegate support and help forum » Microsoft Excel Training and help » microsoft-excel-courses - Loop through a list copying each item on the list to a new works

microsoft-excel-courses - Loop through a list copying each item on the list to a new works

ResolvedVersion Standard

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

Excel tip:

Using an equal (=) sign that isn't part of a formula

Before you type the equal sign, type an apostrophe: '
Then type your equal sign: = (and anything else you want to add after your equal sign)
Press ENTER.

(the apostraphe will disappear

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.