Copy data from another workbook on Open and hide all sheets except 1 at the end.

[BODYCOTE]

Hello I want to do the following

When opening Workbook (a)>
open workbook (b)>
copy 'table1' from workbook (b)
paste 'table1' into workbook (a) table '1'
close workbook (b)
hide all worksheets on workbook (a) except 'HOME' worksheet

i have the following code returning runtime error '1004';
Application-defined or object-defined error

Private Sub Workbook_Open()
'
' OpenWorkbook Macro

    Application.ScreenUpdating = False
    Sheets("STOCKLIST").Select
    ActiveSheet.Unprotect Password:="lavish123"

  Workbooks.Open "C:\Users\posuser\Dropbox\Synagogue Management\Bars & License\Cost Sheets\0. Master- Cost Sheet.xlsm"
  
  Workbooks("0. Master- Cost Sheet.xlsm").Worksheets("STOCKLIST").Range("Table1").Copy
  
  ThisWorkbook.Worksheets("STOCKLIST").Range("Table1").PasteSpecial Paste:=xlPasteAll
  
  Workbooks("0. Master- Cost Sheet.xlsm").Worksheets("SUPPLIERS").Range("Table6").Copy

  ThisWorkbook.Worksheets("SUPPLIERS").Range("Table6").PasteSpecial Paste:=xlPasteAll
  
  Workbooks("0. Master- Cost Sheet.xlsm").Close SaveChanges:=False
  
  Worksheets("HOME").Activate

Dim sh As Worksheet
For Each sh In Worksheets
If sh.Name <> "HOME" Then sh.Visible = xlSheetHidden
Next
End Sub

Thanks for any help

 

[ranman256]

Public Sub HideAll()
Dim ws As Worksheet

On Error Resume Next
Sheets("Home").Activate
For Each ws In Sheets
  If ws.Name = "HOME" Then
     ws.Visible = xlSheetVisible
  Else
     ws.Visible = xlSheetHidden
  End If
Next
Set ws = Nothing
End Sub

 

[BODYCOTE]

Thanks for the reply, how do i incorporate the rest of the vba to open and copy for the other workbook

 

[dmt32]

Hi,
not tested but see if this update to your code does what you want

Private Sub Workbook_Open()
    Dim wbA            As Workbook
    Dim sh             As Worksheet, wsHome As Worksheet
    Dim wsStockList    As Worksheet, wsSuppliers As Worksheet
    Dim FolderPath     As String, FileName As String
    '
    ' OpenWorkbook Macro
    
    FolderPath = "C:\Users\posuser\Dropbox\Synagogue Management\Bars & License\Cost Sheets\"
    FileName = "0. Master- Cost Sheet.xlsm"
    
    On Error GoTo myerror
    
    Set wsStockList = ThisWorkbook.Worksheets("STOCKLIST")
    Set wsSuppliers = ThisWorkbook.Worksheets("SUPPLIERS")
    Set wsHome = ThisWorkbook.Worksheets("HOME")
    
    wsHome.Visible = xlSheetVisible
    
    wsStockList.Unprotect Password:="lavish123"
    
    Application.ScreenUpdating = False
    
    
    Set wbA = Workbooks.Open(FolderPath & FileName, False, True)
    
    wbA.Worksheets("STOCKLIST").Range("Table1").Copy wsStockList.Range("Table1").Cells(1, 1)
    
    wbA.Worksheets("SUPPLIERS").Range("Table6").Copy wsSuppliers.Range("Table6").Cells(1, 1)
    
    For Each sh In ThisWorkbook.Worksheets
        sh.Visible = sh.Name = wsHome.Name
    Next
    
myerror:
    If Not wbA Is Nothing Then wbA.Close False
    wsStockList.Protect Password:="lavish123"
    wsHome.Activate
     
    Application.ScreenUpdating = True
        
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
         
     
End Sub

Dave