1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Macro giving error 'label not defined'

Discussion in 'Business Applications' started by dpotmshr, Feb 7, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. dpotmshr

    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
     
  2. Rollin_Again

    Rollin_Again

    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
     
  3. dpotmshr

    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?
     
  4. Rollin_Again

    Rollin_Again

    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
     
  5. dpotmshr

    dpotmshr Thread Starter

    Joined:
    Feb 7, 2013
    Messages:
    6
    Hello Rollin,
    Thank you for your reply again.
    The Data im using is :
    countryNameNumberIndiaOrange5Algeriaapple3AlgeriaBanana4Algeriapeach5AustraliaOrange6Australiaapple4BahrainOrange3Bahrainapple4BahrainBanana5
     
  6. Rollin_Again

    Rollin_Again

    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
     
  7. dpotmshr

    dpotmshr Thread Starter

    Joined:
    Feb 7, 2013
    Messages:
    6
    Hello,
    Thanks for helping me out on this.
    I am attaching the sample file.
     

    Attached Files:

  8. Rollin_Again

    Rollin_Again

    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
     
  9. dpotmshr

    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.
     
  10. Rollin_Again

    Rollin_Again

    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
     
  11. dpotmshr

    dpotmshr Thread Starter

    Joined:
    Feb 7, 2013
    Messages:
    6
    Hello ,
    you are genius..my problem is solved.Thank you
     
  12. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1088536

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice