VBA Code for adding new Sheet based on cell value
Can anyone help me out to get the code for my below request.
I need to add new sheets in excel based on unique cell values of a particular column.
E.g. If I have a data of 1000 rows and in that the 1st column contains the month names. I need to run a macro so that the new sheets will get created in the same file with the specific month names along with their data.
»
- asish's blog
- Login or register to post comments
- 44152 reads
Please Try Below Code
Private Sub CreateSheetsWithNames()
Dim sheetCount As Integer
Dim sheetName As String
Dim workbookCount As Integer
With ActiveWorkbook
sheetCount = Sheets(1).Range("A2").End(xlDown).Row
For i = 2 To sheetCount Step 1
sheetName = .Sheets(1).Range("A" & i).Value
workbookCount = .Worksheets.Count
.Sheets.Add After:=Sheets(workbookCount)
.Sheets(i).Name = sheetName
.Sheets(i).Range("A" & i, "F" & i).Value = .Sheets(1).Range("A" & i, "F" & i).Value
Next
End With
End Sub
Please Try Below Code
Private Sub CreateSheetsWithNames()
Dim sheetCount As Integer
Dim sheetName As String
Dim workbookCount As Integer
With ActiveWorkbook
sheetCount = Sheets(1).Range("A2").End(xlDown).Row
For i = 2 To sheetCount Step 1
sheetName = .Sheets(1).Range("A" & i).Value
workbookCount = .Worksheets.Count
.Sheets.Add After:=Sheets(workbookCount)
.Sheets(i).Name = sheetName
.Sheets(i).Range("A" & i, "F" & i).Value = .Sheets(1).Range("A" & i, "F" & i).Value
Next
End With
End Sub
Sorry, not trying to hijack, but...
This is exactly what I need, except... I'm just trying to create the worksheets, I already have a macro to populate the worksheets. My problem is, I have say 1000 rows (could be up to 500,000 or more), with information in 15 columns. I want to make named worksheets for the data in one of the columns (which happens to be column D), but of the 1000 rows, there is maybe 5 to 50 different numbers in it, randomly. I need a single worksheet for each different number. i.e. if my column D has only 1,6,4,12,15 (again randomly about the column. I need 5 worksheets created with those names. Is this possible? Thanks in advance.
Here is my modified Code.
Private Sub CreateSheetsWithNames()
Dim sheetCount As Integer
Dim sheetName As String
Dim workbookCount As Integer
With ActiveWorkbook
sheetCount = Sheets(1).Range("A2").End(xlDown).Row
For i = 2 To sheetCount Step 1
sheetName = .Sheets(1).Range("D" & i).Value
workbookCount = .Worksheets.Count
.Sheets.Add After:=Sheets(workbookCount)
.Sheets(i).Name = sheetName
Next
End With
End Sub
Brilliant! But can you help with the following?
Took me all day to find your code - it's brilliant, efficient and effective.
But could you please help me with the following?
Instead of inserting the values from each row from the source worksheet onto that exact same row in the newly created worksheets...
how could it be coded so that regardless of the row on the source worksheet its values become transposed onto the first (or second) row of each newly created worksheet?
I tried playing around with your code, but I couldn't get it.
This is my code... it's that line that I have as comment which I think is key:
Sub CreateTabs()
Dim sheetCount As Integer
Dim sheetName As String
Dim workbookCount As Integer
With ActiveWorkbook
sheetCount = Sheets("sample").Range("A2").End(xlDown).Row
For i = 2 To sheetCount Step 1
sheetName = .Sheets("sample").Range("A" & i).Value
workbookCount = .Worksheets.Count
.Sheets.Add After:=Sheets(workbookCount)
.Sheets(i).Name = sheetName
'.Sheets(i).Range("A" & i, "F" & i).Value = .Sheets("sample").Range("A" & i, "F" & i).Value
Next
End With
Worksheets("sample").Activate
End Sub
Works, except when there are more worksheets
I used the suggested code, and works perfectly. However, it doesn't work when there is another worksheet in the workbook.
Say I have a "master" worksheet before the active worksheet from where the code is run (to create the worksheets)... how would the code need to be adjusted so that the "master" worksheet does not interfere with the code?
What I'm trying to do is, once the worksheets are created, add additional information onto them (which I would take from the "master" worksheet).
Say the "master" worksheet has random strings and functions from a range that also varies in the number of rows it can have.
In essence, then, I'm trying to combine the code to create the worksheets with additional code to add/pass values and functions onto those same worksheets.
Could you please help with this?
Spent the rest of the night and now it's almost daylight and I can't figure it out.
Clarification
What data do you want in the newly created sheets?
I got it :)
For that line that transposes the values, I wrote it like this so that it goes on the 2nd row of each newly created worksheet:
.Sheets(i).Range("A2:E2").Value = .Sheets("sample").Range("A" & i, "F" & i).Value
lol, it was pretty simple after all. Thanks though!! I really have spent all day working on this and I FINALLY got it.