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
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