Results 1 to 3 of 3

Thread: Sheet Copy

  1. #1
    Neophyte kalion21's Avatar
    Join Date
    Aug 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016

    Unhappy Sheet Copy



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 !


    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 by kalion21; 2019-08-01 at 06:21 PM.

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    769
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  3. #3
    Neophyte kalion21's Avatar
    Join Date
    Aug 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016

    Cool

    Quote Originally Posted by NoS View Post
    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...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •