Check if new workbook was created

[BrianDMiller]

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?

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

 

[AlphaFrog]

Flying blind here. Can you post your whole code?

Are you still using the public declaration for NSRReport so it persists after the procedure ends.

 

[BrianDMiller]

Sure thing. Yes, still using the public dec.

Sub CreateNSR()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim Report As Workbook
Dim lastrow As Long
Dim lastrow2 As Long
Dim nextrow As Long
Dim x As Integer
Dim Conflict As String
Dim COE As String
Dim Scheduled As String
Set sh1 = B_Tracking
lastrow = sh1.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
    'check for issues with data
    For x = 2 To lastrow
        Conflict = sh1.Range("G" & x).Value
        COE = sh1.Range("I" & x).Value
        Scheduled = sh1.Range("D" & x).Value
        If Scheduled = "Yes" Then
            If Conflict <> "" Or COE <> "" Then
                MsgBox ("You must fix issues before continuing!")
                Exit Sub
            End If
        End If
    Next x
' Problem starts here
Set sh2 = G_Report
lastrow2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
    If NSRReport Is Nothing Then
        Set NSRReport = Workbooks.Add()
        Set sh3 = NSRReport.Sheets("Sheet1")
        'copy column headers
        sh2.Range("A1:I1").Copy
        sh3.Range("A1").PasteSpecial
    End If
        
    'copy data NSRReport
    Set sh3 = NSRReport.Sheets("Sheet1")
    nextrow = sh3.Range("A" & Rows.Count).End(xlUp).Row + 1
    sh2.Range("A1:A3001").AutoFilter Field:=1, Criteria1:="<>"
    sh2.Range("A2:I" & lastrow2).Copy
    sh3.Range("A" & nextrow).PasteSpecial Paste:=xlPasteValues
    sh3.Columns("A:I").AutoFit
    
Application.ScreenUpdating = True
End Sub

 

[AlphaFrog]

Where are you defining B_Tracking and G_Report? Where are you closing their parent workbooks? How are you calling this procedure? Can you show the public decs also?

Use this to ensure the new workbook is added to the same instance of Excel that has the code workbook.

[color=darkblue]Set[/color] NSRReport = [B]ThisWorkbook.Parent[/B].Workbooks.Add()

 

[BrianDMiller]

The only public dec is the one you told me to use. B_tracking and G_report are the sheet names in the workbook that holds my data. Sorry i wasn't clear with that. B_tracking holds all the data that the user interacts with and G_report displays records that meet specific criteria. Those are the records i want to copy onto the NSRReport. There are 10 workbooks that contain data and i want to be able to open each wb and with 1 click copy the records off of G_report onto the NSRReport. I'm trying to avoid creating 10 NSRreports and having to compile them manually.

I'll try adding the code you gave me but unfortunately i'm not familiar with "parent" workbooks or what that means.

 

[BrianDMiller]

changed decleration

Set NSRReport = ThisWorkbook.Parent.Workbooks.Add()

Still doesn't recognize that the NSRReport is already open and it creates a new one. I'm really at a loss for anything else to try. i can run the macro multiple times from the same wb and it will recognize that the NSRreport is already open and just paste at the end of the existing data instead of making a new NSRReport. But from another workbook that uses the exact same template it will not recognize the open NSRReport and instead creates a new one.

 

[AlphaFrog]

If you close the workbook that contains this code or more specifically the public declaration for NSRReport, it will also close loose the public variable NSRReport. Is that what you're doing? Put this code in and run it from a workbook that remains open.

 

[BrianDMiller]

yes that is the case. when i left the wb open that made the declaration it still wouldn't do it. and since the other 9 workbooks all work off the same template they should all be making the same declarations, no? i will not actually be the one using these reports so i'm trying to avoid having additional stipulations like leaving something open.

 

[AlphaFrog]

yes that is the case. when i left the wb open that made the declaration it still wouldn't do it. and since the other 9 workbooks all work off the same template they should all be making the same declarations, no? i will not actually be the one using these reports so i'm trying to avoid having additional stipulations like leaving something open.

You don't have one workbook with this code in it that you start with and it remains open? Then it just copies from the other nine workbooks. That's how most people would do it.

Otherwise, describe how you want the user to do this; where they start; what workbook is open, then do they close a workbook and open what?

 

[BrianDMiller]

there are 10 wb's (trackers) that are identical. a database separates records and dumps them into the trackers. 10 different users work with the data and mark conflicts on certain records. the G_report sheet lists all the records that have certain conflicts marked because they need to be sent to a different department to resolve. originally the idea was for each user to create their NSRreport and email it to their manager and she would copy/paste all 10 reports onto one. then i thought, she would most likely just access all 10 trackers herself and just generate the reports and copy/paste them together. now what i would like to do is have her be able to open each tracker and create the report and have excel know that if a NSRreport is already open, instead of making a new one, just add on to the existing report so that the manager doesn't have the cumbersome task of copy/pasting 10 sheets together.

 

[AlphaFrog]

there are 10 wb's (trackers) that are identical. a database separates records and dumps them into the trackers. 10 different users work with the data and mark conflicts on certain records. the G_report sheet lists all the records that have certain conflicts marked because they need to be sent to a different department to resolve. originally the idea was for each user to create their NSRreport and email it to their manager and she would copy/paste all 10 reports onto one. then i thought, she would most likely just access all 10 trackers herself and just generate the reports and copy/paste them together. now what i would like to do is have her be able to open each tracker and create the report and have excel know that if a NSRreport is already open, instead of making a new one, just add on to the existing report so that the manager doesn't have the cumbersome task of copy/pasting 10 sheets together.

Give her an empty NSRreport template workbook with the code in it. The NSRreport template imports the data from the 10 trackers.