0

I'm very new to VBA. My very simple code is copying data in ranges from sheet 'Source' to sheet 'Destination', sheet 'Destination' is printed later and I really would like to avoid blank lines. The number of rows filled with data in the range varies. This code copies the complete range including any blank records at end of the range. Ideally once the last record is copied and pasted from a range then copying and pasting starts from the new range. Sitting scratching my head a while on this. Appreciate any usefull tips here. 

Sub Copy()

Sheets("Source").Range("F1:G20").Value = Sheets("Destination").Range("A1:B20").Value
Sheets("Source").Range("A20:B40").Value = Sheets("Destination").Range("A21:B41").Value
Sheets("Source").Range("N1:M20").Value = Sheets("Destination").Range("E42:F42").Value

End Sub
Deey2112
  • 1
  • There are a couple ways that immediately come to mind... `loop` through the records, and where the row/cell is not empty/blank you copy/paste. The other thought would be to `filter`/`sort` your source ranges so you only have blocks of data. At the end of the day you will need to find the [last row](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row), so you can apend to the last row in the destination sheet. – Cyril Jan 20 '20 at 14:59

1 Answers1

0

Fills in Destination with Matching Blank Cells

There's a couple ways you can do this. As @Cyril pointed out, you can either loop or filter your ranges. A loop allows you to specify how you want to treat specific cases (i.e. the cell is blank), whereas a filter (through Excel or VBA) lets you specify which cells you want prior to copying them over.

A loop in which you do not copy blank cells would look something like this:



Dim curRange as Range 'Source values
Dim destRange as Range 'Target location

Set curRange = Sheets("Source").Range("F1:G20")
Set destRange = Sheets("Destination").Range("A1:B20")

For i = 1 to curRange.Cells.Count
    If Not curRange.Cells(i).Value = vbNullString Then
        destRange.Cells(i).Value = curRange.Cells(i).Value
    End If
Next i

The loop can be nested as appropriate if you are iterating over a large or unknown number of ranges.

If you have a default value (as opposed to vbNullString), simply replace vbNullString with the default value.

Fills in Destination in Continuous Manner, Jumping Over Blank Cells

After re-reading, I think you may have meant that you are trying to have no blank cells in your destination sheet, which means that you want to continue pasting from the previous cell.

Adjust the loop as so:

Dim curRange As Range 'Source values
Dim destRange As Range 'Target location
Dim i As Long

Set curRange = Sheets("Source").Range("F1:G20")
Set destRange = Sheets("Destination").Range("A1:B20")
i = 1

For Each cell In destRange.Cells
    Do Until i > curRange.Cells.Count
        If Not curRange.Cells(i).Value = vbNullString Then
            cell.Value = curRange.Cells(i).Value
            i = i + 1
            Exit Do
        Else
            i = i + 1
        End If
    Loop
Next cell
jclasley
  • 668
  • 5
  • 14
  • Thanks a lot, this really helps. Have several source ranges with the same destination, pasting continuously the values into the destination list. Guess that a nested loop will sort that out. – Deey2112 Jan 21 '20 at 07:57
  • Tried nested loops however it just over writes the previously pasted rows from the first source range. I trying to copy from and additional Source range and continue pasting from the last row+1 into the common destination sheet. Any tips appreciated. – Deey2112 Jan 21 '20 at 12:39