Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Check if new workbook was created

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Check if new workbook was created

    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 by BrianDMiller; Apr 23rd, 2014 at 04:44 PM.

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,953
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Check if new workbook was created

    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 by AlphaFrog; Apr 23rd, 2014 at 05:10 PM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if new workbook was created

    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

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,953
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Check if new workbook was created

    Quote Originally Posted by BrianDMiller View Post
    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.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  5. #5
    New Member
    Join Date
    Apr 2014
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if new workbook was created

    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 by BrianDMiller; Apr 23rd, 2014 at 05:38 PM.

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,953
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Check if new workbook was created

    These should be the same
    Code:
        If NSRReport Is Nothing Then
            Set Report = Workbooks.Add()
            Set sh2 = Report.Sheets("Sheet1")
    Is this code within a loop

    Code:
    Set sh3 = G_Report
        If NSRReport Is Nothing Then
            'Make repoort with headers
            Set NSRReport = Workbooks.Add()
            Set sh2 = NSRReport.Sheets("Sheet1")
            lastrow2 = sh3.Range("A" & Rows.Count).End(xlUp).Row
            'copy column headers
            sh3.Range("A1:I1").Copy
            sh2.Range("A1").PasteSpecial
        End If
        
        '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
    Last edited by AlphaFrog; Apr 23rd, 2014 at 05:44 PM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  7. #7
    New Member
    Join Date
    Apr 2014
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if new workbook was created

    yes they should, excel crashed on me and i forgot to fix that.

  8. #8
    New Member
    Join Date
    Apr 2014
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if new workbook was created

    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!

  9. #9
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,953
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Check if new workbook was created

    You're welcome.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  10. #10
    New Member
    Join Date
    Apr 2014
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if new workbook was created

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •