Pasting Values only with an Offset

[miricleman]

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

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

 

[My Aswer Is This]

I have never seen this script line work:
s1.Range("Ah5").Copy = s2.Range("f2").Offset(a, 0)

 

[My Aswer Is This]

I would think the script should look like this:

s1.Range("AI5").Copy Destination:=s2.Range("g2").Offset(a, 0)

 

[miricleman]

Hi My Answer Is This

Sorry the Script I posted should not have the

=

sign in it.

I have tried your code but it and is does not work for me, it gives me

=SUM(#REF!)

when running the code.

Do you have any other thoughts?

Thanks

 

[MARK858]

@miricleman, If you are saying that the code you posted without the = sign i.e.

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

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.

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

 

[My Aswer Is This]

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.

 

[miricleman]

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.

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.

 

[miricleman]

Thank you the code that Mark has supplied has worked for me.

Thanks for your time.

 

[My Aswer Is This]

..

 

[My Aswer Is This]

I like this way better:

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