Copy Row 2 and 3 from a closed workbook and paste it into my active workbook

[Twollaston]

I'm trying to create a macro that sets up my reports with a header and spaces above and before the data

Essentially what I want to do is add a column in the front of my data, add 6 Rows above my data, turn gridlines off, and open my workbook that contains my header and copy and paste it into the report.

I created a macro to do it but i'm having issues with the pasting portion. All the rows columns and gridlines work fine. It opens the workbook with the header and it copies it, but i don't know how to call back the active report. I tried to create a variable for the original report sheet so I can call back to it when i need to paste, but it's not working for me. Anyone that can help me amend this slightly so that it will work?

Note: The header workbook will always be in the same path with the same name, but the original report I'm creating will never have the same name or path

What I have so far:

Sub AddHeaderAndRowsAndColumn()


OrigWork = ThisWorkbook.ActiveSheet.Name
'
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown


    ActiveWindow.DisplayGridlines = False
    Workbooks.Open Filename:= _
        "W:\Dealer Sales Reporting Group\Wesley\Report Header.xlsx"
    Rows("2:3").Select
    Selection.Copy
    OrigWork.Activate
    ActiveSheet.Rows("2:3").Select
    Selection.Paste
    Windows("Report Header.xlsx").Activate
    ActiveWindow.Close
    
End Sub

 

[Fluff]

How about

Sub AddHeaderAndRowsAndColumn()
    Dim OrigWork As Worksheet

    Set OrigWork = ThisWorkbook.ActiveSheet
'
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("1:1").Resize(3).Insert

    ActiveWindow.DisplayGridlines = False
    Workbooks.Open Filename:= _
        "W:\Dealer Sales Reporting Group\Wesley\Report Header.xlsx"
    Rows("2:3").Copy OrigWork.Range("A2")
    ActiveWorkbook.Close False
    
End Sub

 

[Twollaston]

How about

It did everything right, except it didn't paste the header into my original sheet

 

[Fluff]

If you step through the code using F8, is the Report Header workbook active when you get to this line

Rows("2:3").Copy OrigWork.Range("A2")

and is the right sheet in that workbook active?

 

[Twollaston]

If you step through the code using F8, is the Report Header workbook active when you get to this line

Rows("2:3").Copy OrigWork.Range("A2")

and is the right sheet in that workbook active?

Okay I see what it's doing.

My personal macro workbook is always open.

When I run the Macro the active sheet is my report sheet, but I also have my personal workbook open and it's adding the spaces and turning the gridlines off for the report sheet, but when it goes to paste the header, it pastes it into my Personal Macro Workbook instead of the report sheet

When I step through line:
Workbooks.Open Filename:= _ "W:\Dealer Sales Reporting Group\Wesley\Report Header.xlsx"

The macro ends. It's not allowing me to step through:
Rows("2:3").Copy OrigWork.Range("A2")
ActiveWorkbook.Close False

 

[Fluff]

In that case use

Set OrigWork = Activeworkbook.ActiveSheet

 

[Twollaston]

In that case use

Set OrigWork = Activeworkbook.ActiveSheet

Perfect, you really are the best Fluff. Thanks alot for helping me with that, it's working exactly as I hoped. Have a good Day

 

[Fluff]

My pleasure & thanks for the feedback