rickincanada
Board Regular
- Joined
- Aug 31, 2010
- Messages
- 61
Hello,
I'm looking to create a macro that will prompt the user to select the workbook that their "settings data" is in and then proceed to copy and paste some of the data from that workbook to the new one.
So far I've got this in place:
Sub GetOptions()
Dim vFile As Variant
'Showing Excel Open Dialog Form
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)
'If Cancel then exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
End If
'Open selected file
Workbooks.Open vFile
End Sub
I'm challenged in that I'm not certain of the location of the file on their computer or if they've possibly renamed it since its creation. Therefore I'm hoping to prompt them to open the file and then once it's open use it's name through the rest of my code.
An example of this working might look like this:
NewWorkbook.xls is open and there is a button called "Get Settings", when the user clicks this button they are prompted for the location and name of "OldWorkbook.xls".
Once they've selected it then I'd like the following to take place:
Windows("OldWorkbook.xlsm").Activate
Sheets("Options").Select
Range("H3:H45").Select
Selection.Copy
Windows("NewWorkbook.xlsm").Activate
Sheets("Options").Select
Range("H3").Select
ActiveSheet.Paste
Thank you for taking a look and I greatly appreciate any help that can be provided.
I'm looking to create a macro that will prompt the user to select the workbook that their "settings data" is in and then proceed to copy and paste some of the data from that workbook to the new one.
So far I've got this in place:
Sub GetOptions()
Dim vFile As Variant
'Showing Excel Open Dialog Form
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)
'If Cancel then exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
End If
'Open selected file
Workbooks.Open vFile
End Sub
I'm challenged in that I'm not certain of the location of the file on their computer or if they've possibly renamed it since its creation. Therefore I'm hoping to prompt them to open the file and then once it's open use it's name through the rest of my code.
An example of this working might look like this:
NewWorkbook.xls is open and there is a button called "Get Settings", when the user clicks this button they are prompted for the location and name of "OldWorkbook.xls".
Once they've selected it then I'd like the following to take place:
Windows("OldWorkbook.xlsm").Activate
Sheets("Options").Select
Range("H3:H45").Select
Selection.Copy
Windows("NewWorkbook.xlsm").Activate
Sheets("Options").Select
Range("H3").Select
ActiveSheet.Paste
Thank you for taking a look and I greatly appreciate any help that can be provided.
Last edited: