PivotIdiot
Board Regular
- Joined
- Jul 8, 2010
- Messages
- 76
- Office Version
- 365
- Platform
- 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
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