• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 89
  • Last Modified:

VBA "Copy & Paste Special vs. VBA "Copy"

Experts:

The below VBA copies data from one sheet (I6:I15) into another worksheet.    Values from I6:I7 are currently formulae.   Data for I8:I15 are actual values.

Instead of copying of the formulae for I6:I7, I need the actual output values to be copied over.   I am hoping to change the VBA from "copy" to "copy & paste special".

How does the VBA need to be changed to accommodate a paste special (vs. simple copy process)?

ON WORKSHEET:

Private Sub RunUpdateOnFirst_Click()

	Range("I6:I15").Copy Destination:=Sheets("Tracking of Bills").Range(getNextHalf())
	
End Sub


***********************************************************************************


IN MODULE:

Public Function getNextHalf(Optional ByVal first As Boolean = True)
    Dim i, j
    i = 2 'start at col B
    j = 1 'start at row 2
    If (Not first) Then
        j = 11 'row 11 for second half
    End If
    While (Sheet6.Cells(j, i).Value <> "")
        i = i + 1
    Wend
    
    getNextHalf = Sheet6.Cells(j, i).Address
End Function

Open in new window

0
ExpExchHelp
Asked:
ExpExchHelp
  • 2
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Replace the following line...
Range("I6:I15").Copy Destination:=Sheets("Tracking of Bills").Range(getNextHalf())

Open in new window

WITH
Range("I6:I15").Copy
Sheets("Tracking of Bills").Range(getNextHalf()).PasteSpecial xlPasteValues

Open in new window

0
 
ExpExchHelpAuthor Commented:
Awesome... works perfectly!!!   Thank you for the prompt response.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad it worked as desired.
0
 
Rob HensonFinance AnalystCommented:
You don't need to do the copy, you can just force the values across:

Sheets("Tracking of Bills").Range(GetNextHalf()) = Sheets("Sheet1").Range("I6:I15").Value

Open in new window

Replace Sheet1 with source sheet name.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now