heroofgoodwi
10-04-2017, 01:16 AM
Hey guys,
So I am a little bit stuck, I currently have a piece of code which I recently wrote which allows the user to pull data from a closed workbook and import it into the one they currently have open.
While I am very happy with how this works at the moment the end goal needs to be to have some kind of user input to allow the user to select a cell range to pull and the cell range which determines the final location for the data.
Currently these are set to $A$1:$Z$100 for both but I would like to be able to have the user select these ranges without having to go into vba and actually edit the code itself.
Does anyone have any suggestions about how to achieve this. Code displayed below.
Sub PulldatawithsafetyQuestion()
'Safety question to check if used has saved a back up
'This will exit the sub if the user selects NO
Q1 = MsgBox("Have you saved a back up copy of this worksheet?", vbQuestion + vbYesNo)
If Q1 = vbNo Then Exit Sub
Dim mydata As String
'data location & range to copy
mydata = "='C:\[FilePathto\:Cdrive]Sheet1'!$A$1:$Z$100" '<< change as required
'(LOCATION TO PULL FROM)
'link to worksheet
With ThisWorkbook.Worksheets(1).Range("$A$1:$Z$100") '<< change as required (LOCATION TO MOVE DATA TO)
.Formula = mydata
'convert formula to text
.Value = .Value
MsgBox "Your data has now been copied across"
End With
End Sub
So I am a little bit stuck, I currently have a piece of code which I recently wrote which allows the user to pull data from a closed workbook and import it into the one they currently have open.
While I am very happy with how this works at the moment the end goal needs to be to have some kind of user input to allow the user to select a cell range to pull and the cell range which determines the final location for the data.
Currently these are set to $A$1:$Z$100 for both but I would like to be able to have the user select these ranges without having to go into vba and actually edit the code itself.
Does anyone have any suggestions about how to achieve this. Code displayed below.
Sub PulldatawithsafetyQuestion()
'Safety question to check if used has saved a back up
'This will exit the sub if the user selects NO
Q1 = MsgBox("Have you saved a back up copy of this worksheet?", vbQuestion + vbYesNo)
If Q1 = vbNo Then Exit Sub
Dim mydata As String
'data location & range to copy
mydata = "='C:\[FilePathto\:Cdrive]Sheet1'!$A$1:$Z$100" '<< change as required
'(LOCATION TO PULL FROM)
'link to worksheet
With ThisWorkbook.Worksheets(1).Range("$A$1:$Z$100") '<< change as required (LOCATION TO MOVE DATA TO)
.Formula = mydata
'convert formula to text
.Value = .Value
MsgBox "Your data has now been copied across"
End With
End Sub