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

Twollaston

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,206
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,206
Office Version
365
Platform
Windows
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?
 

Twollaston

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,206
Office Version
365
Platform
Windows
In that case use
Code:
Set OrigWork = Activeworkbook.ActiveSheet
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,206
Office Version
365
Platform
Windows
My pleasure & thanks for the feedback
 

Forum statistics

Threads
1,081,991
Messages
5,362,595
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top