Excel Macros

[roblit]

In a macro I want to repeatedly move every 300 rows of my
spreadsheet into separate worksheets. Upto a maximum of
say 8. I could probably do it with hardcoded cell
addreses but I would prefer to do it with something more
general.
I am using Excel 2000 and XP
many thanks

 

[atrick Molloy]

something along these lines ..paste the code into a
standard code module:-

Sub check()
Dim block As Long
Dim ws As Worksheet
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
With ActiveSheet
For block = 1 To 8
Set ws = Worksheets.Add
.Rows("1:300").Copy
ws.Range("a1").PasteSpecial xlPasteAll
.Rows("1:300").Delete
Next
End With
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With

End Sub

tested OK


Patrick Molloy
Microsoft Excel MVP

 

[Guest]

Thanks Patrick,
how can I stop it going beyond the last row (and so
producing lots of empty tabs)?
Put another way, is it possible to manipulate non hard
coded addreses like "ActiveCell.SpecialCells
(xlLastCell).Select" inside macro code?

 

[Guest]

Hi

use Worksheet.UsedRange to determine the region in which there is anything; if in your loop you get outside of that region you can simply exit the sub as you're done

Hope this helps

Dag

 

[roblit]

Thanks - any chance of an example of its use?

-----Original Message-----
Hi,

use Worksheet.UsedRange to determine the region in which

there is anything; if in your loop you get outside of
that region you can simply exit the sub as you're done.