VBA to browse/open workbook then select specific worksheet

PivotIdiot

Board Regular
Joined
Jul 8, 2010
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

I have a macro that currently runs within a workbook, looks for a named sheet and runs copy/paste functions on certain ranges within.
I'd like to prefix this macro with code that allows me to open another workbook and select a specific worksheet to run the copy/paste functions from there.

Ive started with this....


strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xlsx*),(*.xlsm*),")


If strFileToOpen = "False" Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
Workbooks.Open filename:=strFileToOpen
End If

Dim ws As Worksheet
Dim x As Integer

x = 1

Sheets.Add.Name = "TempSheet"
Sheets("TempSheet").Range("A:A").Clear

For Each ws In Worksheets
Sheets("TempSheet").Cells(x, 1) = ws.Name
x = x + 1
Next ws

I thought i could then work out how to select the required sheet name, store that as a string to use in the copy/paste code further on.

Is there a better way of getting this to be stored for further use?

Thanks in advance, PIV
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

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
Back
Top