VBA script for copy value from another sheet at specific interval

Question

I need help with one of the script. I am new to vba scripting. I have one file with 2 worksheets. Sheet 1 has numbers in 9 columns. I have to insert one blank row after every 50 rows. For which I created the script. Now second part is tricky as all those blank rows has to be filled with data from Sheet 2 which also has a series of numbers dynamically entered. This data to be inserted in blank row that, 1st blank row of sheet1 with 1st row of Sheet2, 2nd blank row (51st row) with 2nd row from sheet2 and so on. The script I tried is here. It stuck at first blank row. and that too not selecting dynamically from sheet2. Please help me with the correct code. Thanks a lot.

    Sub Copydatafromothersheet()
Dim ws As Worksheet
Dim c As Range
Dim LastRow As Long, i As Long
Dim j As Integer
Dim sCol As Integer, rCount As Integer, cRow As Integer
Dim cRowValue As String
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To LastRow
    sCol = 1
    rCount = Cells(Rows.Count, sCol).End(xlUp).Row
    For currentRow = 1 To rCount
    cRowValue = Cells(currentRow, sCol).Value
    If IsEmpty(cRowValue) Or cRowValue = "" Then
    Cells(currentRow, sCol).Select
    End If
    Next
    Sheets("COVER").Range("A2:I2").Offset(1, 0).Copy
    Sheets("Sheet1").Cells(cRowValue, "A").End(xlUp).Offset(1). _
    PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Next i

End Sub

Unknown Unknown · Accepted Answer · 2020-11-29T22:12:11

It’s a bit difficult to fully understand exactly what it is you’re trying to do. On the one hand, you say the first blank row on Sheet1 is filled with the first row from Sheet2 – but then you say the next blank row on Sheet1 is row 51 2nd blank row (51st row) – which it wouldn’t be if you inserted a blank row "after every 50 rows" – because that would be row 101 not 51?

Anyhow, the following code will achieve both a blank row insertion every 50 rows on Sheet1 – and a copy of data from Sheet2 – until the last row divisible by 50 on Sheet1 is reached. Clarify if this isn’t what you intended and I’ll change the code to suit.

Option Explicit
Sub CopyOption1()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Long, j As Long, LastRow As Long

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("COVER")
LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

i = 51
j = 1

Do While i < LastRow
ws1.Rows(i).Insert shift:=xlDown
ws2.Rows(j).Copy
ws1.Rows(i).PasteSpecial xlPasteValues
Application.CutCopyMode = False
    i = i + 51
    j = j + 1
    LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Loop

End Sub

VBA script for copy value from another sheet at specific interval

1 Answers