need to paste range or rows as values

[kxlvba]

Hello,
I have about 31 worksheets by name of 1,2,3,4.....31.
Now, I need to find the last entry and from row number 3 to this row I need to paste entries as values. Because there are lookup formulas as well.

I tried using the below mentioned code, but it is not working. Please help.

Chosen 2 because first sheet is INDEX.

Sub update()

Dim ws As Worksheet
Dim kctr, irow As Integer

For kctr = 2 To 32

Set ws = ThisWorkbook.Sheets(kctr)
irow = ws.Cells(Rows.Count, 2).End(xlUp).Row

Rows(irow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False

kctr = kctr + 1

Next kctr

End Sub

 

[onlyadrafter]

Hello

try

Sub update()
Dim ws As Worksheet
Dim kctr, irow As Integer

For kctr = 2 To ActiveWorkbook.Sheets.Count
    With Sheets(kctr)
        .Rows("3:" & Cells(Rows.Count, 2).End(xlUp).Row).Copy
        .Range("A3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
Next kctr
End Sub

 

[kxlvba]

Thanks,
However this codes does the above changes for all worksheets. There is a cell B3 in each worksheet. How do I further place a condition, by which only if cell B3 is populated or <> "", then only paste that worksheets row as values. Rest of the worksheets with no data should be untouched.

 

[onlyadrafter]

Hello

like this?

Sub update()
Dim ws As Worksheet
Dim kctr, irow As Integer

For kctr = 2 To ActiveWorkbook.Sheets.Count
    If Not IsEmpty(Sheets(kctr).Range("B3").Value) Then
        With Sheets(kctr)
            .Rows("3:" & Cells(Rows.Count, 2).End(xlUp).Row).Copy
            .Range("A3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
    End If
Next kctr
End Sub

 

[kxlvba]

Oh yes, you are The Ghost Who Walks!!..
Thanks a ton.