Sorry, I posted this on an old question and then realised I should have posted it as a new one as the original was 10 years old.
I need some help to modify the code below please. It's a great bit of code originally from Macro to select rows in between keywords and the to cut and paste into a new sheet but I can't figure out how to tweak it to what I want.
I have a dataset - it's basically a configuration document and what I would like to do is as follows
1) Run through the config, which is all in Col A in an excel sheet, look for a keyword and extract the subset of data including and under the keyword out to the relevant sheet already created dependent on how many rows there are in the dataset.
2) The sheets are Config,1,2,3,4,5+ and relate to how may rows are in the definition
3) Make sure it strips out any row that starts with description from the count, but include it in the transfer to the new location
I've uploaded a screenshot - hopefully making it clear what I am looking to achieve and the original code from above link
Test Data Source
I need some help to modify the code below please. It's a great bit of code originally from Macro to select rows in between keywords and the to cut and paste into a new sheet but I can't figure out how to tweak it to what I want.
I have a dataset - it's basically a configuration document and what I would like to do is as follows
1) Run through the config, which is all in Col A in an excel sheet, look for a keyword and extract the subset of data including and under the keyword out to the relevant sheet already created dependent on how many rows there are in the dataset.
2) The sheets are Config,1,2,3,4,5+ and relate to how may rows are in the definition
3) Make sure it strips out any row that starts with description from the count, but include it in the transfer to the new location
I've uploaded a screenshot - hopefully making it clear what I am looking to achieve and the original code from above link
VBA Code:
Sub SplitConfigFile()
Dim SrcSheet As Worksheet, NewSheet As Worksheet
Dim SrcRange As Range, startCell As Range
'Create new sheets to be used
Set NewSheet1 = Sheets.Add(After:=SrcSheet)
NewSheet1.Name = "1 line defs"
Set NewSheet2 = Sheets.Add(After:=SrcSheet)
NewSheet2.Name = "2 line defs"
Set NewSheet3 = Sheets.Add(After:=SrcSheet)
NewSheet3.Name = "3 line defs"
Set NewSheet4 = Sheets.Add(After:=SrcSheet)
NewSheet4.Name = "4 line defs"
Set NewSheet5 = Sheets.Add(After:=SrcSheet)
NewSheet5.Name = "5+ line defs"
'macro must be run with the data sheet as the currently active sheet
Set SrcSheet = ActiveSheet
Set SrcRange = SrcSheet.Columns(1)
Set startCell = SrcRange.Find(What:="object-group", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)
Do Until startCell Is Nothing
'**** Somehow do a count on this command below and not include any line starting description to get a number, which I can then evaluate in a case statement*****
'I thought I could just set a variable to do X = SrcSheet.Range(startCell.Row & ":" & SrcSheet.Range("A" & SrcSheet.Rows.Count).End(xlUp).Row) and perhaps get a count that way?
SrcSheet.Range(startCell.Row & ":" & SrcSheet.Range("A" & SrcSheet.Rows.Count).End(xlUp).Row).EntireRow.Cut _
NewSheet.Range("A2") 'adjust this range so it writes a blank line and then puts the new text on the next line after it each time - always in Col A on the respective sheet.
'**** Use a case statement to write to the relevant sheet 1,2,3,4,5+ ****
Set startCell = SrcRange.Previous
Loop
End Sub
Test Data Source
Column to be operated on |
object-group 1 |
a |
object-group 2ythiop |
description this is some descriptive text |
c c c |
object-group 3000001 |
d d d d d |
e |
f f f f |
object-group 4 which is a new group |
g |
object-group 5 |
h h h h |
I I I |