hi
@Joe4 thanks for the reply. this is the code below i use. basically i have a template which creates pivottables which then feed into the workbook which contains worksheets for each day of the month. the code below will be for the 10th september.
Sub Copy_Method10()
'Copy range to another workbook
Workbooks("Prod log 2021-2022.xlsm").Worksheets("PivotTable").Range("B2:E30").Copy _
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("M2")
'Copy range to clipboard
Workbooks("Prod log 2021-2022.xlsm").Worksheets("PivotTable").Range("B2:E30").Copy
'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("M2").PasteSpecial Paste:=xlPasteValues
'Copy range to another workbook
Workbooks("Prod log 2021-2022.xlsm").Worksheets("CondAccPivTab").Range("B2:C30").Copy _
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("R2")
'Copy range to clipboard
Workbooks("Prod log 2021-2022.xlsm").Worksheets("CondAccPivTab").Range("B2:E30").Copy
'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("R2").PasteSpecial Paste:=xlPasteValues
'Copy range to another workbook
Workbooks("Prod log 2021-2022.xlsm").Worksheets("PendingPivotTable").Range("B1:E30").Copy _
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("U2")
'Copy range to clipboard
Workbooks("Prod log 2021-2022.xlsm").Worksheets("PendingPivotTable").Range("B1:C1000").Copy
'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("U2").PasteSpecial Paste:=xlPasteValues
End Sub