PasteSpecial with variable

Wondering if this is possible and is a beginners question so fogive me if it sounds stupid
Part of macro looks like this

Please Login or Register  to view this content.

and I then go on to pastespecial dividing by 20.
Is it possible to assign 20 to a variable instead and then use that variable in the pastespecial code?

I do not know what are you trying to do, but let me guess.

You assigned the value of 20 in to A1 and copied A1, but that is. Excel will copy and you will see the copy sign and will not disappear, even you paste it somewhere. You have to tell VBA where to paste the copied range. You can only paste in to a cell and that cell needs to have an address. Yes, you can represent the address with a variable, and this will point VBA to paste it into that address.

macro put a value of 20 in cell a1.
Macro then copied this and used paste special command thus:

Sheets(strSheetName).Range("M25", Range("O65536").End(xlUp)).PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks _
:=False, Transpose:=False

I was asking if instead of putting 20 in a cell and copying it i could do something like
x = 20
with x....do the pastespecial

OK...thanks.
Thought as much but wanted to be clear

Let me be clear.

Is this what you are trying to do

Range("a1").value = 20


Range("a2").PasteSpecial xlvalues=Range("a1")


If it is, it is a syntax error- the same as putting the copy value and paste in a single line is syntax error.

Range("a1") could be a variable, but it is still a syntax error
Range("a1").Copy Range("a2").PasteSpecial xlvalues ' error
?

no
It's all in post 3

Okay! shg gives you another angle, but still xlPasteSpecialOperationDivide can not be assigned to a variable.

Originally Posted by shg

Do you mean this?

Please Login or Register  to view this content.

would that take x and use it in pastespecial?
If so then it is what I was looking for but I see it won't make my code shorter

Originally Posted by AB33

Let' say, you have

Range("a1").Copy
Range("M25", Range("O65536").End(xlUp)).PasteSpecial Paste:=xlPasteAll

Range("M25", Range("O65536") is the address of the cell where the copied value will be pasted.

PasteSpecial is a property of a range, so I do not think you can change it (Assign a variable to it).
You can only change the cell (Range) address.
I hope I understand your drift.

You explained it well. It can't really be done in a way that would reduce the amount of code

It may be, but remember short code does not mean efficient, nor it is better. I do not think it will make any difference in performance since we are dealing with 2-3 more lines.