Parsing data to sheets

[cuetipper]

What I would like to do is to read through column g and compare cell by cell to see if there is an existing worksheet in the workbook with named like it.
If so copy that entire row to that worksheet and if not create a new worksheet with that name copy the 1st row of the original worksheet and then add the search row to it.
So if I had a worksheet with 100 entries of ten items I would end up with 11 worksheets, the original and ten more each with ten lines in them from the cell criteria with the sheet named after that criteria.

I started something but wont run. Am I on the right path?



Sub Parse_Sheets()

lr = Range("A1").End(xlDown).Row
'Get Vendor name
For ds = lr To 2 Step -1
shtn = Cells(ds, 7).Value
Rows(ds).Copy
'check existnce of sheet
For Each ws In ThisWorkbook.Worksheets
awsn = ActiveSheet.Name
If awsn <> shtn Then
activesheetname = shtn
Rows(ds).Paste
Sheets(Data).Select
Else
Sheets.Add
activesheetname = shtn
Rows(ds).Paste
Sheets(Data).Select
n1:
Next ds
End Sub

 

[cuetipper]

Thank you guys.
I was running fluffs and as it is working well so I have not yet tried paramray's.
I do have another issue with emailing. And as you are both far more knowledgeable then I am was wondering if you might take a look at it? https://www.mrexcel.com/forum/excel-questions/1077135-send-xls-email-body.html

 

[Fluff]

Glad we could help & thanks for the feedback

 

[ParamRay]

Beg to differ, I very rarely use error handlers.
I could also criticise elements of your code, but (IMO) that's not the purpose of this board.
Also, I don't make inaccurate claims about my code vs somebody else's.

No problem. Depends on the number of distict sheets in column G in both our tests I imagine. And use of error handling probably depends on style. But please tell me which elements you criticise so I can learn from it...

 

[Fluff]

But please tell me which elements you criticise so I can learn from it...

Nothing major.
I Tend to avoid ThisWorkbook, especially when helping people here, as there is no way of knowing where they are going to put the code. But this is more a personal preference than anything else.
The other thing is that you are pulling the row into an array before "pasting" it into the destination sheet. If the OP has formula of formatting that they wish to keep, this will remove them, also (more personal preference) I don't see any benefit over simply using copy destination.


I also notice that you have changed your post#9
My code took 9.8 seconds on 299,500 rows. Yours was 15minutes & counting (before I stopped it)

 

[ParamRay]

My code took 9.8 seconds on 299,500 rows. Yours was 15minutes & counting (before I stopped it)

But what happens if the source data is heavy on formulas? All that use of autofilter is going to get very clunky. Definitely worth switching off auto calculation.

I accept your point on formatting, but not on ThisWorkbook, as it's safer when you don't know what the OP has open at the time.

 

[ParamRay]

I don't make inaccurate claims about my code vs somebody else's... My code took 9.8 seconds on 299,500 rows. Yours was 15minutes & counting (before I stopped it)

This claim is also inaccurate. I just ran a test of both on 300,000 rows. Yours took 01:03 and mine took 01:11.

Admittedly yours ran 8 seconds quicker, but you didn't have to stop it after 15 minutes, unless there was something wrong with your PC.

 

[Fluff]

You're quite right, tried it again today & it ran ok & took 41 seconds.
Xl must have got hung-up on something yesterday.

 

[cuetipper]

Hey guys.. Any chance you can look at this?
https://www.mrexcel.com/forum/excel-questions/1077135-send-xls-email-body.html

 

[cuetipper]

ParamRay,
I went to give your code a try and I am getting the error subscript out of range, any ide why?