I have recorded the simple macro below but need to change the "F2" cell
address to be a user defined cell preferrably by 'clicking' on cell as I have
had no luck with coding the inputbox or goto...

Basically the macro unhides personal.xls, copies a cell (with 1 in it),
rehides the workbook and then should go to a starting cell, highlight range
and paste values..

Any assistance is greatly appreciated, thanks in advance.

Sub PasteMultipliedValue()
Application.Run "personal.xls!UnhidePersonal"
Range("E1").Select
Selection.Copy
Application.Run "personal.xls!HidePersonal"
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=False
End Sub

Replace:
Range("F2").Select
with:
Application.InputBox(Prompt:="Pick a cell", Type:=8).Select

This is nice because the user can either type an address in the input box or
use the mouse to CLICK on a cell.
--
Gary''s Student - gsnu200855


"jo2109" wrote:

I have recorded the simple macro below but need to change the "F2" cell
address to be a user defined cell preferrably by 'clicking' on cell as I have
had no luck with coding the inputbox or goto...

Basically the macro unhides personal.xls, copies a cell (with 1 in it),
rehides the workbook and then should go to a starting cell, highlight range
and paste values..

Any assistance is greatly appreciated, thanks in advance.

Sub PasteMultipliedValue()
Application.Run "personal.xls!UnhidePersonal"
Range("E1").Select
Selection.Copy
Application.Run "personal.xls!HidePersonal"
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=False
End Sub

Hi,

Here's one way:

Sub PasteMultipliedValue()
rg = Application.InputBox("Enter a cell address:", Type:=8).Address
Workbooks("Quick Parts.xlsm").Sheets("Dashboard").Range("J1").Copy
Range(rg, Range(rg).End(xlDown).End(xlToLeft)).PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlMultiply
End Sub

Notes:
You don't need to unhide Personal and rehide it, just leave it hidden.
You don't need to select a cell to copy it, nor do you need to select it to
paste into a cell or range.
Setting Skip Blanks to true has no effect since you are copying a single cell.
Since you are not Transposing you don't neet that argument.
It is quite unusual to have the user place the cursor at the top right
corner of a range, is this in error? If so change the xlToLeft to read
xlToRight.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"jo2109" wrote:

I have recorded the simple macro below but need to change the "F2" cell
address to be a user defined cell preferrably by 'clicking' on cell as I have
had no luck with coding the inputbox or goto...

Basically the macro unhides personal.xls, copies a cell (with 1 in it),
rehides the workbook and then should go to a starting cell, highlight range
and paste values..

Any assistance is greatly appreciated, thanks in advance.

Sub PasteMultipliedValue()
Application.Run "personal.xls!UnhidePersonal"
Range("E1").Select
Selection.Copy
Application.Run "personal.xls!HidePersonal"
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=False
End Sub

Thanks for trying guys, but neither of your solutions actually have the
desired result..

Gary"s student, your replacement line has the same end as my previous
attempts with the input box, the remainder of the macro does not complete.

Shane, whilst your macro completes ( I altered the directives to my file,
tab & cell reference) it does not covert the text cells to numbers.
p.s. I have found it a better solution to work from top right instead of
left as I include formula columns to the right of pasted data and need to
ensure that these are not valued in the process.

My workaround solution, although not ideal, is to simply sit on the starting
cell and run the unhide & hide personal macros then continue and it works ok.

Again, thanks for trying, it is obviously not as easy to code as I would
have thought.

"jo2109" wrote:

I have recorded the simple macro below but need to change the "F2" cell
address to be a user defined cell preferrably by 'clicking' on cell as I have
had no luck with coding the inputbox or goto...

Basically the macro unhides personal.xls, copies a cell (with 1 in it),
rehides the workbook and then should go to a starting cell, highlight range
and paste values..

Any assistance is greatly appreciated, thanks in advance.

Sub PasteMultipliedValue()
Application.Run "personal.xls!UnhidePersonal"
Range("E1").Select
Selection.Copy
Application.Run "personal.xls!HidePersonal"
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=False
End Sub