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

 

[BrianDMiller]

ok so i've basically taken your idea of having an NSR template but i've done it slightly different. the code is still on the user workbook because i want the versatility of the user being able to create an NSR report for just their tracker and the manager to be able to complile all 10. the main difference now is that instead of opening a new blank workbook and calling it NSR report, it's opening a template so I have a consistent file to refer to. so the goal is about the same, if the NSR template is not open then open it and copy data to the next blank row. if it is already open, copy the data to the open template. it works with one small exception. if the template is not open, it opens it and then gives me a run-time error '91': object variable or With block variable not set and doesn't copy the data. i can choose "end" and then run it again and it copies data. i can close it and open the next tracker and it will paste the data to the open template. just need to get past the error on the first iteration.

Set sh2 = G_Report
lastrow2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
Dim RT As Workbook
    On Error Resume Next
    Set RT = Workbooks("NSR_Template.xlsm")
        If RT Is Nothing Then
                Workbooks.Open ("S:\Registrar\New Start Scheduling Team\New Start Scheduling Trackers\NSR_Template.xlsm")
        End If
    On Error GoTo 0
    'copy data to NSRReport
    Set sh3 = RT.Sheets("Sheet1")
    nextrow = sh3.Range("A" & Rows.Count).End(xlUp).Row + 1
    sh2.Range("A1:A3001").AutoFilter Field:=1, Criteria1:="<>"
    sh2.Range("A2:J" & lastrow2).Copy
    sh3.Range("A" & nextrow).PasteSpecial Paste:=xlPasteValues
    sh3.Columns("A:J").AutoFit

 

[AlphaFrog]

You're getting close.

    [color=darkblue]Set[/color] RT = Workbooks("NSR_Template.xlsm")
    [color=darkblue]If[/color] RT [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
           [color=red]Set RT =[/color] Workbooks.Open("S:\Registrar\New Start Scheduling Team\New Start Scheduling Trackers\NSR_Template.xlsm")
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0

 

[BrianDMiller]

bravo kind sir. i really appreciate all the help.