I have encountered an error that is mildly problematic. Tho Goal of the code below is to allow the user to select the sheets (HR1 - HR20) they would like to modify, then select the cells (range of cells) on the original sheet that they want to copy to the selected sheets.
The problem is that the user has to select the range they want to change , then run the macro ( via button), select the sheets, then RESELCET THE RANGE. If they only have a single cell selected prior to the macro being run, the macro will copy the value of that single cell even after they have highlighted the range ,when prompted by the macro, they want changed. Is there some way to tell the macro only use the selected range?
Dim WSN As Worksheet
Set WSN = ActiveSheet
Dim sheetname As String
sheetname = ActiveSheet.Name
Unload Me
On Error GoTo Cancel
Dim rng As Range
Dim myString As String
Set rng = Application.InputBox("select cell range with changes", "Cells to be copied", Default:="Select Cell Range", Type:=8)
Application.ScreenUpdating = False
myString = Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)
'MsgBox Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)
Range(myString).Copy
If CheckBoxALL.Value = True Then
Sheets("Cost").Select
For i = 1 To 20
ActiveSheet.Next.Select
rngS = rng.Address
Range(rngS).Select
ActiveSheet.Paste
ActiveWindow.ScrollRow = 85
Next i
End If
If CheckBoxHR1.Value = True Then
Sheets("Cost").Next.Select
rngS = rng.Address
Range(rngS).Select
ActiveSheet.Paste
ActiveWindow.ScrollRow = 85
End If
Answer
didn't test it but i think that you could use 'rng' variable directly
I mean
instead of
Set rng = Application.InputBox("select cell range with changes", "Cells to be copied", Default:="Select Cell Range", Type:=8)
Application.ScreenUpdating = False
myString = Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)
'MsgBox Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)
Range(myString).Copy
you could go this way
Set rng = Application.InputBox("select cell range with changes", "Cells to be copied", Default:="Select Cell Range", Type:=8)
Application.ScreenUpdating = False
rng.Copy
No comments:
Post a Comment