Sheet Copy

kalion21

New member
Joined
Aug 1, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
2016
Hello friends!

I am trying to write a VBA snippet that will find a specific string in sheet 1 then copy it to sheet 2 starting from the first column. Below is my badly-written code which finds the desired string in sheet 1 and copies it to every row in sheet 2. I am not sure how to increase the sheet 1 counter to correct this issue, please help :eek2:!


Code:
Sub RowCopy()
    For i = 3 To 500 'sheet 1 counter
            TargetString = InStr(Rows.Range("L" & i).Value, "Target Text 1") Or InStr(Rows.Range("L" & i).Value, "Target Text 2") 'sheet 1 target strings
              
              If TargetString > 0 Then 'if TargetString exists then
                 For j = 3 To 500 'sheet 2 counter
                    Worksheets("sheet2").Rows.Range("B" & j).Value = Left(Rows.Range("B" & i).Value, TargetString + 46) 'copy to second sheet
                 Next j
            End If   
    Next i
End Sub
 
Last edited:
If you put the cursor anywhere within your macro and hit the F8 key
the macro will execute one line of code each time you hit F8 and you can follow what the code is doing.
I'm sure you will quickly see what's going on.

I suspect you'll want to assign a starting row for j at the beginning of the macro and use j=j+1 instead of the j loop within the i loop.
 
If you put the cursor anywhere within your macro and hit the F8 key
the macro will execute one line of code each time you hit F8 and you can follow what the code is doing.
I'm sure you will quickly see what's going on.

I suspect you'll want to assign a starting row for j at the beginning of the macro and use j=j+1 instead of the j loop within the i loop.

Thank you so much NoS, that is exactly what I needed! After looking at this for a while I ruled out such a simple solution...
 
Back
Top