Using macro in Book 1 to open 2 other workbooks within the same folder as book 1 and position on screen

dori2o

Board Regular
Joined
Apr 7, 2016
Messages
72
Book 1 is a blank worksheet with a large button in the middle entitled OPEN

The macro for this button is here

Sub Macro1()
'
' Macro1 Macro
'
'
Workbooks.Open Filename:= _
"C:\Users\Username\Desktop\Media\Workbook 1.xlsm"
Application.Left = 1150
Workbooks.Open Filename:= _
"C:\Users\Username\Desktop\Media\Workbook 2.xlsm"
Application.Left = 1150
Windows("Book1.xlsm").Activate
Windows.Arrange ArrangeStyle:=xlVertical
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

I've put the macro together by following advice on here and recording my own macro's and trying to piece them together, now I'm stuck.

Book1, Workbook 1 and Workbook 2 will be held together in the same folder. If possible I want workbook 1 and workbook 2 to be hidden files. With this is mind, and as the 2 sheets wil be held in the same folder as Book 1, is it possible to then remove the full location from the macro and just link to them based on Book 1 being in the same location (within a folder called Media Tools)?

I want to be able to open Workbook 1 and 2 together and split them on the screen. Ideally with Workbook 1 taking up 1/3 of the screen and Workbook 2 2/3 of the screen, but if I can only do 50:50 I'm happy to just accept that. I want Book 1 to close as it is merely a tool to open the other 2 spreadsheets.

The problem I am having is that when it arranges the spreadsheets it's including Book 1 in the vertical split as that instruction is there before the instruction to close.

I am also using a dual screen set up, I want the workbooks to open on the 2nd screen, the line 'Application.Left = 1150' opens the workbooks at the furthest point left of the second screen.

Any help would be most appreciated. I've probably gone about this completely the wrong way but I'm still trying to learn VBA etc.

Thanks in advance
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,193
Messages
5,768,765
Members
425,492
Latest member
blueexcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top