Several IF statements that should be And statements

[vanfel]

I am trying to write a code to copy information from one worksheet to another that has IF Statements because of criteria that needs to be met in certain cells. But for some reason, it's not picking up the 2nd IF statement. It's giving me every type. Here is the code.

Sub BuyertoIssued()
Dim ws As Worksheet
Set ws = Sheets("Buyer to Issued")
ws.Rows("2:" & Rows.Count).ClearContents

a = Worksheets("Bid Worklog").Cells(Rows.Count, 1).End(xlUp).Row

For I = 2 To a

If Worksheets("Bid Worklog").Cells(I, 1).Value = "Active" Then
    If Worksheets("Bid Worklog").Cells(I, 29).Value = "IFB" Then
       If Worksheets("Bid Worklog").Cells(I, 53).Value > 15 Then GoTo CopyRow1
        End If
CopyRow1:   Worksheets("Bid Worklog").Rows(I).Copy
   
   Worksheets("Buyer to Issued").Activate
   b = Worksheets("Buyer to Issued").Cells(Rows.Count, 1).End(xlUp).Row
   Worksheets("Buyer to Issued").Cells(b + 1, 1).Select
   ActiveSheet.Paste
   Worksheets("Bid Worklog").Activate
   
            End If
        
            
Next
Application.CutCopyMode = False

End Sub

I've read about CASE statements, but I'm confused on that part. What is so weird, is that this was working, but then I changed the cell locations so now it's not picking it up correctly. Any help would be appreciated.
Thank you.

[6StringJazzer]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code] [/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[6StringJazzer]

Don't use GoTo. Ever.

I can't figure out what you want your code to do, because your code will always execute this line

CopyRow1: Worksheets("Bid Worklog").Rows(I).Copy

regardless of the If condition. If the condition is True, it goes to this statement. If it's False, it ends up there anyway.

Also you do not have to Activate and Select objects to operate on them.

My best guess is this. If it does not work please attach your file for testing.

Sub BuyertoIssued()
Dim ws As Worksheet
Set ws = Sheets("Buyer to Issued")
ws.Rows("2:" & Rows.Count).ClearContents

a = Worksheets("Bid Worklog").Cells(Rows.Count, 1).End(xlUp).Row

For I = 2 To a

   If Worksheets("Bid Worklog").Cells(I, 1).Value = "Active" And Worksheets("Bid Worklog").Cells(I, 29).Value = "IFB" And Worksheets("Bid Worklog").Cells(I, 53).Value > 15 Then

      Worksheets("Bid Worklog").Rows(I).Copy
   
      b = Worksheets("Buyer to Issued").Cells(Rows.Count, 1).End(xlUp).Row
      Worksheets("Buyer to Issued").Cells(b + 1, 1).EntireRow.Paste

   
   End If
                 
Next
Application.CutCopyMode = False

End Sub

[vanfel]

This code gives me a type mismatch on the If statement. Would that be because the format in those cells in the IF statements are not all text? For instance the value in Cell 53 is a number.
I have attached a sample file.

[6StringJazzer]

Cell BA36 has a #VALUE error. That is because the formula is using DAYS, and that function is referencing cells that have "N/A" (text). DAYS gives an error if it is expecting dates but gets text. Then when the code refers to that cell it raises an error.

My suggestion would be to fix your formula so that it does not produce an error. Maybe something like

=IFERROR(IF(DAYS($AZ36,$P36)< 0, 0,(DAYS($AZ36,$P36))),"")

[vanfel]

I fixed the cells by adding the IFERROR formula, but I get an error on the code you gave me:
Worksheets("Buyer to Issued").Cells(b + 1, 1).EntireRow.Paste
The error is Object doesn't support this property or method. I have tried looking this error up to understand it, but I just don't. Any suggestions on what that may be?

[vanfel]

Is there a way to only paste certain cells instead of the entire row? For instance, I only want to copy over Columns C, D, E, F, P, Q, R, T, U, V, W and AD.

[AB33]

should be

 Worksheets("Buyer to Issued").Cells(b + 1, 1).EntireRow.PasteSpecial xlValues

[AB33]

 Union(Range("C" & i & ":" & "F" & i), Range("P" & i & ":" & "R" & i), Range("T" & i & ":" & "W" & i, Range("AD" & i))).Copy
 
 Worksheets("Buyer to Issued").Cells(b + 1, 1).PasteSpecial xlValues

[6StringJazzer]

vanfel can you clarify the data layout you want on the pasted sheet?

 Union(Range("C" & i & ":" & "F" & i), Range("P" & i & ":" & "R" & i), Range("T" & i & ":" & "W" & i, Range("AD" & i))).Copy
 
 Worksheets("Buyer to Issued").Cells(b + 1, 1).PasteSpecial xlValues

This will certainly paste the desired cells, but they will be pasted contiguously, not according to the source layout. That is, data from C:F will be pasted into A:D, data from P:R will be pasted into E:G, and data from T:W will be pasted into H:K, AD will be pasted to L. Your sample file does not have column headers on the Buyer to Issued sheet so it's hard to tell what you are expecting.