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.
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
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
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