Check if new workbook was created

BrianDMiller

New Member
Joined
Apr 23, 2014
Messages
18
I have a code that opens a new workbook and pastes into it from another workbook. I have several workbooks that will use this code so what I want to do is be able is have excel see if the new workbook (which is unsaved and only a temporary holding place to compile data from multiple workbooks) is already open and paste the data at the end of the existing workbook rather than creating another new workbook. Since I'm not saving it I can't tell it to look for a specific file path/name. It has to work w/o saving. So it boils down to how can i have excel check if NSRReport is open?
Code:
Set NSRReport = Workbooks.Add()
Set sh2 = NSRReport.Sheets("Sheet1")
Set sh3 = G_Report
lastrow2 = sh3.Range("A" & Rows.Count).End(xlUp).Row

sh3.Range("A1:I1").Copy
sh2.Range("A1").PasteSpecial

sh3.Range("A1:A3001").AutoFilter Field:=1, Criteria1:="<>"
sh3.Range("A2:I" & lastrow2).Copy
sh2.Range("A2").PasteSpecial Paste:=xlPasteValues
sh2.Columns("A:I").AutoFit
Brian
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Put this at the very top of the code module above all the macro code

Code:
Public NSRReport As Workbook


This will create a new workbook only if it's not yet created.
Code:
If NSRReport Is Nothing Then Set NSRReport = Workbooks.Add()
 
Last edited:
Upvote 0
So i made the public declaration as instructed and then adjusted the code but it doesn't seem to do anything now.

Code:
    If Not NSRReport Is Nothing Then
        Set NSRReport = Workbooks.Add()
        Set sh2 = NSRReport.Sheets("Sheet1")
        Set sh3 = G_Report
        lastrow2 = sh3.Range("A" & Rows.Count).End(xlUp).Row
        
        sh3.Range("A1:I1").Copy
        sh2.Range("A1").PasteSpecial
        
        sh3.Range("A1:A3001").AutoFilter Field:=1, Criteria1:="<>"
        sh3.Range("A2:I" & lastrow2).Copy
        sh2.Range("A2").PasteSpecial Paste:=xlPasteValues
        sh2.Columns("A:I").AutoFit
    End If
 
Upvote 0
So i made the public declaration as instructed and then adjusted the code but it doesn't seem to do anything now.

Code:
    If Not NSRReport Is Nothing Then
        Set NSRReport = Workbooks.Add()
        Set sh2 = NSRReport.Sheets("Sheet1")
        Set sh3 = G_Report
        lastrow2 = sh3.Range("A" & Rows.Count).End(xlUp).Row
        
        sh3.Range("A1:I1").Copy
        sh2.Range("A1").PasteSpecial
        
        sh3.Range("A1:A3001").AutoFilter Field:=1, Criteria1:="<>"
        sh3.Range("A2:I" & lastrow2).Copy
        sh2.Range("A2").PasteSpecial Paste:=xlPasteValues
        sh2.Columns("A:I").AutoFit
    End If

Remove the NOT. I had edited that out later.
 
Upvote 0
I've added an "else" statement and some notes to explain what i'm doing. it isn't recognizing that the NSRReport is already open, just keeps opening a new one.

Code:
Set sh3 = G_Report
    If NSRReport Is Nothing Then
        Set Report = Workbooks.Add()
        Set sh2 = Report.Sheets("Sheet1")
        lastrow2 = sh3.Range("A" & Rows.Count).End(xlUp).Row
        'copy column headers
        sh3.Range("A1:I1").Copy
        sh2.Range("A1").PasteSpecial
        'copy data
        sh3.Range("A1:A3001").AutoFilter Field:=1, Criteria1:="<>"
        sh3.Range("A2:I" & lastrow2).Copy
        sh2.Range("A2").PasteSpecial Paste:=xlPasteValues
        sh2.Columns("A:I").AutoFit
    Else
        'just copy data to existing NSRReport sheet
        sh3.Range("A1:A3001").AutoFilter Field:=1, Criteria1:="<>"
        sh3.Range("A2:I" & lastrow2).Copy
        sh2.Range("A2").PasteSpecial Paste:=xlPasteValues
        sh2.Columns("A:I").AutoFit
    End If
 
Last edited:
Upvote 0
These should be the same
Code:
    If [COLOR=#ff0000]NSRReport [/COLOR]Is Nothing Then
        Set [COLOR=#ff0000]Report [/COLOR]= Workbooks.Add()
        Set sh2 = [COLOR=#ff0000]Report[/COLOR].Sheets("Sheet1")
Is this code within a loop

Code:
[COLOR=darkblue]Set[/COLOR] sh3 = G_Report
    [COLOR=darkblue]If[/COLOR] NSRReport [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'Make repoort with headers[/COLOR]
        [COLOR=darkblue]Set[/COLOR] NSRReport = Workbooks.Add()
        [COLOR=darkblue]Set[/COLOR] sh2 = NSRReport.Sheets("Sheet1")
        lastrow2 = sh3.Range("A" & Rows.Count).End(xlUp).Row
        [COLOR=green]'copy column headers[/COLOR]
        sh3.Range("A1:I1").Copy
        sh2.Range("A1").PasteSpecial
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=green]'copy data[/COLOR]
    sh3.Range("A1:A3001").AutoFilter Field:=1, Criteria1:="<>"
    sh3.Range("A2:I" & lastrow2).Copy
    sh2.Range("A2").PasteSpecial Paste:=xlPasteValues
    sh2.Columns("A:I").AutoFit
 
Last edited:
Upvote 0
Ok it seems to be working with:

Code:
Set sh3 = G_Report
lastrow2 = sh3.Range("A" & Rows.Count).End(xlUp).Row
    If NSRReport Is Nothing Then
        Set NSRReport = Workbooks.Add()
        Set sh2 = NSRReport.Sheets("Sheet1")
        'copy column headers
        sh3.Range("A1:I1").Copy
        sh2.Range("A1").PasteSpecial
    End If
        
    'copy data NSRReport
    Set sh2 = NSRReport.Sheets("Sheet1")
    lastrow3 = sh2.Range("A" & Rows.Count).End(xlUp).Row
    sh3.Range("A1:A3001").AutoFilter Field:=1, Criteria1:="<>"
    sh3.Range("A2:I" & lastrow2).Copy
    sh2.Range("A" & lastrow3).PasteSpecial Paste:=xlPasteValues
    sh2.Columns("A:I").AutoFit

I hadn't declared "lastrow" for the NSR Report to add on to the bottom of that report since previously it was only using a blank report. a few other declaration issues resolved as well. Thanks for the prompt help!
 
Upvote 0
need to revisit this. when i close the original data source (that created the NSRReport) and open the next report i want to pull data from it no longer recognizes that there is an active NSRReport and creates a new one. I'm guessing because the declaration that the new, unsaved book is called "NSRReport" (since it was never saved) is removed once the workbook is closed. any thoughts? and even if i don't close the original just open the new one and run the same macro is still creates a new one.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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