Hi all,
Let me preface this by saying I am very, very new to VBA. I get the basics, but when problems arise I'm struggling and this particular issue has me at a complete loss. I was following along to a YouTube video creating a Macro to split out my master list of information into separate sheets for each salesperson listed on it and everything seemed to be matching exactly to the code lines in the video. However, when running the Macro, I keep getting a "Compile Error: Variable not defined" message and it highlights the bit below that I've bold/ underlined in blue. The code is below and I triple checked it matches the code from the YouTube video, except that I updated the "client" part to "Salesperson" as that is the column header I'm using on my sheet in the same column. I've also included a screen shot of my master sheet for reference if that helps. If it matters, the video I was following was "VBA to Split data into multiple sheets - Advance VBA Tutorial by Exceldestination".
Any / all advice is appreciated! I've had some success so far with creating macros but this one has me about ready to throw in the towel!
Code being used:
Sub Salesperson_Split()
'Declaring Constant Variables
Const col = "D"
Const header_row = 1
'Assigning inital value as 2, because data transfer will happen from 2nd row onwards
Const starting_row = 2
'To declare variable of worksheet type for main sheet, that has data to split
Dim source_sheet As Worksheet
'To declare variable of worksheet type for adding required sheets
Dim destination_sheet As Worksheet
Dim source_row As Long
Dim last_row As Long
'This variable is for changing values in column D, that has Salesperson names
Dim Salesperson As String
'Assigning Active sheet, that has data to split
Set source_sheet = ActiveSheet
'To know the last filled row and activesheet bases on column D, that has data to split
last_row = source_sheet.Cells(source_sheet.Rows.Count, col).End(xlUp).Row
For source_row = starting_row To last_row
Salesperson = source_sheet.Cells(source_row, col).Value
Set destination_sheet = Nothing
On Error Resume Next
Set destination_sheet = Worksheets(Salesperson)
On Error GoTo 0
If destination_sheet Is Nothing Then
'To add new sheet, if there is no existing sheet for the given Salesperson name
Set destination_sheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
'To assign name to added sheet
destination_sheet.Name = Salesperson
'To add header row to each added sheet
source_sheet.Rows(header_row).Copy Destination:=destination_sheet.Rows(header_row)
End If
'To identify the next available row on destination sheet
destination_row = destination_sheet.Cells(destination_sheet.Rows.Count, col).End(xlUp).Row + 1
'Copying rows from active sheet, one by one and pasting to next available line on destination sheet
source_sheet.Rows(source_row).Copy Destination:=destination_sheet.Rows(destination_row)
Next source_row
End Sub
Example of sheet set up:
Let me preface this by saying I am very, very new to VBA. I get the basics, but when problems arise I'm struggling and this particular issue has me at a complete loss. I was following along to a YouTube video creating a Macro to split out my master list of information into separate sheets for each salesperson listed on it and everything seemed to be matching exactly to the code lines in the video. However, when running the Macro, I keep getting a "Compile Error: Variable not defined" message and it highlights the bit below that I've bold/ underlined in blue. The code is below and I triple checked it matches the code from the YouTube video, except that I updated the "client" part to "Salesperson" as that is the column header I'm using on my sheet in the same column. I've also included a screen shot of my master sheet for reference if that helps. If it matters, the video I was following was "VBA to Split data into multiple sheets - Advance VBA Tutorial by Exceldestination".
Any / all advice is appreciated! I've had some success so far with creating macros but this one has me about ready to throw in the towel!
Code being used:
Sub Salesperson_Split()
'Declaring Constant Variables
Const col = "D"
Const header_row = 1
'Assigning inital value as 2, because data transfer will happen from 2nd row onwards
Const starting_row = 2
'To declare variable of worksheet type for main sheet, that has data to split
Dim source_sheet As Worksheet
'To declare variable of worksheet type for adding required sheets
Dim destination_sheet As Worksheet
Dim source_row As Long
Dim last_row As Long
'This variable is for changing values in column D, that has Salesperson names
Dim Salesperson As String
'Assigning Active sheet, that has data to split
Set source_sheet = ActiveSheet
'To know the last filled row and activesheet bases on column D, that has data to split
last_row = source_sheet.Cells(source_sheet.Rows.Count, col).End(xlUp).Row
For source_row = starting_row To last_row
Salesperson = source_sheet.Cells(source_row, col).Value
Set destination_sheet = Nothing
On Error Resume Next
Set destination_sheet = Worksheets(Salesperson)
On Error GoTo 0
If destination_sheet Is Nothing Then
'To add new sheet, if there is no existing sheet for the given Salesperson name
Set destination_sheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
'To assign name to added sheet
destination_sheet.Name = Salesperson
'To add header row to each added sheet
source_sheet.Rows(header_row).Copy Destination:=destination_sheet.Rows(header_row)
End If
'To identify the next available row on destination sheet
destination_row = destination_sheet.Cells(destination_sheet.Rows.Count, col).End(xlUp).Row + 1
'Copying rows from active sheet, one by one and pasting to next available line on destination sheet
source_sheet.Rows(source_row).Copy Destination:=destination_sheet.Rows(destination_row)
Next source_row
End Sub
Example of sheet set up:
