Copy and paste values by range, not by cell

Question

I am trying to get the below piece of code to paste the values I want as values, but to do it by block range, not cell by cell.

I've tried the below code and a few other examples, but this one gets as close to what I want.

Sub main()

Dim LastRow As Long
Dim i As Integer
For i = 6 To 30
    If IsEmpty(Sheets("Main").Cells(i, 6).Value) = False Then

    Sheets("SSCC_Bin").UsedRange 'refreshes sheet2
    LastRow = Sheets("SSCC_Bin").UsedRange.Rows(Sheets("SSCC_Bin").UsedRange.Rows.Count).Row 'find the number of used rows

    Sheets("Main").Cells(i, 5).Offset(0, -2).Copy
    Sheets("SSCC_Bin").Range("A1").Offset(LastRow, 0).PasteSpecial xlPasteValues
    'copies and pastes the data

Else
End If
Next i

End Sub

I have formula's in column C of sheet 'Main' which are order numbers. I want them moved in one motion to SSCC_Bin. The code above does it cell by cell which plays with my formula's.

`Sheets("Main").Cells(i, 5).Offset(0, -2).Copy` You are copying online 1 single cell. You need to modify this part to copy the range you want to. — Foxfire And Burns And Burns, Jul 23 '19 at 10:16
Try avoiding `UsedRange`. Please check out this answer: https://stackoverflow.com/a/50434743/6908282 — Gangula, Jul 23 '19 at 11:08

Tom · Accepted Answer · 2019-07-23T11:10:30.317

0

You could try using something like the following. This will 'copy' the values in your Main sheet in blocks based on where the empty cells are

Sub main()
    Dim LastRow As Long, DestRow As Long
    Dim i As Long
    With Sheets("SSCC_Bin")
        DestRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

    With Sheets("Main")
        LastRow = .Cells(.Rows.Count, 6).End(xlUp).Row
        For i = 6 To LastRow
            ' Skips over empty cells
            If Not IsEmpty(.Cells(i, 6)) Then
                ' Gets CurrentRegion - basically all surrounding cells that are populated
                With Intersect(.Cells(i, 6).CurrentRegion, .Columns(6))
                    ' Same as PasteSpecial xlPasteValues but faster           
                    Sheets("SSCC_Bin").Cells(DestRow, 1).Resize(.Rows.Count, .Columns.Count).Value2 = .Value2
                    ' Updates where the next data set is to be added too
                    DestRow = DestRow + .Rows.Count
                    ' Increase i for the rows that we have already handled
                    i = i + .Rows.Count
                End With
            End If
        Next i
    End With
End Sub

edited Jul 23 '19 at 11:10

answered Jul 23 '19 at 10:51

Tom

9,725
3
31
48

Hi Tom, thanks for your time. I only want the values in column C, the CurrentRegion i tried, but also brings every other column with it? – Ceebee86 Jul 23 '19 at 11:06
@Ceebee86 Have updated - can you try again with my edit? – Tom Jul 23 '19 at 11:10
Thats great, modified it a little to go for last row after. :) – Ceebee86 Jul 23 '19 at 11:54
Just add one to your `LastRow` variable or is that not what you mean? i.e. `LastRow = .Cells(.Rows.Count, 6).End(xlUp).Row + 1` – Tom Jul 23 '19 at 11:55
1

to get the scenario I wanted, I added the +1 after DestRow -----------------Sheets("SSCC_Bin").Cells(DestRow + 1, 1).Resize(.Rows.Count, .Columns.Count).Value2 = .Value2 – Ceebee86 Jul 23 '19 at 11:58

Gangula · Answer 2 · 2019-07-23T11:04:00.163

0

The following code should probably work for you. I have not tested this yet.

Instead of looping through each cell and checking if its empty, you can use SpecialCells function to find constants.

Sub Macro1()

Dim LastRow As Long    

    Sheets("SSCC_Bin").UsedRange 'refreshes sheet2
    LastRow = Sheets("SSCC_Bin").UsedRange.Rows(Sheets("SSCC_Bin").UsedRange.Rows.Count).Row 'find the number of used rows

    Sheets("Main").Range("F6:F30").SpecialCells(xlCellTypeConstants, 23).Copy
    Sheets("SSCC_Bin").Range("A1").Offset(LastRow, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Note: This code does not work if your range has formula's

edited Jul 23 '19 at 11:04

answered Jul 23 '19 at 10:51

Gangula

5,193
4
30
59

You might benefit from reading this [How to avoid using Select in Excel VBA](https://stackoverflow.com/a/10717999/3042759) – Tom Jul 23 '19 at 10:54
Thank you @Tom. I've updated the code to not include Select – Gangula Jul 23 '19 at 10:59
Hi Gangula, I get run time error 1004 when trying to run it? :( – Ceebee86 Jul 23 '19 at 11:04
I was not able to figure out where exactly you want it to be pasted. The `UsedRange` function is not preferable. Can you let me know where exactly you want it pasted? Please add it within the question. – Gangula Jul 23 '19 at 11:08

score 0 · Answer 3 · answered Jul 23 '19 at 13:54

I know you have another answer, here is a basic solution with comments.

With ThisWorkbook.Sheets("Main")
    'Hide the rows in column 6 if the cell is empty
    .Columns(6).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

    'Copy only the visible cells in the range
    .Range("C6:C30").SpecialCells(xlCellTypeVisible).Copy

    'paste the range to the next cell after the last used cell in column A
    Sheets("SSCC_Bin").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

End With

'Clear the marching ants
Application.CutCopyMode = False

Copy and paste values by range, not by cell

3 Answers3