Insert new sheets...

[JayL]

All -
I have a rather large spreadsheet and want to divide it by moving pieces to
a new tab (Sheet) in the same workbook.
I would like to scan Column A for 2 empty rows together. Then continue until
there are 2 more empty rows, cut and paste what resides between these rows
to a new sheet and continue down the original sheet until exhausted.
Example:
R1 blank
R2 blank
R3 - R6 data
R7 & 8 blank
R9-20 data
R21 22 blank
R23 - 44 data

Objective: create a new worksheet from R3-R6, another worksheet from R9-R20,
another worksheet from R23-R44

Thanks!

 

[smithb2]

I have done some research and came up with the following. Due to my lack of VBA knowledge, the following code is not the most efficient but I hope it helps.

You will have to insert the name of the sheet all your data is in into line 1 &7 and change the integer in line 6 to the number of data groups you have, separated by blank rows.

Option Explicit
Dim i As Integer

Sub MoveData()
Worksheets("CTS").Range("A1").Activate
ActiveCell.CurrentRegion.Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Do While i <= 3
Worksheets("CTS").Activate
Selection.Clear
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
Loop
Do While ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.CurrentRegion.Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
i = i + 1
Loop
End Sub



Any Qs, feel free to e-mail me.

Have a great day,
Smithb2
(e-mail address removed)