Creating New Sheets from Main Sheet
-
Friday, May 18, 2012 10:42 AMHi,
So, i'm new to excel and perhaps someone can point me in the right direction; what I should be looking at.
I have an excel sheet, with data on it.
This data has a colomn 'N'; which specifies a 'Group' (North, South or East) (data of groups via 'vlookup' from sheet 'CLIENT LIST')
What I am trying to do, is to be able to split the data on three seperate excel sheets automatically. (OR even better different workbooks are created)
'NORTH CLIENT', 'SOUTH CLIENT' and 'WEST CLIENT' (see the tabs which are colour coded GREEN - (this would be the end result)) and the name of each single sheet is renamed to the 'CLIENT GROUP'.
Any ideas?
Thanks.
Sam
All Replies
-
Friday, May 18, 2012 10:44 AM
-
Tuesday, May 22, 2012 2:02 AMModerator
Hi,
I want to confirm something to this issue:
The values of column N are already existing in the workbook?
What you want is just copy the records into the worksheet which the name of sheet is corresponding to the column N?
As my understanding, the original workbook is just has the worksheets Main and CLIENT LIST, then run a macro, there are three new worksheets created, and hold the relative records. Am I right?
Jaynet Zhang
TechNet Community Support
-
Tuesday, May 22, 2012 8:45 AMModerator
Hi,
I have written some code for you.
Just insert a Module in the VBAProject, then copy the following code into it. Then go back to your Main worksheet, run the macro “new_sheets”.
Note: The code will delete the others sheets except sheet “Main” and “CLIENT LIST”. So if there are other sheets you need in this workbook, please tell me. I will change the code.
Public Sub new_sheets()
Dim row_count As Integer
Dim row_now As Integer
Dim sheets_count As Integer
Dim sheet_now As Integer
sheets_count = ActiveWorkbook.sheets.Count
For sheet_now = sheets_count To 3 Step -1
sheets(sheet_now).Delete
Next
ActiveWorkbook.sheets.Add After:=sheets("CLIENT LIST")
sheets(sheets.Count).Name = "NORTH CLIENT"
ActiveWorkbook.sheets("NORTH CLIENT").Tab.color = 5296274
ActiveWorkbook.sheets.Add After:=sheets("NORTH CLIENT")
sheets(sheets.Count).Name = "SOUTH CLIENT"
ActiveWorkbook.sheets("SOUTH CLIENT").Tab.color = 5296274
ActiveWorkbook.sheets.Add After:=sheets("SOUTH CLIENT")
sheets(sheets.Count).Name = "WEST CLIENT"
ActiveWorkbook.sheets("WEST CLIENT").Tab.color = 5296274
ActiveWorkbook.sheets("Main").Select
row_count = ActiveSheet.UsedRange.Rows.Count
Dim row_used As Integer
For row_now = 1 To row_count - 4 Step 4
sheets("Main").Select
Select Case Cells(row_now, 14).Value
Case "NORTH CLIENT"
Range("A" & row_now & ":" & "M" & row_now + 3).Select
Selection.Copy
sheets("NORTH CLIENT").Select
row_used = ActiveSheet.UsedRange.Rows.Count
row_used = row_used + 1
If row_used > 2 Then
row_used = row_used + 1
End If
Rows(row_used).Select
ActiveSheet.paste
Case "SOUTH CLIENT"
Range("A" & row_now & ":" & "M" & row_now + 3).Select
Selection.Copy
sheets("SOUTH CLIENT").Select
row_used = ActiveSheet.UsedRange.Rows.Count
row_used = row_used + 1
If row_used > 2 Then
row_used = row_used + 1
End If
Rows(row_used).Select
ActiveSheet.paste
Case "WEST CLIENT"
Range("A" & row_now & ":" & "M" & row_now + 3).Select
Selection.Copy
sheets("WEST CLIENT").Select
row_used = ActiveSheet.UsedRange.Rows.Count
row_used = row_used + 1
If row_used > 2 Then
row_used = row_used + 1
End If
Rows(row_used).Select
ActiveSheet.paste
End Select
Next
End Sub
Jaynet Zhang
TechNet Community Support
- Marked As Answer by Jaynet ZhangMicrosoft Contingent Staff, Moderator Monday, May 28, 2012 2:08 AM
.png)
