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

[promo1313]

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

 

[Kenneth Hobson]

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?

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

 

[promo1313]

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?

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.

 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

 

[Kenneth Hobson]

Test on backup copies. Maybe:

'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

 

[promo1313]

Test on backup copies. Maybe:

'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

 

[Kenneth Hobson]

Set visibilty to true just before the close.