Copy Data to a new tab same sheet

marcidee

Board Regular
Joined
May 23, 2016
Messages
196
Office Version
  1. 2019
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]
 
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
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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]
 
Last edited:
Upvote 0
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
 
Upvote 0
In terms of fixing mine:
Code:
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:
Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,247
Messages
6,171,004
Members
452,374
Latest member
keccles

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