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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
I have never seen this script line work:
s1.Range("Ah5").Copy = s2.Range("f2").Offset(a, 0)
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
I would think the script should look like this:
Code:
s1.Range("AI5").Copy Destination:=s2.Range("g2").Offset(a, 0)
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

@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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 6, 2013
Messages
36

ADVERTISEMENT

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.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,331
Messages
5,601,002
Members
414,419
Latest member
JRDunya

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
Top