VBA to copy and paste each list after skipping few blank rows

[youngbella]

Hi, I'm new to VBA.

I have some problem which I have some list of data and I need to transfer the list to other sheet. For each list, I need to skips 7 blanks cell for remark. I tried to copy and paste and do some looping but it didnt work.

The code does copy the data but it did not skips 7 blank rows when pasting. very grateful if you could help me.

Below is the list and the outcome that I need.

 

[ekrause]

Try this with your paste code. You will need to define what you are copying where I have named Selection. And you will need to define where to start the code either with a named range or a hardcoded value and replace all of the following - Range("PasteValueRange")

I also tried to account for any changes in your example...if you have 3, 4, 5 lines that you are trying to copy for instance/if you are starting a new workbook or appending an old book. Let me know if this suits your needs.

Selection.Copy

If Range("PasteValueRange") = "" Then

    Range("PasteValueRange").PasteSpecial xlPasteValues

Else

    If Range("PasteValueRange").Offset(Range("PasteValueRange").SpecialCells(xlCellTypeLastCell).Row - Range("PasteValueRange").Row).End(xlUp).Offset(-1) = "" Then
    
        Range("PasteValueRange").Offset(Range("PasteValueRange").SpecialCells(xlCellTypeLastCell).Row - Range("PasteValueRange").Row).End(xlUp).Offset(8).PasteSpecial xlPasteValues
    
    Else
    
        Range("PasteValueRange").Offset(Range("PasteValueRange").SpecialCells(xlCellTypeLastCell).Row - Range("PasteValueRange").Row).End(xlUp).End(xlUp).Offset(8).PasteSpecial xlPasteValues
    
    End If

End If

 

[youngbella]

Try this with your paste code. You will need to define what you are copying where I have named Selection. And you will need to define where to start the code either with a named range or a hardcoded value and replace all of the following - Range("PasteValueRange")

I also tried to account for any changes in your example...if you have 3, 4, 5 lines that you are trying to copy for instance/if you are starting a new workbook or appending an old book. Let me know if this suits your needs.

Selection.Copy

If Range("PasteValueRange") = "" Then

    Range("PasteValueRange").PasteSpecial xlPasteValues

Else

    If Range("PasteValueRange").Offset(Range("PasteValueRange").SpecialCells(xlCellTypeLastCell).Row - Range("PasteValueRange").Row).End(xlUp).Offset(-1) = "" Then
  
        Range("PasteValueRange").Offset(Range("PasteValueRange").SpecialCells(xlCellTypeLastCell).Row - Range("PasteValueRange").Row).End(xlUp).Offset(8).PasteSpecial xlPasteValues
  
    Else
  
        Range("PasteValueRange").Offset(Range("PasteValueRange").SpecialCells(xlCellTypeLastCell).Row - Range("PasteValueRange").Row).End(xlUp).End(xlUp).Offset(8).PasteSpecial xlPasteValues
  
    End If

End If

Dear Sir,

Thank your for your assistance. but however, when I try to rename the range. I get error "type mismatch". is my range wrong?


Below is my code :

Sub pastethat()

Dim countA As Integer
Dim wb As Worksheet

Worksheets("ImportData").Range("D14:D19").Copy

Set wb = ThisWorkbook.Worksheets("June")


If wb.Range("A:A") = "" Then

    wb.Range("A:A").PasteSpecial xlPasteValues

Else

    If wb.Range("A:A").Offset(wb.Range("A:A").SpecialCells(xlCellTypeLastCell).Row - wb.Range("A:A").Row).End(xlUp).Offset(-1) = "" Then
    
        wb.Range("A:A").Offset(wb.Range("A:A").SpecialCells(xlCellTypeLastCell).Row - wb.Range("A:A").Row).End(xlUp).Offset(8).PasteSpecial xlPasteValues
    
    Else
    
        wb.Range("A:A").Offset(wb.Range("A:A").SpecialCells(xlCellTypeLastCell).Row - wb.Range("A:A").Row).End(xlUp).End(xlUp).Offset(8).PasteSpecial xlPasteValues
    
    End If

End If


End Sub

 

[ekrause]

Instead of making the range "A:A" start with your starting cell, "A2" for instance.

 

[youngbella]

Instead of making the range "A:A" start with your starting cell, "A2" for instance.

Nope. It doesnt work. the code did paste the list but did not skip 7 rows for the second list. I think need to do looping but i dont know how to.

 

[youngbella]

dear sir @ekrause ,

I've made some coding regarding this paste problem. But it only copy and paste the last cells of the list. i've made it into for loop. need your help to correct me on what went wrong? imagine that I want to copy from column A Sheet 1 to column A sheet 2 but I want to skip 7 rows on the sheet 2 after each data.

Sub copypasteskip()

Dim name As String
Dim src As Variant
Dim dest As Variant
Dim endnumber As Integer
Dim finalrow As Variant
Dim i As Integer
Dim r As Integer


Set src = ThisWorkbook.Worksheets("sheet1")
Set dest = ThisWorkbook.Worksheets("Sheet2")

endnumber = Cells(Rows.Count, "A").End(xlUp).Row
finalrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

For r = 11 To endnumber
        
        
        For i = 14 To finalrow
        
        Worksheets("Sheet1").Cells(i, "A").Copy
        Worksheets("Sheet2").Cells(r, "A").PasteSpecial xlPasteAll
        
        
        Next i
        
        
        r = r + 7

Next r


End Sub