VBA question

CFA General
#1

I am writing a copy and paste macro which pastes as values using the following: Range(“TargetPaste”).PasteSpecial Paste:=xlPasteValues This does the trick nicely except that now I wish the range to be dynamic based on an input in Excel. So, I would like it to paste to the Excel equivalent of INDEX(TargetPasteRange,SelectedNumber). How do I write this in VBA, is it simple, or do I have to actually write some proper code? Any help would be much appreciated.

#2

try and see if you can combine a function and macro together to make it dynamic. so you can call on the function inside the macro

#3

Thanks for your help. It sounds a bit too complicated for me - my VBA skills are not good.I’ll try to think up an Excel solution instead, I’m better at that.

#4

Why must paste value? If you need to, another way of doing it is: First assign the value: 1). Range(“TargetPaste”).value = Index(blah, blah) Then hardcode: 2). Range(“TargetPaste”).PasteSpecial Paste:=xlPasteValues

#5

Why must paste value? If you need to, another way of doing it is: First assign the value: 1). Range(“TargetPaste”).value = Index(blah, blah) Then hardcode: 2). Range(“TargetPaste”).PasteSpecial Paste:=xlPasteValues Hope this helps.

#6

Why must paste value? If you need to, another way of doing it is: First assign the value: 1). Range(“TargetPaste”).value = Index(blah, blah) Then hardcode: 2). Range(“TargetPaste”).PasteSpecial Paste:=xlPasteValues Hope this helps.

#7

Oh, its simple Why must paste value? If you need to, another way of doing it is: First assign the value: 1). Range(“TargetPaste”).value = Index(blah, blah) Then hardcode: 2). Range(“TargetPaste”).PasteSpecial Paste:=xlPasteValues Hope this helps.

#8

Thanks. Not sure why there are 4 posts the same by different users. I need to paste as values to break a circularity. It seems that I can use Cells( , ) to reference a cell… just need to work out how to get that information from a namerange now… sure it must be somthing like namerange.column but that doesn’t seem to work.

#9

To answer my own question… Range(“TargetPasteRange”).Columns(Range(“SelectedNumber”).Value).PasteSpecial Paste:=xlPasteValues …I really need to get better at this!