Import and rename

[trevolly]

Hi all.

I would like to be able to use an excel workbook with vba as a "backup" that I will run each month to collect data from three other closed workbooks. These workbooks are used everyday and when one became corrupt my employer panicked. This simple system would save me some work when my employer comes panicking asking if I can retrieve the lost data (I did!).

I've written the vba to run and open each workbook from a specific location on a network drive and then copy a worksheet in to the "Backup workbook" and this all runs fine. The issue is that each of the worksheets in the workbooks are named "Data" and I end up with "Data", "Data(2)" and "Data(3)" in the backup workbook. The worksheets cant be renamed in the original workbooks due to coding someone has written.

Could these worksheets be renamed via vba when excel imports them?

I'd like the sheet "Data" to become "Turnaround Audit"
"Data (2)" to become "Airbridge Inspection Audit"
"Data (3)" to become "Airbridge Operation Audit"

My coding is.....

Sub CopySheetFromClosedWB()
Application.ScreenUpdating = False

'Imports as "Data" - would like it to be renamed to "Turnaround Audit"
Set closedbBook = Workbooks.Open("\\gatwick\Group9\Airfield Operations\2023 Airfield Inspection and Data\Aircraft Turnaround Audits\Aircraft Turnaround Audit 2023.xlsm")
closedbBook.Sheets("Data").Copy After:=ThisWorkbook.Sheets(1)
closedbBook.Close SaveChanges:=False

'Imports as "Data(2)" - would like it to be renamed to "Airbridge Inspection Audit"
Set closedbBook = Workbooks.Open("\\gatwick\Group9\Airfield Operations\2023 Airfield Inspection and Data\Airbridge Safety Inspections\Airbridge Safety Inspection 2023.xlsm")
closedbBook.Sheets("Data").Copy After:=ThisWorkbook.Sheets(1)
closedbBook.Close SaveChanges:=False

'Imports as "Data(3)" - would like it to be renamed to "Airbridge Operation Audit"
Set closedbBook = Workbooks.Open("\\gatwick\Group9\Airfield Operations\2023 Airfield Inspection and Data\Aircraft Turnaround Audits\Airbridge Operation Safety Audit 2023.xlsm")
closedbBook.Sheets("Data").Copy After:=ThisWorkbook.Sheets(1)
closedbBook.Close SaveChanges:=False


Application.ScreenUpdating = True
ActiveWorkbook.Save

Sheets("Master").Select

ActiveWorkbook.Save

MsgBox "Audit Data Copied For The Month"
End Sub

Thank you all

 

[sleek12]

Try:-

Sub CopySheetFromClosedWB()
    Application.ScreenUpdating = False

    'Imports as "Data" - rename to "Turnaround Audit"
    Set closedbBook = Workbooks.Open("\\gatwick\Group9\Airfield Operations\2023 Airfield Inspection and Data\Aircraft Turnaround Audits\Aircraft Turnaround Audit 2023.xlsm")
    closedbBook.Sheets("Data").Copy After:=ThisWorkbook.Sheets(1)
    ThisWorkbook.Sheets(2).Name = "Turnaround Audit"
    closedbBook.Close SaveChanges:=False

    'Imports as "Data(2)" - rename to "Airbridge Inspection Audit"
    Set closedbBook = Workbooks.Open("\\gatwick\Group9\Airfield Operations\2023 Airfield Inspection and Data\Airbridge Safety Inspections\Airbridge Safety Inspection 2023.xlsm")
    closedbBook.Sheets("Data").Copy After:=ThisWorkbook.Sheets(1)
    ThisWorkbook.Sheets(3).Name = "Airbridge Inspection Audit"
    closedbBook.Close SaveChanges:=False

    'Imports as "Data(3)" - rename to "Airbridge Operation Audit"
    Set closedbBook = Workbooks.Open("\\gatwick\Group9\Airfield Operations\2023 Airfield Inspection and Data\Aircraft Turnaround Audits\Airbridge Operation Safety Audit 2023.xlsm")
    closedbBook.Sheets("Data").Copy After:=ThisWorkbook.Sheets(1)
    ThisWorkbook.Sheets(4).Name = "Airbridge Operation Audit"
    closedbBook.Close SaveChanges:=False

    Application.ScreenUpdating = True
    ActiveWorkbook.Save
    Sheets("Master").Select
    ActiveWorkbook.Save

    MsgBox "Audit Data Copied For The Month"
End Sub

In this version of the code, after each worksheet is copied, its name is set using the Name property of the worksheet object. The sheet names are set to "Turnaround Audit", "Airbridge Inspection Audit", and "Airbridge Operation Audit" as you specified.
Take note also that I added line breaks to make the code easier to read, and removed the unnecessary ActiveSheet.Select line.

 

[trevolly]

Try:-

Sub CopySheetFromClosedWB()
    Application.ScreenUpdating = False

    'Imports as "Data" - rename to "Turnaround Audit"
    Set closedbBook = Workbooks.Open("\\gatwick\Group9\Airfield Operations\2023 Airfield Inspection and Data\Aircraft Turnaround Audits\Aircraft Turnaround Audit 2023.xlsm")
    closedbBook.Sheets("Data").Copy After:=ThisWorkbook.Sheets(1)
    ThisWorkbook.Sheets(2).Name = "Turnaround Audit"
    closedbBook.Close SaveChanges:=False

    'Imports as "Data(2)" - rename to "Airbridge Inspection Audit"
    Set closedbBook = Workbooks.Open("\\gatwick\Group9\Airfield Operations\2023 Airfield Inspection and Data\Airbridge Safety Inspections\Airbridge Safety Inspection 2023.xlsm")
    closedbBook.Sheets("Data").Copy After:=ThisWorkbook.Sheets(1)
    ThisWorkbook.Sheets(3).Name = "Airbridge Inspection Audit"
    closedbBook.Close SaveChanges:=False

    'Imports as "Data(3)" - rename to "Airbridge Operation Audit"
    Set closedbBook = Workbooks.Open("\\gatwick\Group9\Airfield Operations\2023 Airfield Inspection and Data\Aircraft Turnaround Audits\Airbridge Operation Safety Audit 2023.xlsm")
    closedbBook.Sheets("Data").Copy After:=ThisWorkbook.Sheets(1)
    ThisWorkbook.Sheets(4).Name = "Airbridge Operation Audit"
    closedbBook.Close SaveChanges:=False

    Application.ScreenUpdating = True
    ActiveWorkbook.Save
    Sheets("Master").Select
    ActiveWorkbook.Save

    MsgBox "Audit Data Copied For The Month"
End Sub

In this version of the code, after each worksheet is copied, its name is set using the Name property of the worksheet object. The sheet names are set to "Turnaround Audit", "Airbridge Inspection Audit", and "Airbridge Operation Audit" as you specified.
Take note also that I added line breaks to make the code easier to read, and removed the unnecessary ActiveSheet.Select line.

Hi @sleek12.

Thanks for the reply with this. I’m sorry I haven’t replied earlier I just haven’t been on the site. I’ll try this. Once again thank you.