Automatic Range Selection Macro?

[excel_novice2019]

Good morning,

I was wondering if someone could help me with a macro that automatically selects the following ranges for each "block" of data delimited by cells in column A. I have my own macro that will copy each "block" to a new workbook and save it as its own file (once the range is selected; see below), but I want to automate the process of selecting each data "block" down column A.

Ideally, the "Automatic Range Selection Macro" will:

1. Select block 1,
Block 1
Range selection 1.PNG

2. Run my "Move selected range to new workbook & save" macro (see code below).

3. And then continue automatically to block 2 etc...until the last block is selected, copied and saved.
Range Selection 2.PNG

"Move selected range to new workbook & save" macro

Sub CopyandSave()

Dim Path As String
Dim filename As String
Dim xWs As Worksheet
Dim rng As Range

Set rng = Application.Selection
Application.Workbooks.Add
Set xWs = Application.ActiveSheet
rng.Copy Destination:=xWs.Range("A1")

Path = "DESTINATION FOLDER\"
filename = Range("C2")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close

End Sub

M

[mohan.r1980]

May be

Please Login or Register  to view this content.

[:) Sixthsense :)]

Replace this

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

[excel_novice2019]

Replace this

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

I placed this into my code in the OP. When I complied I got: "variable not defined" for r. Is r long or an integer? Thanks

M

[excel_novice2019]

May be

Please Login or Register  to view this content.

Thanks for quick reply, I couldn't get this code to work though.

M

[:) Sixthsense :)]

I placed this into my code in the OP. When I complied I got: "variable not defined" for r. Is r long or an integer?

r As Range

[excel_novice2019]

r As Range

I appreciate the help!

It ran but I received run-time error '91': Object variable or With block variable not set at the underlined line below:

Sub Super()
Dim xWs As Worksheet
Dim rng As Range, iCl As Long, lRw As Long
Dim r As Range

lRw = Range("A" & Rows.Count).End(xlUp).Row
iCl = Cells(1, Columns.Count).End(xlToLeft).Column

For Each r In Range("A1:A" & lRw).SpecialCells(xlCellTypeBlanks).Areas
r.Resize(r.Rows.Count + 1, iCl).Copy
Next r
Application.Workbooks.Add
Set xWs = Application.ActiveSheet
rng.Copy Destination:=xWs.Range("A1")
End Sub

[:) Sixthsense :)]

Please Login or Register  to view this content.

[excel_novice2019]

Please Login or Register  to view this content.

This is perfect! Thanks sixth sense!