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

sachavez

Active Member
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
 

DanteAmor

Well-known Member
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:

sachavez

Active Member
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.
 

DanteAmor

Well-known Member
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
 

sachavez

Active Member
Getting an error on this step:

Sheets.Add(after:=Sheets(Sheets.Count)).Name = c.Value
 

sachavez

Active Member
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
 

DanteAmor

Well-known Member
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.
 

Some videos you may like

This Week's Hot Topics

Top