Saving a VBA script in 'personal workbook' and wanting to run it in newly created export file from a software application

Hello clever people in my computer,

I am a real novice when it comes to VBA script so please bear with me.

I have written a script that I would like to save in 'personal workbook' and be able to run it in a newly created file from our payroll software.

When i run the BACS export file from our payroll software, it is given a name of...'BACS(1)' or BACS(2) or BACS(3) etc etc etc.

Currently if i run the script below it runs the script in the 'personal workbook' but i need it to run in an open spreadsheet with a wildcard of 'BACS', i guess.

I think my issue is the green highlighted section, as it is using the 'personal workbook' as the source spreadsheet as opposed to the exported file called BACS...

Sub LFPayrollFPImport() 

Application.ScreenUpdating = False 

    Dim wsSrc As Worksheet 
    Dim wsDest As Worksheet 
    Dim lastRow As Long 
    Dim i As Long 

    ' Set source worksheet as active worksheet 
    Set wsSrc = ThisWorkbook.Activesheet 
    ' Add a new worksheet for output 
    Set wsDest = ThisWorkbook.Sheets.Add(After:=wsSrc) 
    wsDest.Name = "MacroOutput" 
    ' Find the last row with data in column A of source sheet 
    lastRow = wsSrc.Cells(wsSrc.Rows.Count, 1).End(xlUp).Row 
  
    ' Loop through each row and write transformed data 
    For i = 1 To lastRow 
        wsDest.Cells(i, 1).Value = 303265 ' Column A 
        wsDest.Cells(i, 2).Value = "'01645638" ' Column B as text 
        wsDest.Cells(i, 3).Value = 3 ' Column C 
        wsDest.Cells(i, 4).Value = "" ' Column D 
        wsDest.Cells(i, 5).Value = "Lubbock Fine LLP" ' Column E 
        wsDest.Cells(i, 6).Value = "00/00/0000" ' Column F 
        wsDest.Cells(i, 7).Value = 0 ' Column G 
        wsDest.Cells(i, 8).Value = wsSrc.Cells(i, 5).Value ' Column H from column E 
        wsDest.Cells(i, 9).Value = "'" & wsSrc.Cells(i, 1).Text ' Column I from column A 
        wsDest.Cells(i, 10).Value = "'" & wsSrc.Cells(i, 2).Text ' Column J from column B 
        wsDest.Cells(i, 11).Value = "Lubbock Fine LLP" ' Column K 
        wsDest.Cells(i, 12).Value = wsSrc.Cells(i, 7).Value ' Column L from column A 
        wsDest.Cells(i, 13).Value = "" ' Column M 
        wsDest.Cells(i, 14).Value = "" ' Column N 
        wsDest.Cells(i, 15).Value = 1 ' Column O 

    Next i 
    MsgBox "Transformation complete. Output is in sheet: MacroOutput" 

Application.ScreenUpdating = True 

Dim strFullName As String 
Application.DisplayAlerts = False 

strFullName = "P:\Staff Payroll\Lloyds Payment imports" + "\LFPayrollFP.csv" 
ThisWorkbook.Sheets("MacroOutput").Copy 
ActiveWorkbook.SaveAs Filename:=strFullName, FileFormat:=xlCSV, CreateBackup:=True 
ActiveWorkbook.Close 

Application.DisplayAlerts = True 

End Sub

thank you so much for your help Lara

when i try to run the macro in the newly created payroll software export file, it runs in the 'personal workbook' and creates the newly formated tab in there rather than the new file called 'BACS(1)' or BACS(2) etc.

I ideally need it to find an open spreadsheet with a file name that starts with 'BACS' and run it in that spreadsheet.

thank you

Solution

Simply you need to replace ThisWorkbook by ActiveWorkbook in most places.

More accurate solution will be to create a new workbook:

    ' Set source worksheet as active worksheet 
    Set wsSrc = ThisWorkbook.Activesheet 
    ' Add a new worksheet for output
    Dim nwb As Workbook
    Set nwb = Workbooks.Add
    Set wsDest = nwb.Sheets.Add(After:=wsSrc)
    ...
    nwb.SaveAs Filename:=strFullName, FileFormat:=xlCSV, _
        CreateBackup:=True 
    nwb.Close

A template will be the very useful option which helps you to avoid many writes like wsDest.Cells(i, 1).Value = 303265 etc.

    Set nwb = Workbooks.Add("BasicTemplate.xltx")

Finally, it can look like:

Sub LFPayrollFPImport() 
    Application.ScreenUpdating = False 
    Dim wsSrc As Worksheet, wsDest As Worksheet, nwb As Workbook 
    Dim lastRow&, i&, strFullName$
    ' Set source worksheet as active worksheet 
    Set wsSrc = ThisWorkbook.Activesheet ' evidently it should not be ThisWorkbook 
    Set nwb = Workbooks.Add("BasicTemplate.xltx")
    set wsDest = nwb.Sheets("MacroOutput") 
    ' Find the last row with data in column A of source sheet 
    lastRow = wsSrc.Cells(wsSrc.Rows.Count, 1).End(xlUp).Row 
    ' Loop through each row and write transformed data 
    For i = 1 To lastRow 
        wsDest.Cells(i, 8).Value = wsSrc.Cells(i, 5).Value ' Column H from column E 
        wsDest.Cells(i, 9).Value = "'" & wsSrc.Cells(i, 1).Text ' Column I from column A 
        wsDest.Cells(i, 10).Value = "'" & wsSrc.Cells(i, 2).Text ' Column J from column B 
        wsDest.Cells(i, 12).Value = wsSrc.Cells(i, 7).Value ' Column L from column A 
    Next i 
    MsgBox "Transformation complete. Output is in sheet: MacroOutput" 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = False 
    strFullName = "P:\Staff Payroll\Lloyds Payment imports" + "\LFPayrollFP.csv" 
    nwb.SaveAs Filename:=strFullName, FileFormat:=xlCSV, CreateBackup:=True 
    nwb.Close 
    Application.DisplayAlerts = True 
End Sub

All constant information should be available on the "MacroOutput" sheet of the "BasicTemplate.xltx" template workbook.