Getting mouse selected cell as input to start operations on data of that cell row

Hello reach78,

Clicking a cell makes it the ActiveCell. You can use the Row property to obtain the row of cell.

    Dim row As Long

        row = ActiveCell.Row

Use Application.inputbox with the Type argument set to 8 to return a Range object.

It's just row.row (not a good name for a variable!) instead of Range(Row).Row

The code below now works:

Sub GenerateReport()

Dim NewMonth As String
Dim Point As Range

       


'NewMonth = InputBox("Enter Report Month to generate (Format: mmmmmmm yyyy e.g. JANUARY 2013")
'Sheets("Report").Activate
'ActiveSheet.Range("A5").Value = NewMonth
Set Point = Application.InputBox(prompt:="Select Supplier", Type:=8)
Sheets("Data").Range(Cells(Point.row, 2), Cells(Point.row, 7)).Copy

ActiveSheet.Range("B49").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False



End Sub

However when I replace:

Sheets("Data").Range(Cells(Point.row, 2), Cells(Point.row, 7)).Copy

ActiveSheet.Range("B49").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

To call a specific cell and paste it to a specific location in another sheet. It breaks down. The code I replaced it with is

Sheets("Data").Range(Cells(Point.row, 5), Cells(Point.row, 5)).Copy
Sheets("Report").Range("H8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

The debugger hangs at the Select.PasteSpecial... line. Code somewhere above it wrong I guess?

You're still using Set row =

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.