for loop with named cells

[padadof2]

I have a workbook that has two worksheets, "Bid Sheet" and "Proposal" I would like to loop through the "bid sheets" rows and paste them over to the proposal sheet. The bid sheet could have 1 row to paste or 20 rows, depending on the job. I have the cells named, item1, Desc1, Quan1, and Unit1, then item2, Desc2, Etc.... My boss will sometimes delete a row in the bid sheet as well, so I have to delete the empty rows as well. I was going to start by copying and pasting the cells down, so I could delete the empty rows before pasting. Not sure if that's the correct method.

I'm new to VBA and google has been my friend. I was trying to loop and change the named cells based on the loop, but again, I'm stuck. Forgive my messy code

Here is my workbook

Sub proposal()

    Dim i As Long
    Dim j As Long
    Dim x As Range
    Dim RowNumP As Long
    Dim shB As Worksheet
    Dim shP As Worksheet
    Dim r As Long
           
    Application.ScreenUpdating = False
   
    Set shB = Sheets("Bid Sheet")
    Set shP = Sheets("Proposal")
   
    RowNumP = shB.Cells(Rows.Count, 1).End(xlUp).Row
    Range("A9:D50").Copy Destination:=shB.Range("A60")
   
    For i = 110 To 60 Step -1
    If Trim(Cells(i, 2)) = “” Then
        Rows(i).Delete
    End If
   
Next

    shP.Activate
   
    For j = 1 To RowNumP Step 3
   
        For r = 60 To 110
       
            Cells(j, 1).Value = shB.Cells(r - 1, 2)
       
        Next r
       
    Next j
End Sub

The copy paste part works flawless, but that's it. The above code is not finished, of course, but it's where I was stumped.

 

[Zot]

You do not need to activate to switch between sheet. Since you have already defined shB and shP, you just have to put shB.Cells(row,col) or shP.Cells(row,col) when you run macro. This was it will run faster.

The problem I can see with the code is that when using For - Next loop and you are deleting rows, the count i keep increasing, thus it will skip the next row after deleting.. So, once you delete the row, you need to keep the same i count. Therefore, you need to insert line i = i - 1 after deleting the row. This way you will not skip line.

One more thing to note is that the end of loop row will still the same number even after you delete rows, thus you will keep looping more rows than necessary. For loop will remember the initial count even if the end loop is variable such as RowNumP in this case.

Usually I like to used While (condition) loop when dealing with variable end loop count to be a bit efficient