VBA Rename sheets and Workbooks

[Jimmy_m]

Hello,

I have a template workbook that is used to fill information into different workbooks. The code below work great for that, here are a couple of Items I'd like to add if possible.

• rename the 4 worksheets in the "coversheets.xlsx" workbook with the cell information from the active workbook/ Template workbook
• rename the "coversheet.xlsx" workbook using the cell information from the active workbook/ Template workbook
• rename the "BOM Civil.xlsm" workbook using the cell information from the active workbook/ Template workbook
• rename the "BOM Pull.xlsm" workbook using the cell information from the active workbook/ Template workbook
• rename the "BOM ISP.xlsm" workbook using the cell information from the active workbook/ Template workbook
• rename the "BOM Act.xlsm" workbook using the cell information from the active workbook/ Template workbook

All of the workbooks are located in the same folder C:\Users\james\Desktop\BOM

Here is the code I'm using now.

Thanks

Sub PasteSpecial()

Dim Wb As Workbook

Set Wb = ThisWorkbook

'Civil
Workbooks("Coversheets.xlsx").Worksheets("Civil").Range("B2:B4").Value = Wb.Worksheets("TEMPLATE").Range("B17:B19").Value 'Planner,PM,Inspector
Workbooks("Coversheets.xlsx").Worksheets("Civil").Range("B6").Value = Wb.Worksheets("TEMPLATE").Range("B9").Value 'Location
Workbooks("Coversheets.xlsx").Worksheets("Civil").Range("B7").Value = Wb.Worksheets("TEMPLATE").Range("E7").Value 'PID ORacle
Workbooks("Coversheets.xlsx").Worksheets("Civil").Range("B8").Value = Wb.Worksheets("TEMPLATE").Range("B11").Value 'site contact
Workbooks("Coversheets.xlsx").Worksheets("Civil").Range("B9").Value = Wb.Worksheets("TEMPLATE").Range("C14").Value 'Permits
Workbooks("Coversheets.xlsx").Worksheets("Civil").Range("B10").Value = Wb.Worksheets("TEMPLATE").Range("B16").Value 'Node area
Workbooks("Coversheets.xlsx").Worksheets("Civil").Range("B13:B22").Value = Wb.Worksheets("TEMPLATE").Range("B23:B32").Value 'Project description

'Pull
Workbooks("Coversheets.xlsx").Worksheets("Pull").Range("B2:B4").Value = Wb.Worksheets("TEMPLATE").Range("B17:B19").Value 'Planner,PM,Inspector
Workbooks("Coversheets.xlsx").Worksheets("Pull").Range("B6").Value = Wb.Worksheets("TEMPLATE").Range("B9").Value 'Location
Workbooks("Coversheets.xlsx").Worksheets("Pull").Range("B7").Value = Wb.Worksheets("TEMPLATE").Range("E7").Value 'PID ORacle
Workbooks("Coversheets.xlsx").Worksheets("Pull").Range("B8").Value = Wb.Worksheets("TEMPLATE").Range("B11").Value 'site contact
Workbooks("Coversheets.xlsx").Worksheets("Pull").Range("B9").Value = Wb.Worksheets("TEMPLATE").Range("C14").Value 'Permits
Workbooks("Coversheets.xlsx").Worksheets("Pull").Range("B10").Value = Wb.Worksheets("TEMPLATE").Range("B16").Value 'Node area
Workbooks("Coversheets.xlsx").Worksheets("Pull").Range("B13:B22").Value = Wb.Worksheets("TEMPLATE").Range("F23:F32").Value 'Project description

'ISP
Workbooks("Coversheets.xlsx").Worksheets("ISP").Range("B2:B4").Value = Wb.Worksheets("TEMPLATE").Range("B17:B19").Value 'Planner,PM,Inspector
Workbooks("Coversheets.xlsx").Worksheets("ISP").Range("B6").Value = Wb.Worksheets("TEMPLATE").Range("B9").Value 'Location
Workbooks("Coversheets.xlsx").Worksheets("ISP").Range("B7").Value = Wb.Worksheets("TEMPLATE").Range("E7").Value 'PID ORacle
Workbooks("Coversheets.xlsx").Worksheets("ISP").Range("B8").Value = Wb.Worksheets("TEMPLATE").Range("B11").Value 'site contact
Workbooks("Coversheets.xlsx").Worksheets("ISP").Range("B9").Value = Wb.Worksheets("TEMPLATE").Range("C14").Value 'Permits
Workbooks("Coversheets.xlsx").Worksheets("ISP").Range("B10").Value = Wb.Worksheets("TEMPLATE").Range("B16").Value 'Node area
Workbooks("Coversheets.xlsx").Worksheets("ISP").Range("B13:B22").Value = Wb.Worksheets("TEMPLATE").Range("J23:J32").Value 'Project description

'Act
Workbooks("Coversheets.xlsx").Worksheets("ACT").Range("B2:B4").Value = Wb.Worksheets("TEMPLATE").Range("B17:B19").Value 'Planner,PM,Inspector
Workbooks("Coversheets.xlsx").Worksheets("ACT").Range("B6").Value = Wb.Worksheets("TEMPLATE").Range("B9").Value 'Location
Workbooks("Coversheets.xlsx").Worksheets("ACT").Range("B7").Value = Wb.Worksheets("TEMPLATE").Range("E7").Value 'PID ORacle
Workbooks("Coversheets.xlsx").Worksheets("ACT").Range("B8").Value = Wb.Worksheets("TEMPLATE").Range("B11").Value 'site contact
Workbooks("Coversheets.xlsx").Worksheets("ACT").Range("B9").Value = Wb.Worksheets("TEMPLATE").Range("C14").Value 'Permits
Workbooks("Coversheets.xlsx").Worksheets("ACT").Range("B10").Value = Wb.Worksheets("TEMPLATE").Range("B16").Value 'Node area
Workbooks("Coversheets.xlsx").Worksheets("ACT").Range("B13:B22").Value = Wb.Worksheets("TEMPLATE").Range("N23:N32").Value 'Project description

Workbooks("BOM Civil.xlsm").Worksheets("Order").Range("C6").Value = Wb.Worksheets("TEMPLATE").Range("B17").Value 'Planner
Workbooks("BOM Civil.xlsm").Worksheets("Order").Range("D6").Value = Wb.Worksheets("TEMPLATE").Range("E9").Value 'Project name
Workbooks("BOM Civil.xlsm").Worksheets("Order").Range("E6").Value = Wb.Worksheets("TEMPLATE").Range("C34").Value 'Project code ID
Workbooks("BOM Civil.xlsm").Worksheets("Order").Range("F6").Value = Wb.Worksheets("TEMPLATE").Range("B6").Value 'RPN
Workbooks("BOM Civil.xlsm").Worksheets("Order").Range("G6").Value = Wb.Worksheets("TEMPLATE").Range("B35").Value 'CWO

Workbooks("BOM Pull.xlsm").Worksheets("Order").Range("C6").Value = Wb.Worksheets("TEMPLATE").Range("B17").Value 'Planner
Workbooks("BOM Pull.xlsm").Worksheets("Order").Range("D6").Value = Wb.Worksheets("TEMPLATE").Range("E9").Value 'Project name
Workbooks("BOM Pull.xlsm").Worksheets("Order").Range("E6").Value = Wb.Worksheets("TEMPLATE").Range("C34").Value 'Project code ID
Workbooks("BOM Pull.xlsm").Worksheets("Order").Range("F6").Value = Wb.Worksheets("TEMPLATE").Range("B6").Value 'RPN
Workbooks("BOM Pull.xlsm").Worksheets("Order").Range("G6").Value = Wb.Worksheets("TEMPLATE").Range("F35").Value 'CWO

Workbooks("BOM ISP.xlsm").Worksheets("Order").Range("C6").Value = Wb.Worksheets("TEMPLATE").Range("B17").Value 'Planner
Workbooks("BOM ISP.xlsm").Worksheets("Order").Range("D6").Value = Wb.Worksheets("TEMPLATE").Range("E9").Value 'Project name
Workbooks("BOM ISP.xlsm").Worksheets("Order").Range("E6").Value = Wb.Worksheets("TEMPLATE").Range("C34").Value 'Project code ID
Workbooks("BOM ISP.xlsm").Worksheets("Order").Range("F6").Value = Wb.Worksheets("TEMPLATE").Range("B6").Value 'RPN
Workbooks("BOM ISP.xlsm").Worksheets("Order").Range("G6").Value = Wb.Worksheets("TEMPLATE").Range("J35").Value 'CWO

Workbooks("BOM ACT.xlsm").Worksheets("Order").Range("C6").Value = Wb.Worksheets("TEMPLATE").Range("B17").Value 'Planner
Workbooks("BOM ACT.xlsm").Worksheets("Order").Range("D6").Value = Wb.Worksheets("TEMPLATE").Range("E9").Value 'Project name
Workbooks("BOM ACT.xlsm").Worksheets("Order").Range("E6").Value = Wb.Worksheets("TEMPLATE").Range("O34").Value 'Project code ID
Workbooks("BOM ACT.xlsm").Worksheets("Order").Range("F6").Value = Wb.Worksheets("TEMPLATE").Range("B6").Value 'RPN
Workbooks("BOM ACT.xlsm").Worksheets("Order").Range("G6").Value = Wb.Worksheets("TEMPLATE").Range("N35").Value 'CWO




End Sub

 

[Zot]

It is not clear which worksheet you want to rename. The syntax to rename worksheet and workbook are quite straightforward.

To simplify/shorten code, I'd also define sheet.

To rename worksheet (since you have already defined Wb as ActiveWorkbook

Dim wsTemplate As Worksheet

Set wsTemplate = Wb.Sheets("TEMPLATE")
wsTemplate.Name = NewName

To rename workbook

Name "C:\Users\james\Desktop\BOM\BookA.xlsx" As "C:\Users\james\Desktop\BOM\BookB.xlsx"

 

[Jimmy_m]

It is not clear which worksheet you want to rename. The syntax to rename worksheet and workbook are quite straightforward.

To simplify/shorten code, I'd also define sheet.

To rename worksheet (since you have already defined Wb as ActiveWorkbook

Dim wsTemplate As Worksheet

Set wsTemplate = Wb.Sheets("TEMPLATE")
wsTemplate.Name = NewName

To rename workbook

Name "C:\Users\james\Desktop\BOM\BookA.xlsx" As "C:\Users\james\Desktop\BOM\BookB.xlsx"

Hi Zot, thanks for the reply.
The worksheets are in the workbook "Coversheets" and not the active workbook. The sheet names would come from 4 cell values in the Template wb (ActiveWorkbook).
Need a way to copy the cell info from the Template wb then make the Coversheet wb active to paste the name in a ws. If that makes sense.
In your example Name "C:\Users\james\Desktop\BOM\BookA.xlsx" As "C:\Users\james\Desktop\BOM\BookB.xlsx" is there a way to populate the "BookB" value with a cell value from the template wb?
Thanks

 

[Zot]

If you have workbook opened either active (activated or not), you can assign variable like this:
Set wk = Workbooks("Example.xlsx")

If you want to assign parameter when opening workbook
Set wk =Workbooks.Open ("C:\Docs\Example.xlsx")

By assigning variable to workbook and then worksheet, you don't have to bother about the need to activate this and that workbook. You can just call it call it. Example

Dim wsTemplate As Worksheet, wsCivilOrder As Worksheet
Dim wb As Workbook, wbCivil As Workbook

Set wb = ActiveWorkbook
Set wsTemplate = wb.Sheets("TEMPLATE")
Set wbCivil = Workbooks("BOM Civil.xlsm")
Set wsCivilOrder = wbCivil.Sheets("Order")

'Instead of
Workbooks("BOM Civil.xlsm").Worksheets("Order").Range("C6").Value = wb.Worksheets("TEMPLATE").Range("B17").Value

'it can be just
wsCivilOrder.Range("C6").Value = wsTemplate.Range("B17").Value

To rename a workbook, that workbook cannot be active or opened. So, it has to be close and macro is run from another workbook. You can have a workbook to run macro to work on both workbook Template and other workbooks like using the macro to automatically open and assign variable by calling using parameter as above.

Renaming can be like this:
Name "C:\Users\james\Desktop\BOM\BookA.xlsx" As "C:\Users\james\Desktop\BOM\" & wsTemplate.Range("B17")

I'm not a programmer or do macro even on weekly basis. Just write macro whenever I want to simplify my routine work. So, the code example might contains syntax error . Anyway, it will give you the idea on what you are trying to do, I hope.

 

[Jimmy_m]

If you have workbook opened either active (activated or not), you can assign variable like this:
Set wk = Workbooks("Example.xlsx")

If you want to assign parameter when opening workbook
Set wk =Workbooks.Open ("C:\Docs\Example.xlsx")

By assigning variable to workbook and then worksheet, you don't have to bother about the need to activate this and that workbook. You can just call it call it. Example

Dim wsTemplate As Worksheet, wsCivilOrder As Worksheet
Dim wb As Workbook, wbCivil As Workbook

Set wb = ActiveWorkbook
Set wsTemplate = wb.Sheets("TEMPLATE")
Set wbCivil = Workbooks("BOM Civil.xlsm")
Set wsCivilOrder = wbCivil.Sheets("Order")

'Instead of
Workbooks("BOM Civil.xlsm").Worksheets("Order").Range("C6").Value = wb.Worksheets("TEMPLATE").Range("B17").Value

'it can be just
wsCivilOrder.Range("C6").Value = wsTemplate.Range("B17").Value

To rename a workbook, that workbook cannot be active or opened. So, it has to be close and macro is run from another workbook. You can have a workbook to run macro to work on both workbook Template and other workbooks like using the macro to automatically open and assign variable by calling using parameter as above.

Renaming can be like this:
Name "C:\Users\james\Desktop\BOM\BookA.xlsx" As "C:\Users\james\Desktop\BOM\" & wsTemplate.Range("B17")

I'm not a programmer or do macro even on weekly basis. Just write macro whenever I want to simplify my routine work. So, the code example might contains syntax error . Anyway, it will give you the idea on what you are trying to do, I hope.

Thank you very much Zot. Everything is working great, I've learned so much.