Copy Data to a new tab same sheet

[marcidee]

I have a sheet that I need to send to a client each month - they require each person as seen in column F (Adam , Alaco) in a new tab in the same spreadsheet (there are numerous names in the one sheet) - so in the example below all the data for Adam will go into one sheet, all the data for Alaco will go into the next sheet and Beatrice in the next sheet and so on (same file).


Is there a script that will create a new tab for each person in column F and copy or move the data into that sheet.

If you can help I would be very grateful
[TABLE="width: 791"]
<tbody>[TR]
[TD]BEDF002
[/TD]
[TD]Bedford Borough Council
[/TD]
[TD]Katie
[/TD]
[TD="align: right"]19-Nov-17
[/TD]
[TD]13-Nov-17
[/TD]
[TD]Adam
[/TD]
[TD]0.75
[/TD]
[TD="align: right"]14.96
[/TD]
[TD][/TD]
[TD]11.22
[/TD]
[/TR]
[TR]
[TD]BEDF002
[/TD]
[TD]Bedford Borough Council
[/TD]
[TD]Diana
[/TD]
[TD="align: right"]19-Nov-17
[/TD]
[TD]14-Nov-17
[/TD]
[TD]Adam
[/TD]
[TD]0.75
[/TD]
[TD="align: right"]14.96
[/TD]
[TD][/TD]
[TD]11.22
[/TD]
[/TR]
[TR]
[TD]BEDF002
[/TD]
[TD]Bedford Borough Council
[/TD]
[TD]Katie
[/TD]
[TD="align: right"]19-Nov-17
[/TD]
[TD]15-Nov-17
[/TD]
[TD]Adam
[/TD]
[TD]0.75
[/TD]
[TD="align: right"]14.96
[/TD]
[TD][/TD]
[TD]11.22
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5.25
[/TD]
[TD][/TD]
[TD][/TD]
[TD]78.99
[/TD]
[/TR]
[TR]
[TD]BEDF002
[/TD]
[TD]Bedford Borough Council
[/TD]
[TD]Josephine
[/TD]
[TD="align: right"]19-Nov-17
[/TD]
[TD]13-Nov-17
[/TD]
[TD]Alaco
[/TD]
[TD]0.50
[/TD]
[TD="align: right"]14.96
[/TD]
[TD][/TD]
[TD]7.48
[/TD]
[/TR]
[TR]
[TD]BEDF002
[/TD]
[TD]Bedford Borough Council
[/TD]
[TD]Katie
[/TD]
[TD="align: right"]19-Nov-17
[/TD]
[TD]14-Nov-17
[/TD]
[TD]Alaco
[/TD]
[TD]0.50
[/TD]
[TD="align: right"]14.96
[/TD]
[TD][/TD]
[TD]7.48
[/TD]
[/TR]
[TR]
[TD]BEDF002
[/TD]
[TD]Bedford Borough Council
[/TD]
[TD]Josephine
[/TD]
[TD="align: right"]19-Nov-17
[/TD]
[TD]15-Nov-17
[/TD]
[TD]Alaco
[/TD]
[TD]0.50
[/TD]
[TD="align: right"]14.96
[/TD]
[TD][/TD]
[TD]7.48
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD]45.03
[/TD]
[/TR]
[TR]
[TD]BEDF002
[/TD]
[TD]Bedford Borough Council
[/TD]
[TD]Claudia
[/TD]
[TD="align: right"]19-Nov-17
[/TD]
[TD]14-Nov-17
[/TD]
[TD]Beatrice
[/TD]
[TD]0.50
[/TD]
[TD="align: right"]14.96
[/TD]
[TD][/TD]
[TD]7.48
[/TD]
[/TR]
[TR]
[TD]BEDF002
[/TD]
[TD]Bedford Borough Council
[/TD]
[TD]Claudia
[/TD]
[TD="align: right"]19-Nov-17
[/TD]
[TD]15-Nov-17
[/TD]
[TD]Beatrice
[/TD]
[TD]0.50
[/TD]
[TD="align: right"]14.96
[/TD]
[TD][/TD]
[TD]7.48
[/TD]
[/TR]
[TR]
[TD]BEDF002
[/TD]
[TD]Bedford Borough Council
[/TD]
[TD]Claudia
[/TD]
[TD="align: right"]19-Nov-17
[/TD]
[TD]16-Nov-17
[/TD]
[TD]Beatrice
[/TD]
[TD]0.50
[/TD]
[TD="align: right"]14.96
[/TD]
[TD][/TD]
[TD]7.48
[/TD]
[/TR]
[TR]
[TD]Thanks
Marc
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1.50
[/TD]
[TD][/TD]
[TD][/TD]
[TD]22.44


[/TD]
[/TR]
</tbody>[/TABLE]

 

[marcidee]

2013

It breaks on the 2nd line below - it creates one new tab with no data - would it help if I can send you the spreadsheet (not sure if that's allowed / possible?)

Sheets("Sheet1").Rows(rownum & ":" & rownum2).Copy
Sheets(sheetname).Select

 

[mrshl9898]

Here's a link

https://ufile.io/3g9ny

 

[mrshl9898]

If that doesn't work (suspecting i won't). What are the values for rownum and rownum2?

 

[marcidee]

doesn't seem to work - I have uploaded to myairbridge - https://mab.to/v2qZxWy1q

First 2 rows look like this columns A - J

[TABLE="width: 791"]
<tbody>[TR]
[TD][/TD]
[TD]Name
[/TD]
[TD]Temp Name
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]QTY
[/TD]
[TD][/TD]
[TD="colspan: 2"]Unit Price
[/TD]
[/TR]
[TR]
[TD]BEDF002
[/TD]
[TD]Bedford Borough Council
[/TD]
[TD]Dee Adams
[/TD]
[TD="align: right"]29-Oct-17
[/TD]
[TD]25-Oct-17
[/TD]
[TD]Adam Giddings
[/TD]
[TD]0.75
[/TD]
[TD="align: right"]14.96
[/TD]
[TD][/TD]
[TD]11.22
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Here you go:

What is Row2? It's not what you are saying, it has 1.5 in G and 22.4 in J.

I have started on Row 3 and ignored it in the below

https://ufile.io/z70hx

 

[marcidee]

Thank you so much for this - for some reason I can only get this to work in your sheet - but that's not the end of the world as I can copy the data across - I will fiddle to try and work out why - I have copied and renamed the sheet in case there was a typo but that didn't help.

Once again - thank you - this will save so much time

 

[mrshl9898]

How strange, glad you're sorted.

 

[Jon von der Heyden]

In terms of fixing mine:

wksNew.Name = rngArea.Resize(1, 1).Offset(, 5).Text

As I said this could give a RT error. We are renaming the sheet per the name in the cell. If that text does not comply with sheet name rules then you will get a runtime error. I suggest this:

On Error Resume Next
    wksNew.Name = rngArea.Resize(1, 1).Offset(, 5).Text
On Error GoTo 0

This way the only element that can fail is the bit that renames the sheet. Then you can go back and see which names are not working and you can manually rename those few sheets.

 

[My Aswer Is This]

This has all been interesting to me.

The script looks down column "F" and creates a new sheet with the names found in column "F"
Then copies all rows to the sheet name found in column "F"

So if in row 5 "George" is in column "F" this row should be copied to sheet named "George"
So if in row 6 "Bob" is in column "F" this row should be copied to sheet named "Bob"

My script which was way back in Post 3 I believe worked for me.

We are now up to post 29 now and still have nothing working for you.

When I have tried the other scripts provided none of them work for me.

Like one script referred to columns 6 and 7 not sure why that was needed.
My script does assume none of these sheets have already been created.

I will continue to monitor this thread and see when we get one to work for you.