I have the following macro. Before I run it each time I have to edit the reference to "030" below (4th line from bottom). For example, I will edit it to "031" then run it. I will then edit it to "032" then run it again...etc

It is because I dont know how to make that particular line in the macro be referable to a cell which will change each time I want to run the Macro. I was hoping to position my cursor into the desired target cell (be it 030 or 031 or 032 or 033 etc etc) prior to running the macro, and then for Macro to paste the stuff I have copied from a different source (an extract from a PDF file for instance)

Any help appreciated. Thanks


Sub MacroGM4()
'
' MacroGM4 Macro
'

'
Range("O1:O11").Select
Range("O11").Activate
Selection.ClearContents
Range("O1").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Range("O12").Select
Selection.Copy
Range("O30").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

Hi,

Am Thu, 7 Jan 2016 07:08:35 -0800 (PST) schrieb :

Range("O1:O11").Select
Range("O11").Activate
Selection.ClearContents
Range("O1").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Range("O12").Select
Selection.Copy
Range("O30").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

why do you paste O12 in multiple cells from O30 downwards?

Try for that part of the code:

Range("O12").Copy
If Len(Range("O30")) = 0 Then
Set Dest = Range("O30")
Else
Set Dest = Cells(Rows.Count, "O").End(xlUp)(2)
End If

Dest.PasteSpecial Paste:=xlPasteValues


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional