Pasting Values only with an Offset

miricleman

New Member
Joined
Nov 6, 2013
Messages
36
Hi

I am trying to create some code that will copy and paste the values only. The code I have below will copy and paste the formula in the origin cell. all I need is the value. Can anyone Help??

Thanks


Code:
Sub UploadInfo()

 a = Sheets("Talley Sheet").Range("aindex")
 Dim s1 As Worksheet, s2 As Worksheet
    
    Set s1 = Sheets("Posting Sheet")
    Set s2 = Sheets("Talley Sheet")
    
If Sheets("Posting Sheet").Range("AH5").Value = "" Then
GoTo 2
Else:
s1.Range("n5").Copy = s2.Range("d2").Offset(a, 0)
s1.Range("o5").Copy = s2.Range("e2").Offset(a, 0)
s1.Range("Ah5").Copy = s2.Range("f2").Offset(a, 0)
s1.Range("AI5").Copy = s2.Range("g2").Offset(a, 0)

       
End If

MsgBox "information Posted"


2

End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I have never seen this script line work:
s1.Range("Ah5").Copy = s2.Range("f2").Offset(a, 0)
 
Upvote 0
I would think the script should look like this:
Code:
s1.Range("AI5").Copy Destination:=s2.Range("g2").Offset(a, 0)
 
Upvote 0
@miricleman, If you are saying that the code you posted without the = sign i.e.
Code:
s1.Range("AI5").Copy s2.Range("g2").Offset(a, 0)
gives you a different result to the code My Answer is This gave you with
Code:
s1.Range("AI5").Copy Destination:=s2.Range("g2").Offset(a, 0)
that can't be as they do exactly the same, it is just you can drop the "Destination:=" in the syntax.

Is your code giving you Ref errors currently?
If not can you copy and paste the actual code you are using in the thread.
What is the value of a when you are running the code?
What are the formulas you are copying?

To do a basic copy as values it would normally be as below but if you are getting Ref errors then copying as values is still going to give you #Ref.

Code:
Sub UploadInfo()

    a = Sheets("Talley Sheet").Range("aindex")
    Dim s1 As Worksheet, s2 As Worksheet

    Set s1 = Sheets("Posting Sheet")
    Set s2 = Sheets("Talley Sheet")
    
    Msgbox "The value of a is " & a 
    
    If Sheets("Posting Sheet").Range("AH5").Value = "" Then
        GoTo 2
    Else:
        s1.Range("n5").Copy
        s2.Range("d2").Offset(a, 0).PasteSpecial Paste:=xlPasteValues
        s1.Range("o5").Copy
        s2.Range("e2").Offset(a, 0).PasteSpecial Paste:=xlPasteValues
        s1.Range("Ah5").Copy
        s2.Range("f2").Offset(a, 0).PasteSpecial Paste:=xlPasteValues
        s1.Range("AI5").Copy
        s2.Range("g2").Offset(a, 0).PasteSpecial Paste:=xlPasteValues

    End If

    MsgBox "information Posted"

2

End Sub
 
Upvote 0
miricleman:
Some people write their own scripts and understand every part and others copy scripts from some place and do not understand exactly what they are doing. My line of script in post #3 was just a sample.
Please explain exactly what you are attempting to do. When you said in your original post "The code I have below will copy and paste the formula in the origin cell. all I need is the value" I assumed you were saying the script was working but was copying everything in the cell. Glad to help you if you would explain what you want to do. Thanks.
 
Upvote 0
Hi Mark 858

Yes your are correct in your summation above. With my original code I was getting the whole link which was a SUM(F:F) from the posting sheet. When I made the changes as suggested I was getting REF where the sum would be.

I wrote some code like yours before, I posted on the site. However I did not use a new line for the pasting function.
Code:
s1.Range("n5").Copy s2.Range("d2").Offset(a, 0).PasteSpecial Paste:=xlPasteValues

I Copied your code and it worked a treat! I have learned that for the copy and paste function need to be on different lines for it to work.

Thanks for you time and effort on this and educating me in in something new.
 
Upvote 0
I like this way better:
Code:
Sub UploadInfo()

    a = Sheets("Talley Sheet").Range("aindex")
    Dim s1 As Worksheet, s2 As Worksheet

    Set s1 = Sheets("Posting Sheet")
    Set s2 = Sheets("Talley Sheet")
    
    MsgBox "The value of a is " & a
    
        If Sheets("Posting Sheet").Range("AH5").Value = "" Then
        GoTo 2
        Else:
            s2.Range("D2").Offset(a, 0).Value = s1.Range("N5").Value
            s2.Range("E2").Offset(a, 0).Value = s1.Range("O5").Value
            s2.Range("F2").Offset(a, 0).Value = s1.Range("AH5").Value
            s2.Range("G2").Offset(a, 0).Value = s1.Range("A15").Value

        End If

    MsgBox "information Posted"

2

End Sub
 
Upvote 0

Forum statistics

Threads
1,217,676
Messages
6,137,934
Members
450,099
Latest member
Pushbutton

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top