Please assist in modifying my macro with a set range

[Phisig500]

After hours or struggle (albeit a great learning experience), I finished the below Macro. The macro basically checks a column for text in each cell, and if text has been entered, it copy's and pastes special the line (s) on a new sheet. The issue I am having is that I have slicers in column A of my worksheet therefore all the cells are blank. When I run the macro it finds the last row, but then stops because it moves to column A, which is blank, and then stops. The range that I need to the macro to run in for this particular sheet is B1:W113, but i do not know quite how to change it to limit it to the specific rage. If anyone can help, it would be greatly appreciated.


Thank you!!


Sub TestagainSAFE()
Dim i As Long
Dim lr1 As Long, lr2 As Long
Dim Delta As String
Dim wks1 As Worksheet, wks2 As Worksheet
Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("Sheet2") 'change to suit
lr1 = wks1.Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lr1
Delta = wks1.Cells(i, "Y").Value
If Not IsEmpty(Len(Delta)) Then
If Len(Delta) <> 0 Then
lr2 = wks2.Cells(Rows.Count, "A").End(xlUp).Row + 1
wks1.Cells(i, "B").EntireRow.Copy
wks2.Cells(lr2, "A").PasteSpecial Paste:=xlPasteValues
End If
End If
Next i
MsgBox "SPI financial inquiries have been submitted", vbInformation
End Sub

 

[archstantn]

Interpreting your description to mean that you want rows pasted, starting in column "B" of the second sheet, you can try the following:

Dim i As Long
 Dim lr1 As Long, lr2 As Long
 Dim Delta As String
 Dim x As Integer
 Dim wks1 As Worksheet, wks2 As Worksheet
 Set wks1 = Worksheets("Sheet1")
 Set wks2 = Worksheets("Sheet2") 'change to suit
 lr1 = wks1.Cells(Rows.Count, "B").End(xlUp).Row
 For i = 2 To lr1
 Delta = wks1.Cells(i, "Y").Value
 'If Not IsEmpty(Len(Delta)) Then
 If Len(Delta) <> 0 Then
 lr2 = wks2.Cells(Rows.Count, "B").End(xlUp).Row + 1
 x = wks1.Cells(i, "B").EntireRow.Columns.Count - 5
 wks1.Range(Cells(i, "B"), Cells(i, x)).Copy
 wks2.Cells(lr2, "B").PasteSpecial Paste:=xlPasteValues
 End If
 'End If
 Next i
 MsgBox "SPI financial inquiries have been submitted", vbInformation
 Application.CutCopyMode = False