CODEWITHSUNDEEP
vbaexcel
Rafael Osipov
Rafael Osipov

Reputation: 740

VBA Excel - Paste to each Worksheet in a Workbook

I want to copy a header from one workbook and paste it to each worksheet in a different workbook on the same range.

'header
Dim OP_wb As Workbook: Set OP_wb = ActiveWorkbook
Dim header As Range
Set header = OP_wb.Sheets("Optic Main").Range("A1:F1")

Dim Part_WS As Worksheet


Demand_WB.Activate

For Each Part_WS In Demand_WB
    header.Copy
    Demand_WB.Activate
    ActiveSheet.Range("A2").Paste
Next Part_WS

I get this error:

object doesn't support this property of method

on line:

For Each Part_WS In Demand_WB

Can someone help fix the code?

this is the code comes before my proble:

Dim Demand_WB As Workbook

Workbooks.Add


For i = 1 To 5

    Worksheets.Add

Next i

ActiveWorkbook.SaveAs Filename:="C:\Users\rosipov\Desktop\eliran\MFG - GSS\Demand_Optics " & Format(Now(), "dd.mm.yyyy") & ".xlsx"



Worksheets("Sheet1").Name = "Illuminators"
Worksheets("Sheet2").Name = "Analyzers"
Worksheets("Sheet3").Name = "Chuck"
Worksheets("Sheet4").Name = "Compensators"
Worksheets("Sheet5").Name = "Spectrometers"
Worksheets("Sheet6").Name = "LDSR"

Set Demand_WB = Workbooks("C:\Users\rosipov\Desktop\eliran\MFG - GSS\Demand_Optics " & Format(Now(), "dd.mm.yyyy") & ".xlsx")

Upvotes: 1

Views: 409

Answers (1)

kaza
kaza

Reputation: 2327

For Each Part_WS In Demand_WB.Sheets
   header.Copy
   Part_WS.Range("A2").PasteSpecial xlPasteAll 
Next Part_WS

Upvotes: 1

Related Questions