VBA solution to create worksheets based on cell value and then copy data to its new worksheet

sachavez

Active Member
Joined
May 22, 2009
Messages
469
Hello,

I'm looking for some code that will:

1. Evaluate my data set in my "test" worksheet. The header for my data set begins in worksheet("Test"), cell A3, and the size of the data set varies weekly.
2. Create new worksheets (and name the new worksheet) based on the cell content is the "Test" worksheet, to newly created worksheets, range("J4") to the end of column J.
3. Copy the data from my test worksheet to the newly created worksheets.

Thanks in advance.

Steve
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Copy the entire sheet to the new sheet, or just the header and the corresponding row?
3. Copy the data from my test worksheet to the newly created worksheets.

Please comment.
 
Upvote 0
Please comment.

Basically, I'd like the code to **create** new customer worksheets, so if the customer name appears in column J, the whole row would be copied into that customer's newly created worksheet.
 
Last edited:
Upvote 0
Basically, I'd like the code to **create** new customer worksheets, so if the customer name appears in column J, the whole row would be copied into that customer's newly created worksheet.

In column J are they unique customers, or are there repeat customers?
Is it likely that the customer sheet already exists?
What should you do if the sheet already exists?
 
Last edited:
Upvote 0
In column J are they unique customers, or are there repeat customers?
Is it likely that the customer sheet already exists?

New work sheets would have to be created each time the macro is run (it is not possible that a customer work sheet would already exist). After the macro is run, each new worksheet could contain multiple rows of data.
 
Upvote 0
I am not sure what already exists in your book. Neither of the data to be copied in the new sheet.


So start testing with the following:

Code:
Sub create_worksheets()
  Dim c As Range
  For Each c In Sheets("test").Range("J4", Sheets("test").Range("J" & Rows.Count).End(xlUp))
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = c.Value
    Sheets("test").Range("3:3," & c.Row & ":" & c.Row).Copy Range("A3")
  Next
End Sub
 
Upvote 0
also, I added on error resume next just to see what would happen. It appears that the code is creating a new work sheet for every row in the "test" sheet. I was planning to fill in each new worksheet with all customer transactions. So if customer "smith" had multiple transactions, all would appear in the same work sheet.

Thanks
 
Upvote 0
I still don't understand what you have on your sheet.
You could explain with examples what you have on the sheet and what you want to put on each new sheet.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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