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

Twollaston

Board Regular
Joined
May 24, 2019
Messages
241
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:

Code:
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
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
How about
Code:
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
 
Upvote 0
If you step through the code using F8, is the Report Header workbook active when you get to this line
Code:
Rows("2:3").Copy OrigWork.Range("A2")
and is the right sheet in that workbook active?
 
Upvote 0
If you step through the code using F8, is the Report Header workbook active when you get to this line
Code:
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
 
Last edited:
Upvote 0
In that case use
Code:
Set OrigWork = Activeworkbook.ActiveSheet
 
Upvote 0
My pleasure & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top