Results 1 to 6 of 6

Thread: VBA - I dont want the destination workbook to be visible when opening to copy pasting
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool VBA - I dont want the destination workbook to be visible when opening to copy pasting

    Hi,
    I have a project where I am copying data from one workbook to another using VBA.
    The syntax has data copying from one workbook, opening another workbook, pasting, then save, and close.
    The thing is that the other workbook is visible when it opens, and saves then closes. I want it to be doing this in the background. I tried a couple of syntax, but it hides the workbook completely, and to open it you need to manually unhide it.
    For an overview, the user enters information in the calculator, presses a button which copies the info, opens an email, copies it to the body, then opens another workbook, and copies in a table under the last line.

    this is what i have to open an close:


    Sub OpenWorkbook()


    Workbooks.Open "F:\Department Folders\Accounting\GMD Calculator Journal\GMD_Calculator_Journal.xlsx"

    End Sub



    **the code that Im using to copy paste from one sheet to another goes here***



    Sub CloseWorkbook()


    Workbooks("GMD_Calculator_Journal.xlsx").Close SaveChanges:=True

    End Sub

  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - I dont want the destination workbook to be visible when opening to copy pasting

    Welcome to the forum! Click the # icon on reply toolbar to insert code tags to paste code between.

    It happens so fast, is this still an issue?
    Code:
    Sub Main()
        Dim ws As Worksheet, r As Range
        Set ws = Workbooks.Open(ThisWorkbook.Path & "\CopyFromClosed.xlsx").Worksheets(1)
        ws.Parent.Windows(1).Visible = False
        Set r = ws.Range("A2", ws.Cells(Rows.Count, "A").End(xlUp))
        r.Copy [A2]    
        ws.Parent.Close False
    End Sub

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - I dont want the destination workbook to be visible when opening to copy pasting

    Quote Originally Posted by Kenneth Hobson View Post
    Welcome to the forum! Click the # icon on reply toolbar to insert code tags to paste code between.

    It happens so fast, is this still an issue?
    Code:
    Sub Main()
        Dim ws As Worksheet, r As Range
        Set ws = Workbooks.Open(ThisWorkbook.Path & "\CopyFromClosed.xlsx").Worksheets(1)
        ws.Parent.Windows(1).Visible = False
        Set r = ws.Range("A2", ws.Cells(Rows.Count, "A").End(xlUp))
        r.Copy [A2]    
        ws.Parent.Close False
    End Sub
    Thank you Kenneth,

    Still a little confused. Sorry I'm a bit new to VBA. So I decided to consolidate all 3 subs in 1, and got the below but I am getting an error.

    Code:
     Sub New_Wb()  
      
    'Find the last used row in both sheets and copy and paste data below existing data.
    
    
    Dim wbOpen As Workbook
    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim lCopyLastRow As Long
    Dim lDestLastRow As Long
    
    
    
    
      'Set variables for copy and destination sheets
      Set wbOpen = Workbooks.Open("F:\Department Folders\Accounting\GMD Calculator Journal\GMD_Calculator_Journal.xlsx").Worksheets("Sheet1")
      Set wsCopy = Workbooks("GMD Fee Calculator V03.xlsm").Worksheets("Lookup")
      Set wsDest = Workbooks("GMD_Calculator_Journal.xlsx").Worksheets("Sheet1")
      
        
      '1. Find last used row in the copy range based on data in column A
      lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "F").End(xlUp).Row
        
      '2. Find first blank row in the destination range based on data in column A
      'Offset property moves down 1 row
      lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
    
    
      '3. Copy & Paste Data
      wsCopy.Range("F2:L2" & lCopyLastRow).Copy
        wsDest.Range("A" & lDestLastRow).PasteSpecial xlValues
        Application.ScreenUpdating = True
        
    Workbooks("GMD_Calculator_Journal.xlsx").Close SaveChanges:=True
        
        End Sub

  4. #4
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - I dont want the destination workbook to be visible when opening to copy pasting

    Test on backup copies. Maybe:
    Code:
    'Find the last used row in both sheets and copy and paste data below existing data.
    Sub New_Wb()
        Dim wsCopy As Worksheet, wsDest As Worksheet
        Dim lCopyLastRow As Long, lDestLastRow As Long
        
        Application.ScreenUpdating = False
        
        'Set variables for copy and destination sheets
        Set wsCopy = Workbooks("GMD Fee Calculator V03.xlsm").Worksheets("Lookup")
        Set wsDest = Workbooks.Open( _
            "F:\Department Folders\Accounting\GMD Calculator Journal\GMD_Calculator_Journal.xlsx").Worksheets("Sheet1")
        wsDest.Parent.Windows(1).Visible = False
        
        '1. Find last used row in the copy range based on data in column A
        lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "F").End(xlUp).Row
        
        '2. Find first blank row in the destination range based on data in column A
        'Offset property moves down 1 row
        lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
        
        '3. Copy & Paste Data
        wsCopy.Range("F2", wsCopy.Cells(lCopyLastRow, "L")).Copy
        wsDest.Cells(lDestLastRow, "A").PasteSpecial xlValues
        
        wsDest.Parent.Close True
        
        Application.ScreenUpdating = True
        Application.CutCopyMode = False
    End Sub

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - I dont want the destination workbook to be visible when opening to copy pasting

    Quote Originally Posted by Kenneth Hobson View Post
    Test on backup copies. Maybe:
    Code:
    'Find the last used row in both sheets and copy and paste data below existing data.
    Sub New_Wb()
        Dim wsCopy As Worksheet, wsDest As Worksheet
        Dim lCopyLastRow As Long, lDestLastRow As Long
        
        Application.ScreenUpdating = False
        
        'Set variables for copy and destination sheets
        Set wsCopy = Workbooks("GMD Fee Calculator V03.xlsm").Worksheets("Lookup")
        Set wsDest = Workbooks.Open( _
            "F:\Department Folders\Accounting\GMD Calculator Journal\GMD_Calculator_Journal.xlsx").Worksheets("Sheet1")
        wsDest.Parent.Windows(1).Visible = False
        
        '1. Find last used row in the copy range based on data in column A
        lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "F").End(xlUp).Row
        
        '2. Find first blank row in the destination range based on data in column A
        'Offset property moves down 1 row
        lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
        
        '3. Copy & Paste Data
        wsCopy.Range("F2", wsCopy.Cells(lCopyLastRow, "L")).Copy
        wsDest.Cells(lDestLastRow, "A").PasteSpecial xlValues
        
        wsDest.Parent.Close True
        
        Application.ScreenUpdating = True
        Application.CutCopyMode = False
    End Sub
    Awesome!. Thank you Kenneth. It worked perfect.

    The only thing is that the document saves as hidden, and when you try to open it, you need to select it under unhide.

    but thats a minor workaround.

    thanks again

  6. #6
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - I dont want the destination workbook to be visible when opening to copy pasting

    Set visibilty to true just before the close.

Some videos you may like

User Tag List

Tags for this Thread

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
  •