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

promo1313

New Member
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

Well-known Member
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
 

promo1313

New Member
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
 

Kenneth Hobson

Well-known Member
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
 

promo1313

New Member
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
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top