Loop until won't stop!?

**meacho** · 07-26-2007, 01:28 PM

I've fixed my previous problem, and this formula works except for one thing!

it doesnt stop at the last empty cell it continues to create a new sheet but is unable to name it as ther is nothing in the cell from whihc it takes its name.

why? what have i missed?

thanks

 Sub Macro1()
'
' Macro1 Macro
' Macro recorded 26/07/2007 by Michael Skinner
'
Application.ScreenUpdating = False
Sheets("export").Select
Range("Ar2").Select

Do Until IsEmpty(ActiveCell)

For n = 1 To 100
Dim sSheetName As String
sSheetName = (ActiveCell)

    Sheets("Template").Select
    Sheets("Template").Copy After:=Sheets(1)
    
    Sheets("Template (2)").Select
    Sheets("Template (2)").Name = sSheetName
    
    Sheets("export").Select
    Range("A1").Select
    ActiveCell.Offset(n, 0).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(sSheetName).Select
    Range("E2:AA2").Select
    ActiveSheet.Paste
    
   
    Sheets("export").Select
    Range("b1").Select
    ActiveCell.Offset(n, 0).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(sSheetName).Select
    Range("h10").Select
    ActiveSheet.Paste
    
    Sheets("export").Select
    Range("c1").Select
    ActiveCell.Offset(n, 0).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(sSheetName).Select
    Range("H11").Select
    ActiveSheet.Paste
    
    Sheets("export").Select
    Range("d1").Select
    ActiveCell.Offset(n, 0).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(sSheetName).Select
    Range("E6").Select
    ActiveSheet.Paste

    Sheets("export").Select
    Range("at1").Select
    ActiveCell.Offset(n, 0).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(sSheetName).Select
    Range("e38").Select
    ActiveSheet.Paste
    
Sheets("export").Select
Range("Ar2").Select
ActiveCell.Offset(n, 0).Select

Next n

      
Loop
End Sub

**davesexcel** · 07-26-2007, 02:10 PM

Try changing your counter
This bit of code will count the number of rows in column b, therefore not requiring to say 1 to 100

For i = 1 To Cells(Rows.Count, 2).End(xlUp).Row 'the 2 means column 2
'your code
next i

**meacho** · 07-27-2007, 03:29 AM

Tried this but it still goes past the loop and creates a new sheet which it can't name.

what does the end(x1up) do?

**meacho** · 07-27-2007, 03:49 AM

the value of x1up when it finishes is -4162 but i only have 2 row in this test example? should it not be 65536 less 2?

i have tried clearing the cells below my data but it didnt help?

thanks

**antoka05** · 07-27-2007, 04:13 AM

The instruction

Cells(Rows.Count, 2).End(xlUp).Row 'the 2 means column 2

do the same thing than go to max row of column 2 (for example B65536) then press 'End' and 'Up' to go to the last row of column 2.

To fix your problem try to do so:

lastRow = Sheets("export").Cells(Rows.Count, 2).End(xlUp).Row 'the 2 means column 2

For i = 1 To lastRow
   'your code
next i

Regards,
Antonio

**meacho** · 07-27-2007, 04:44 AM

thanks for the help.

i sorted it buy just using

n=1

my coded

n=n+1
end sub

thanks for all the help its mcuh appreciated

any thoughts on the deleting sheets questions? (see post)

Loop until won't stop!?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Loop until won't stop!?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions