CODEWITHSUNDEEP
excelvba
KevinBrad
KevinBrad

Reputation: 1

Having trouble copying entire row of data from one sheet to another using VBA

I am trying to organize my spending and my first sheet in my excel document contains all of my purchases. I have "Date" in column A, "Expense Category" in Column B, "Detail" in column C and "Cost" in Column D. I want to copy an entire row to Sheet 2 if Column B has the word "Gas".

I have tried to look up this question and found code from the following link: https://www.extendoffice.com/documents/excel/3723-excel-move-row-to-another-sheet-based-on-cell-value.html#a1

I have tried to update the code to reflect my data but I just began learning VBA and am getting the "Subscript out of range" error and the "I = Worksheets("Sheet1").UsedRange.Rows.Count is being highlighted yellow.

This is my code currently, based on the websites code for copying rows to other sheets:

Sub MoveRowBasedOnCellValue()

Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Sheet1").UsedRange.Rows.Count
J = Worksheets("Sheet2").UsedRange.Rows.Count
If J = 1 Then
  If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 
  0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("B1:B" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Gas" Then
    xRg(K).EntireRow.Copy Desitination:=Worksheets("Sheet2").Range("A" & J + 1)
    J = J + 1
End If
Next
Application.ScreenUpdating = True


End Sub

Any help is greatly appreciated!

Upvotes: 0

Views: 251

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

I believe you need to change

For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Gas" Then
    xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
    J = J + 1
End If

For:

For each KCell in xRg
If KCell.Value = "Gas" Then
    KCell.EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
    J = J + 1
End If
Next KCell

(looping through each cell in xRg)

Upvotes: 2

Related Questions