How do I skip an iteration if condition is true?

Hello, I would like to make some edits to my code to make it work with different types of worksheets. Currently I'm copying and pasting fixed cells, but I would like to incorporate search/match functions in case the information i need are in different cells on different worksheets.

Worksheet Information:
- B2 to B5: Titles of department info
- C2 to C5: Corresponding department info
- Row 8: Header for products
- Cell A8 = serial number. Cell A9 = "Product Information"
- If the department's worksheet has no products, Cell A9 would say "NIL"
- However, some departments might have different formats (eg extra blank column or extra row which cause the code to copy the values

Code's current function:
1. Add 4 columns on left
2. Copy and paste C2 to C5 (now G2 to G5)
3. Copy rows and paste on another sheet (from row 9 to last row)

     
        'Loop each worksheet
            Dim wscount As Integer
    Dim a As Integer
    wscount = ActiveWorkbook.Worksheets.Count
    For a = 1 To wscount
        'activate worksheet (a)
        Worksheets(a).Activate
        'skip if NIL
        
        'Add column and paste department info
        Dim i As Integer
    
Columns("A:D").Insert shift:=xlToRight, _
      CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow

    
    'Loop for cells with content
i = 9
    Do While Cells(i, 6) <> "" 'Currently this includes "NIL"
        Range("G2").Copy
        Cells(i, 1).PasteSpecial Paste:=xlPasteValues
         Application.CutCopyMode = False
         
        Range("G3").Copy
        Cells(i, 2).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        
        Range("G4").Copy
        Cells(i, 3).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        
        Range("G5").Copy
        Cells(i, 4).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False
        i = 1 + i
    Loop
        'Copy row info for A9 to last row
          Dim lrow As Long
lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A9:Z" & lrow).Copy
      
       'Paste on last row of masterlist
       Dim currentwb As Workbook
       Dim targetsheet As Worksheet
       Set currentwb = ThisWorkbook
       
       Set targetsheet = currentwb.Worksheets(4)

        targetsheet.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
    
  'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False
   
   'Next worksheet
        Next
        

Open in new window



I would like to:

- Copy Department information with reference to its labels (currently in B2 to B5), instead of copying directly from C2 to C5. This is because some worksheets might have the chunk of B2:C5 in a different location. Eg. find 'Department', copy cell on the right and paste
- Copy from one row below 'Product Information' to last row and paste elsewhere. Same reason as above. Currently copying from row 9 but in certain worksheets it might be row 8 or row 10
- Skip the iteration if cell below 'Product Name' says "NIL" or "nil" or "Nil" (non-case sensitive).


I'm new to VBA, and have been learning for about a week. This is my first coding experience and I'm still getting the hang of the syntax, so I would appreciate full codes with explanations instead of suggestions of functions! Thank you in advance (:
Pearlyn TanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Could you upload a workbook with some sample data, preferably with sheets that show some of the possible variations in format?
aikimarkCommented:
You can use a "Do Group" (single iteration loop) like I detail in this article:
https://www.experts-exchange.com/articles/2755/Next-Iteration-functionality-in-VB-classic-and-VBA.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.