Excel VBA finding / recording user selection

Question

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

the steps should go make changes to sheet1, press macro button, select sheets via userform, select sheets, click apply then select area that need to be copied. MY issue was that if I just left a random cell selected prior to the macro button press, the macro would grab the data from the random cell not the range highlighted by the user. — Alberto Brown

user3598756 user3598756 · Accepted Answer · 2016-02-26T15:41:24

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

Excel VBA finding / recording user selection

1 Answers