Solved: Macro giving error 'label not defined'

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

dpotmshr

Thread Starter
Joined
Feb 7, 2013
Messages
6
Hi,I have excel sheet which contains multiple rows for each country.The requirement is to split the file for each country and save with country name.I have got the below macro but when im trying to run it,getting error as label not defined.Please advise:
Sub CopyData()
Dim LMainWB As String
Dim LNewWB As String
Dim LRow As Integer
Dim LContinue As Boolean
Dim LColAMaster As String
Dim LColATest As String
Dim LWBCount As Integer
Dim LMsg As String
Dim LPath As String
Dim LFilename As String
Dim LColAValue As String

On Error GoTo Errorcatch

'Path to save all new workbooks to
LPath = "C:\Users\43693678\Documents\Split"

'Retrieve name of the workbook that contains the data
LMainWB = ActiveWorkbook.Name

'Initialize variables
LContinue = True
LRow = 2
LWBCount = 0

'Start comparing with cell A2
LColAMaster = "A2"

'Loop through all column A values until a blank cell is found
While LContinue = True

LRow = LRow + 1
LColATest = "A" & CStr(LRow)

'Found a blank cell, do not continue
If Len(Range(LColATest).Value) = 0 Then
LContinue = False
End If

'Value in column A
LColAValue = Range(LColAMaster).Value

'Found occurrence that did not match, copy data to new workbook
If LColAValue <> Range(LColATest).Value Then

'Copy headings
Range("A1:C1").Select
Selection.Copy

'Add new workbook and paste headings into new workbook
Workbooks.Add
LNewWB = ActiveWorkbook.Name
ActiveSheet.Paste
Range("A1").Select

'Copy data from columns A - D
Windows(LMainWB).Activate
Range(LColAMaster & ":C" & CStr(LRow - 1)).Select
Selection.Copy

'Paste results
Windows(LNewWB).Activate
Range("A2").Select
ActiveSheet.Paste
Range("A1").Select

'Save (and overwrite, if necessary) workbook with name from column A
'and then close workbook
LFilename = LPath & LColAValue & ".xls"
If Dir(LFilename) <> "" Then Kill LFilename
ActiveWorkbook.SaveAs Filename:=LFilename
ActiveWorkbook.Close

'Go back to Main sheet and continue where left off
Windows(LMainWB).Activate
LColAMaster = "A" & CStr(LRow)

'Keep track of the number of workbooks that have been created
LWBCount = LWBCount + 1

End If

Wend

Range("A1").Select
Application.CutCopyMode = False

LMsg = "Copy has completed. " & LWBCount & " new workbooks have been created."
LMsg = LMsg & Chr(10) & "You can find them in the following directory:" & Chr(10) & LPath

MsgBox LMsg

End Sub
Errorcatch:
MsgBox Err.Description
 
Joined
Sep 4, 2003
Messages
4,912
Your Error handling needs to be inside the subroutine. See modified code below

Code:
Sub CopyData()
Dim LMainWB As String
Dim LNewWB As String
Dim LRow As Integer
Dim LContinue As Boolean
Dim LColAMaster As String
Dim LColATest As String
Dim LWBCount As Integer
Dim LMsg As String
Dim LPath As String
Dim LFilename As String
Dim LColAValue As String

On Error GoTo Errorcatch

'Path to save all new workbooks to
LPath = "C:\Users\43693678\Documents\Split"

'Retrieve name of the workbook that contains the data
LMainWB = ActiveWorkbook.Name

'Initialize variables
LContinue = True
LRow = 2
LWBCount = 0

'Start comparing with cell A2
LColAMaster = "A2"

'Loop through all column A values until a blank cell is found
While LContinue = True

LRow = LRow + 1
LColATest = "A" & CStr(LRow)

'Found a blank cell, do not continue
If Len(Range(LColATest).Value) = 0 Then
LContinue = False
End If

'Value in column A
LColAValue = Range(LColAMaster).Value

'Found occurrence that did not match, copy data to new workbook
If LColAValue <> Range(LColATest).Value Then

'Copy headings
Range("A1:C1").Select
Selection.Copy

'Add new workbook and paste headings into new workbook
Workbooks.Add
LNewWB = ActiveWorkbook.Name
ActiveSheet.Paste
Range("A1").Select

'Copy data from columns A - D
Windows(LMainWB).Activate
Range(LColAMaster & ":C" & CStr(LRow - 1)).Select
Selection.Copy

'Paste results
Windows(LNewWB).Activate
Range("A2").Select
ActiveSheet.Paste
Range("A1").Select

'Save (and overwrite, if necessary) workbook with name from column A
'and then close workbook
LFilename = LPath & LColAValue & ".xls"
If Dir(LFilename) <> "" Then Kill LFilename
ActiveWorkbook.SaveAs Filename:=LFilename
ActiveWorkbook.Close

'Go back to Main sheet and continue where left off
Windows(LMainWB).Activate
LColAMaster = "A" & CStr(LRow)

'Keep track of the number of workbooks that have been created
LWBCount = LWBCount + 1

End If

Wend

Range("A1").Select
Application.CutCopyMode = False

LMsg = "Copy has completed. " & LWBCount & " new workbooks have been created."
LMsg = LMsg & Chr(10) & "You can find them in the following directory:" & Chr(10) & LPath
MsgBox LMsg
End

Errorcatch:
MsgBox Err.Description
End Sub
Rollin
 

dpotmshr

Thread Starter
Joined
Feb 7, 2013
Messages
6
Thanks for help and it really worked.But now im getting another error "select methed of Range Class failed". any idea?
 
Joined
Sep 4, 2003
Messages
4,912
Which line of code is throwing the error when you hit debug? I assume it is >>
Range(LColAMaster & ":C" & CStr(LRow - 1)).Select but I want to be positive.

Can you attach a sample copy of your workbook?

Rollin
 

dpotmshr

Thread Starter
Joined
Feb 7, 2013
Messages
6
Hello Rollin,
Thank you for your reply again.
The Data im using is :
countryNameNumberIndiaOrange5Algeriaapple3AlgeriaBanana4Algeriapeach5AustraliaOrange6Australiaapple4BahrainOrange3Bahrainapple4BahrainBanana5
 
Joined
Sep 4, 2003
Messages
4,912
I created a sample workbook and ran the code above and it ran fine without any errors. Which line of code is giving the error? Can you upload your sample workbook?


Rollin
 
Joined
Sep 4, 2003
Messages
4,912
Remove the code from the worksheet object and trying adding it to a stand-alone code module and running it from there instead
(in the VB editor select INSERT >> CODE MODULE) and paste your code into there and run it from here.

Rollin
 

dpotmshr

Thread Starter
Joined
Feb 7, 2013
Messages
6
Yiiipppppeee..it worked and i must say that im learning now little more on macro. Thanks a lot. Just one more requirement but nothing do or die type.Rather thn giving the file path in macro if I want user to select the path while running macro and files should be saved at that path.How do I make changes to macro.
 
Joined
Sep 4, 2003
Messages
4,912
Add the code below. The folder path will be set to the variable named "vPath"

In my example below I am testing using a message box so you would just need to remove the message box and instead use the variable somewhere else in the code when saving the files. Just keep in mind that the variable will only contain the path only so you will need to append the actual filename to the end of it when performing the save. This is only one of several ways to do this but is the easiest way in my opinion.

Code:
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        If .SelectedItems.Count > 0 Then
          vPath = .SelectedItems(1) & "\"
        End If
                
    End With
     
MsgBox (vPath)
Rollin
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top