Expanding (VBA) Open/Copy/Close

[ryansm05]

Hi,

With help of the forum and a little research myself, I've come up with the following code that opens a 2nd workbook, copies the data from the specified tab ('Time') and then closes the 2nd workbook that I just opened.

This is great, but I have two issues that I need help adapting:

1) How would I edit this to allow me to copy data from a second tab in that workbook that was opened?
2) How do I make make this run automatically when the file is open?

Sub GetDataClosedBook()
Dim src As Workbook'location of the file and data to copy
Set src = Workbooks.Open("I:\Accounts\2018\Financial Reporting\BRD\NewRev\Data.xlsx", True, True)'bring to this workbook
ThisWorkbook.Activate
Worksheets("Time").Range("A1:X15000").Formula = src.Worksheets("Time").Range("A1:X15000").Formula
src.Close False
End Sub

Thanks in advance!
Ryan

 

[JackDanIce]

Hi Ryan,

See if following helps:

Sub MonkeyNuts()

    Dim wkb As Workbook: Set wkb = Workbooks.Open("I:\Accounts\2018\Financial Reporting\BRD\NewRev\Data.xlsx", True, True)
    Dim LR  As Long
    
    With wkb
        'Change the sheetname "Time" to name of second sheet. Or use Sheets(2), assuming the index number of the sheet relates to the second sheet shown in your spreadsheet
        LR = .Sheets("Time").Cells(.Sheets("Time").Rows.Count, 1).End(xlUp).Row
        ThisWorkbook.Sheets("Time").Range("A1:X1" & LR).Value = .Sheets("Time").Range("A1:XL" & LR).Value
        
        LR = .Sheets("Sheet2").Cells(.Sheets("Sheet2").Rows.Count, 1).End(xlUp).Row
        ThisWorkbook.Sheets("Sheet2").Range("A1:X1" & LR).Value = .Sheets("Sheet2").Range("A1:XL" & LR).Value
        .Close False
    End With
    
    Sheets("Time").Select


    Set wkb = Nothing
End With

 

[ryansm05]

Hi Ryan,

See if following helps:

Sub MonkeyNuts()

    Dim wkb As Workbook: Set wkb = Workbooks.Open("I:\Accounts\2018\Financial Reporting\BRD\NewRev\Data.xlsx", True, True)
    Dim LR  As Long
    
    With wkb
        'Change the sheetname "Time" to name of second sheet. Or use Sheets(2), assuming the index number of the sheet relates to the second sheet shown in your spreadsheet
        LR = .Sheets("Time").Cells(.Sheets("Time").Rows.Count, 1).End(xlUp).Row
        ThisWorkbook.Sheets("Time").Range("A1:X1" & LR).Value = .Sheets("Time").Range("A1:XL" & LR).Value
        
        LR = .Sheets("Sheet2").Cells(.Sheets("Sheet2").Rows.Count, 1).End(xlUp).Row
        ThisWorkbook.Sheets("Sheet2").Range("A1:X1" & LR).Value = .Sheets("Sheet2").Range("A1:XL" & LR).Value
        .Close False
    End With
    
    Sheets("Time").Select


    Set wkb = Nothing
End With

Hi Jack,

My knowledge of macros is very limited - so I copied and pasted your suggestion and changed all the "sheet2" reference to "jobs" - which correlates with the tab I wish to return.

However, when running the macro, it got stuck on the first line: Sub MonkeyNuts

 

[ryansm05]

In fact it looks like 'End With' was the issue - I've attached a screenshot.


2014 lexus is 250 0 60

 

[JackDanIce]

Sorry typo. That very last End With should change to End Sub

 

[ryansm05]

Sorry typo. That very last End With should change to End Sub

That now works - thanks so much!

The only adaption I need is to run this macro automatically once the file is opened. Is this possible?

 

[JackDanIce]

Have a look at the workbook_open event: https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.open

 

[ryansm05]

Sadly my level of idiocy is at a solid 10 right now.
I've added this code as below - but sadly the macro does not run when I open the document.
I tried to add it in a 'module', but this didn't work either.

Hopefully it's fairly obvious as to why it's not working?

 

[JackDanIce]

On the left side of the picture is the Workbook Object, all of the code needs to go into there as:

Private Sub Workbook_Open()
    Application.WindowState = xlMaximized
    Call MonkeyNuts
End Sub
Sub MonkeyNuts()
..
..
..
End Sub

 

[ryansm05]

Works great!
Cheers