Running through a Config and pulling out the relevant information using keywords into smaller sheets

oitbc

New Member
Joined
Mar 11, 2019
Messages
14
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



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
 

Attachments

  • ExampleInputOutput.PNG
    ExampleInputOutput.PNG
    27.4 KB · Views: 4

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,215,073
Messages
6,122,976
Members
449,095
Latest member
Mr Hughes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top