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

[sachavez]

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]

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.

 

[sachavez]

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.

 

[DanteAmor]

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?

 

[sachavez]

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]

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:

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]

Getting an error on this step:

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

 

[DanteAmor]

What the message says?

 

[sachavez]

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]

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.