Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Code runs in the wrong workbook

0

Hi there, I have two pieces of code put together so that the Open recent file one runs first and then the Split words code. The only problem is the split words code runs in the file the open recent file code opens, but it should Split the words in the master workbook. Is there a way to make the Split words code split the words in the master workbook only

Thanks

Split Words Code

Sub SplitWords()

    n = Application.WorksheetFunction.CountA(Range("A:A"))

    For r = 1 To n
        cadena = Cells(r, 1)
        l = Len(cadena)
        i = 1
        k = 2

        Do
            j = i
            Do
                letra = Mid(cadena, i, 1)
                i = i + 1
            Loop Until letra = "," Or i > l

            word = Mid(cadena, j, i - j - 1)
            Cells(r, k) = word
            k = k + 1
        Loop Until i > l
    Next

End Sub

Code Put together

Option Explicit

Sub NewestFile()

    Dim MyPath As String

    Dim MyFile As String

    Dim LatestFile As String

    Dim LatestDate As Date

    Dim LMD As Date

    Dim wbMaster As Workbook
    Dim wbLatest As Workbook

    Set wbMaster = ThisWorkbook

    MyPath = "C:\Users\lfinc\OneDrive\Documents\Officiating\New Results system\For LIF Files"

    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    MyFile = Dir(MyPath & "*.LIF", vbNormal)

    If Len(MyFile) = 0 Then

        MsgBox "No files were found...", vbExclamation

        Exit Sub

    End If

    Do While Len(MyFile) > 0

        LMD = FileDateTime(MyPath & MyFile)

        If LMD > LatestDate Then

            LatestFile = MyFile

            LatestDate = LMD

        End If

        MyFile = Dir

    Loop

    Set wbLatest = Workbooks.Open(MyPath & LatestFile)

    ' Clear some stuff
    wbMaster.Worksheets("Data Sorter").Cells.Clear

    ' Copy/Paste some stuff
 wbLatest.Worksheets(1).Range("A:A").Copy wbMaster.Worksheets("Data Sorter").Range("A1")
End Sub
Sub CallSubs()

Call NewestFile
Call SplitWords

End Sub
Answer
Discuss

Answers

0
Selected Answer

Hello, Dave

You just have to put wbMaster.WorkSheets("Data Sorter"). in front of Range and Cells. As following:

Dim wbMaster As Workbook

Sub SplitWords()
    
    n = Application.WorksheetFunction.CountA(wbMaster.Sheets("Data Sorter").Range("A:A"))
    
    For r = 1 To n
        cadena = wbMaster.Sheets("Data Sorter").Cells(r, 1)
        l = Len(cadena)
        i = 1
        k = 2
        
        Do
            j = i
            Do
                letra = Mid(cadena, i, 1)
                i = i + 1
            Loop Until letra = "," Or i > l
            
            word = Mid(cadena, j, i - j - 1)
            wbMaster.Sheets("Data Sorter").Cells(r, k) = word
            k = k + 1
        Loop Until i > l
    Next
    
End Sub

Sub OpenFile()

    Dim wbLatest As Workbook
    
    Set wbMaster = ThisWorkbook
    Set wbLatest = Workbooks.Open("Comma Separated")
    
    wbLatest.Worksheets(1).Range("A:A").Copy wbMaster.Worksheets("Data Sorter").Range("A1")
    
    wbLatest.Close

End Sub

Sub callsSubs()

    Call OpenFile
    Call SplitWords
    
End Sub

And declare variable wbMaster out of any procedure as a global variable. At the top of the procedures.

Any doubt, please let me know.

Regards
Basilio

Discuss

Discussion

Hi Basilio, I put your new code in but it came back saying object required and highlighted. Not sure whats wrong?
n = Application.WorksheetFunction.CountA(wbMaster.WorkSheets("Data Sorter").Range("A:A"))
Dave1245 (rep: 8) May 29, '20 at 4:03 am
Hello, Dave
I think is the way how you declare the variable wbMaster.
I have attached to the answer the files I made in order to emulate what you explained in your question and the 3 codes I used. Of course, second code (OpenFile) is just for my example, in your case yours is ok.
Put both files in the same directory and try it (macro callsSub), it worked for me.
Basilio (rep: 105) May 29, '20 at 11:38 am
Hi this works now. Thanks
I also use an import code is there a way to make the code run when a new file appears in a certain folder?
The code I use is (in the next post) needs to run when a new .LIF file appears in the folder location which is the same as in the code below.
Thanks
Dave1245 (rep: 8) May 29, '20 at 3:37 pm
Option Explicit
 
Sub NewestFile()
 
    Dim MyPath As String
 
    Dim MyFile As String
 
    Dim LatestFile As String
 
    Dim LatestDate As Date
 
    Dim LMD As Date
 
    Dim wbMaster As Workbook
    Dim wbLatest As Workbook
 
    Set wbMaster = ThisWorkbook
 
    MyPath = "C:\Users\lfinc\OneDrive\Documents\Officiating\New Results system\For LIF Files"
 
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
 
    MyFile = Dir(MyPath & "*.LIF", vbNormal)
 
    If Len(MyFile) = 0 Then
 
        MsgBox "No files were found check the code is looking in the right folder.", vbExclamation
 
        Exit Sub
 
    End If
 
    Do While Len(MyFile) > 0
 
        LMD = FileDateTime(MyPath & MyFile)
 
        If LMD > LatestDate Then
 
            LatestFile = MyFile
 
            LatestDate = LMD
 
        End If
 
        MyFile = Dir
 
    Loop
 
    Set wbLatest = Workbooks.Open(MyPath & LatestFile)
 
    ' Clear some stuff
    wbMaster.Worksheets("Data Sorter").Cells.Clear
 
    ' Copy/Paste some stuff
 wbLatest.Worksheets(1).Range("A:A").Copy wbMaster.Worksheets("Data Sorter").Range("A1")
End Sub
Sub CallSubs()
Call NewestFile
Call SplitWords
Call Autoclose
Call Copy_Paste_Below_Last_Cell
 
End Sub
 
Dave1245 (rep: 8) May 29, '20 at 3:37 pm
Add to Discussion
0

Your SplitWords code runs on the ActiveSheet in the ActiveWorkbook. When you open a new workbook that workbook will become the ActiveWorkbook and one of its sheets the ActiveSheet automatically.

Your SplitWords code runs on the ActiveSheet by default because it doesn't specify any workbook or worksheet. Therefore the solution to your problem should be to specify which worksheet to modify and in which workbook to find it.

The best way to do that might be to specify the ActiveSheet in the procedure CallSubs, before it's changed, and pass it to SplitWords as an argument. Of course, the code of that procedure will have to be re-written to integrate the information. There are only 3 or 4 expressions that need to be expanded and I'd have done it for you but since you use a large number of variables, none of them declared, the job is one of declaring variables - or go to great expense of code to continue avoid doing so.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login