Solved Copy and paste using a cell value to determine location

December 21, 2018 at 00:45:35
Specs: Windows 64
Hi guys,
Not sure what I,m doing wrong, must be tired

Sub tester()
 'Determine Row Number and COPY Data
   Rownum = Sheets("REMMEMB").Range("K17")
    
'     Sheets("LEGEND").Range("AP" & Rownum & ":AR" & Rownum & ":AT" & Rownum).Select
     Sheets("LEGEND").Range("AP" & Rownum).Select
        Selection.Copy
         Sheets("REMMEMB").Range("R49").Select
           Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
End Sub

Can you see where this is going wrong, Runtime error1004
Look forward to some direction on this

Tony


See More: Copy and paste using a cell value to determine location

Report •

#1
December 21, 2018 at 11:14:46
✔ Best Answer
Lose the Select/Selection instructions

Beside being inefficient (why make Excel do the work of Selecting cells when VBA can perform the operations directly?) you can't Select a cell with VBA unless the cell is on the ActiveSheet.

Your options are to Activate each sheet before Selecting the cells - adding even more inefficiency to your code - or to perform the operations directly.

Sub tester()
 'Determine Row Number and COPY Data
   Rownum = Sheets("REMMEMB").Range("K17")   
      Sheets("LEGEND").Range("AP" & Rownum).Copy
      Sheets("REMMEMB").Range("R49").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
December 21, 2018 at 22:57:42
DD03,
Thanks for the heads up, still learning, and shouldn't try doing stuff when I'm so tired
Thanks for all your help through the year
Have a great Christmas to you and your all

Tony


Report •
Related Solutions


Ask Question