CODEWITHSUNDEEP
excelvbams-office
JL Thalacker
JL Thalacker

Reputation: 45

VBA/Macro script to change a tab name

I have applied changes to the script (posted at the far bottom) help offered recently and it is working and listed below. (thanks PeterB)

Now for each file processed I would like to:

I'm new to vba and believe that once I can learn this use of the DIM next steps will be more clear.

My sense is that I need to use the dimensioned name before I move to the next file to be processed, immediately below I have tried... and it's not working.

thanks!

    'not working!!!
    'File #1
    wb_source.Name = ActiveWorkbook.Name
    wb_source.Sheets("xAccountARAgingPatient.xlsx").Copy 
    after:=wb_target.Sheets(wb_target.Sheets.Count)
    Application.DisplayAlerts = False
    wb_source.Close
    Application.DisplayAlerts = True

    'working
    Sub wwwww()
    Dim wb_source As Workbook
    Dim wb_target As Workbook
    
    Application.DisplayAlerts = False
    
    Set wb_source = Workbooks.Open("C:\Test\xAccountARAgingPatient.xlsx")
    Set wb_target = Workbooks.Open("c:\Test\MEBIllingOffice.xlsm")
    
    'assumes the sheet name is "xAccountARAgingPatient.xlsx" note - sheet names must be 
    unique within target
    'File #1
    wb_source.Sheets("xAccountARAgingPatient.xlsx").Copy 
    after:=wb_target.Sheets(wb_target.Sheets.Count)
    wb_source.Close
    
    'File #2
    Set wb_source = Workbooks.Open("C:\Test\xAccountARAgingPayer.xlsx")
    wb_source.Sheets("xAccountARAgingPayer.xlsx").Copy 
    after:=wb_target.Sheets(wb_target.Sheets.Count)
    wb_source.Close
    
    'File #3'
    Set wb_source = Workbooks.Open("C:\Test\xCashAgingAnalysis.xlsx")
    wb_source.Sheets("xCashAgingAnalysis.xlsx").Copy 
    after:=wb_target.Sheets(wb_target.Sheets.Count)
    wb_source.Close
    
    
    'wb_target.Save
    'wb_target.Close
    
    Application.DisplayAlerts = True
    End Sub

Upvotes: 1

Views: 1867

Answers (1)

dbmitch
dbmitch

Reputation: 5386

From your comments sounds like It looks like you're trying to rename the Worksheet.

Yet your code is looking like it's trying to rename the workbook by assigning the Workkbook Name to the readonly value of the Work Book name. Confusing for sure - and will definitely lead to that error

I'm assuming from your example that you want the Worksheet in wb_source renamed. It's unclear when you split out the code to know which Workbook is active at the time.

Try assigning the name to the Work Sheet of the Active Workbook

ActiveWorkbook.ActiveSheet.Name = wb_source.Name

Or if it's actually the source document - just use that so you don't need to know if it's active or not.

wb_source.ActiveSheet.Name = wb_source.Name

Upvotes: 2

Related Questions