CODEWITHSUNDEEP
excelvba
Dave Partridge
Dave Partridge

Reputation: 3

Copy-PasteSpecial fails on large ranges

Pastespecial to paste values from a column into the next column but having difficulties getting it to work.

If I use this code it works fine

Sub cps()
Range("F2:F500").Copy
Range("G2:G500").PasteSpecial Paste:=xlPasteValues
End Sub

Snag is it's an Excel sheet looking at SQL so the number of rows changes, today there are 2100 and tomorrow there might be 2110. So I tried the below to make sure I caught all the rows..

Sub cps()
Range("F2:F5000").Copy
Range("G2:G5000").PasteSpecial Paste:=xlPasteValues ' <~~~~ Error
End Sub

But I get an error on the line Range("G2:G5000").PasteSpecial Paste:=xlPasteValues

How do I do this if I don't know how many rows there are in the sheet?

So Thomas gave me a solution which works but I just need to adapt the code to look at 3 separate sheets, AP, EMEA and WH.

Upvotes: 0

Views: 336

Answers (1)

Thomas G
Thomas G

Reputation: 10216

Your code is working fine. Maybe it bugs because you are not running it from the sheetmodule and you didn't qualified your Range. For instance : Sheets("Sheet1").Range or ActiveSheet.Range like I do below. Also this code will only copy the used range like you asked.

Edit: Adding stuff to call it from any sheet

Create a new module and move this Sub in it :

Public Sub CopyCOlumnF(strSheet As String)

    Dim LastRow As Long

    With Sheets(strSheet)
        LastRow = .Cells(.Rows.Count, 6).End(xlUp).Row ' Column "F"=6
        .Range("F1:F" & LastRow).Copy
        .Range("G1:G" & LastRow).PasteSpecial Paste:=xlPasteValues
    End With

End Sub

Then in any of your sheetmodules, call the sub like this:

Sub cps()
    CopyCOlumnF (ActiveSheet.Name)
End Sub

You can also call it from any place and apply the trick to any sheet like this:

CopyCOlumnF ("Sheet1")
CopyCOlumnF ("Sheet2")

Upvotes: 1

Related Questions