Error 13, type mismatch

[PGNewbie]

I'm copy certain cell from one sheet to another sheet. My code searches for a value on sheet set as Condition and then searches the Source sheet for the value. Once found it copies specific cells of the row from the Source sheet to a new row on the Target sheet. I'm getting run time error 13, type mismatch. I'm not sure what I've done wrong.

Sub specific_columns
Dim g As Range
    Dim h As Range
    Dim z As Integer
    Dim Source1 As Worksheet
    Dim Target1 As Worksheet
    Dim Condition1 As Worksheet
    
    
    

    Set Source1 = ActiveWorkbook.Worksheets("Input")
    Set Condition1 = ActiveWorkbook.Worksheets("Top10NoisiestClient")
    
    
       'This will start copying data to Target sheet at row 3
    
        
      For Each g In Condition1.Range("A2:A11") 'specifiy condition
      
      'create worksheet for each value in condition
      
      Set Target1 = Sheets.Add(after:=ActiveSheet)
      
      Target1.Name = "Client-" & g.Value
      
      
        'add code to show client name on line A1
        Target1.Range("A1").Value = g.Offset(, 1).Value
        Target1.Range("A1:K1").Merge
        Target1.Range("A1").Interior.ColorIndex = 45
        
        [A2:K2] = Split("Start_Time Host_Name Client_Name Message Count Probe Source Origin Status End_Time Ack_By")
        
            For Each h In Source1.Range("M" & Rows.Count).End(xlUp)
          
                If h.Value = g.Value Then
          
                    Source1.Cells(h, 5).Copy Target1.Cells(lastrow + 1, 1) '<-- causes error
                    Source1.Cells(h, 11).Copy Target1.Cells(lastrow + 1, 2)
                    Source1.Cells(h, 22).Copy Target1.Cells(lastrow + 1, 3)
                    Source1.Cells(h, 6).Copy Target1.Cells(lastrow + 1, 4)
                    Source1.Cells(h, 8).Copy Target1.Cells(lastrow + 1, 5)
                    Source1.Cells(h, 17).Copy Target1.Cells(lastrow + 1, 6)
                    Source1.Cells(h, 12).Copy Target1.Cells(lastrow + 1, 7)
                    Source1.Cells(h, 13).Copy Target1.Cells(lastrow + 1, 8)
                    Source1.Cells(h, 3).Copy Target1.Cells(lastrow + 1, 9)
                    Source1.Cells(h, 4).Copy Target1.Cells(lastrow + 1, 10)
                    Source1.Cells(h, 21).Copy Target1.Cells(lastrow + 1, 11)
                  
                
               End If
              
            Next h
          
    
    Range("A1").Select
    'Zoom to first cell
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1

        Next g
End Sub

 

[Joe4]

Where is lastrow being calculated?

 

[PGNewbie]

Where is lastrow being calculated?

Thanks for noticing, I didn't have the lastrow in there. I added as per the code below but now get error 424, object required

Sub specific_columns
Dim g As Range
    Dim h As Range
    Dim z As Integer
    Dim Source1 As Worksheet
    Dim Target1 As Worksheet
    Dim Condition1 As Worksheet
    Dim lastrow As Long
    
    
    

    Set Source1 = ActiveWorkbook.Worksheets("Input")
    Set Condition1 = ActiveWorkbook.Worksheets("Top10NoisiestClient")
    
    
       'This will start copying data to Target sheet at row 3
    
        
      For Each g In Condition1.Range("A2:A11") 'specifiy condition
      
      'create worksheet for each value in condition
      
      Set Target1 = Sheets.Add(after:=ActiveSheet)
      
      Target1.Name = "Client-" & g.Value
      
      
        'add code to show client name on line A1
        Target1.Range("A1").Value = g.Offset(, 1).Value
        Target1.Range("A1:K1").Merge
        Target1.Range("A1").Interior.ColorIndex = 45
        
        [A2:K2] = Split("Start_Time Host_Name Client_Name Message Count Probe Source Origin Status End_Time Ack_By")
        lastrow = Target1.Cells(sht.Rows.Count, "A").End(xlUp).Row       '<-- Error 424, Object required

            For Each h In Source1.Range("M" & Rows.Count).End(xlUp)
          
                If h.Value = g.Value Then
          
                    Source1.Cells(h, 5).Copy Target1.Cells(lastrow + 1, 1) 
                    Source1.Cells(h, 11).Copy Target1.Cells(lastrow + 1, 2)
                    Source1.Cells(h, 22).Copy Target1.Cells(lastrow + 1, 3)
                    Source1.Cells(h, 6).Copy Target1.Cells(lastrow + 1, 4)
                    Source1.Cells(h, 8).Copy Target1.Cells(lastrow + 1, 5)
                    Source1.Cells(h, 17).Copy Target1.Cells(lastrow + 1, 6)
                    Source1.Cells(h, 12).Copy Target1.Cells(lastrow + 1, 7)
                    Source1.Cells(h, 13).Copy Target1.Cells(lastrow + 1, 8)
                    Source1.Cells(h, 3).Copy Target1.Cells(lastrow + 1, 9)
                    Source1.Cells(h, 4).Copy Target1.Cells(lastrow + 1, 10)
                    Source1.Cells(h, 21).Copy Target1.Cells(lastrow + 1, 11)
                  
                
               End If
              
            Next h
          
    
    Range("A1").Select
    'Zoom to first cell
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1

        Next g
End Sub

 

[Joe4]

Now you have introduced another variable you have not defined, "sht".
You cannot just use variables that you have not set to anything.

You would do well to place this at the very top of your code, before your "Sub specific_columns()" line:

Option Explicit

Sub specific_columns
...

That "Option Explicit" command will force you to declare all your variables before using them.
That will at least help to ensure you haven't introduce any variables without declaring them (you still need to set them to something though too).

 

[PGNewbie]

Now you have introduced another variable you have not defined, "sht".
You cannot just use variables that you have not set to anything.

You would do well to place this at the very top of your code, before your "Sub specific_columns()" line:

Option Explicit

Sub specific_columns
...

That "Option Explicit" command will force you to declare all your variables before using them.
That will at least help to ensure you haven't introduce any variables without declaring them (you still need to set them to something though too).

Thank you so much, sht should be Target1. replacing that leads me back to the original error on the same line

Sub specific_columns
Dim g As Range
    Dim h As Range
    Dim z As Integer
    Dim Source1 As Worksheet
    Dim Target1 As Worksheet
    Dim Condition1 As Worksheet
    Dim lastrow As Long
    
    
    

    Set Source1 = ActiveWorkbook.Worksheets("Input")
    Set Condition1 = ActiveWorkbook.Worksheets("Top10NoisiestClient")
    
    
       'This will start copying data to Target sheet at row 3
    
        
      For Each g In Condition1.Range("A2:A11") 'specifiy condition
      
      'create worksheet for each value in condition
      
      Set Target1 = Sheets.Add(after:=ActiveSheet)
      
      Target1.Name = "Client-" & g.Value
      
      
        'add code to show client name on line A1
        Target1.Range("A1").Value = g.Offset(, 1).Value
        Target1.Range("A1:K1").Merge
        Target1.Range("A1").Interior.ColorIndex = 45
        
        [A2:K2] = Split("Start_Time Host_Name Client_Name Message Count Probe Source Origin Status End_Time Ack_By")
        lastrow = Target1.Cells(Target1.Rows.Count, "A").End(xlUp).Row       

            For Each h In Source1.Range("M" & Rows.Count).End(xlUp)
          
                If h.Value = g.Value Then
          
                    Source1.Cells(h, 5).Copy Target1.Cells(lastrow + 1, 1) '<-- Error 13, Type mismatch
                    Source1.Cells(h, 11).Copy Target1.Cells(lastrow + 1, 2)
                    Source1.Cells(h, 22).Copy Target1.Cells(lastrow + 1, 3)
                    Source1.Cells(h, 6).Copy Target1.Cells(lastrow + 1, 4)
                    Source1.Cells(h, 8).Copy Target1.Cells(lastrow + 1, 5)
                    Source1.Cells(h, 17).Copy Target1.Cells(lastrow + 1, 6)
                    Source1.Cells(h, 12).Copy Target1.Cells(lastrow + 1, 7)
                    Source1.Cells(h, 13).Copy Target1.Cells(lastrow + 1, 8)
                    Source1.Cells(h, 3).Copy Target1.Cells(lastrow + 1, 9)
                    Source1.Cells(h, 4).Copy Target1.Cells(lastrow + 1, 10)
                    Source1.Cells(h, 21).Copy Target1.Cells(lastrow + 1, 11)
                  
                
               End If
              
            Next h
          
    
    Range("A1").Select
    'Zoom to first cell
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1

        Next g
End Sub

 

[Joe4]

Ah, I see the problem now.

You have defined "h" to be a range, not a row.
So you cannot do:
Cells(h, 5)

If you want to get the row that "h" is in, you would need to do:
Cells(h.Row, 5)

 

[PGNewbie]

Ah, I see the problem now.

You have defined "h" to be a range, not a row.
So you cannot do:
Cells(h, 5)

If you want to get the row that "h" is in, you would need to do:
Cells(h.Row, 5)

Thanks, that removed the error!
Unfortunately nothing copied over. I have 30 empty new sheets

 

[Joe4]

Unfortunately, without having access to your sheets and data, it is difficult to figure what your code is doing and where the fault in your logic lies.

I would recommend stepping through your code line-by-line, using the F8 key, and hovering over the variables so you can see what each variable is at each point in time so you can see if they are what you expect them to be. For example, the first time through, you should have a good idea what the values of h.row and lastrow are. See if they actually are what you expect.

If you are having trouble using the hover-over technique, then temporary insert breakpoints and a bunch of message boxes, i.e.
MsgBox "The value of h.Row is: " & h.Row

These are important tools you should learn to use to debug your code. You step through your code to see what is happening each step of the way.
Many times, things aren't working the way you think (many times, when working on multi-sheet problems, you often aren't on the sheet you think you are!).

I should mention that people often also use the Immediate Window in debugging. I don't use much of that myself, but you can Google that, if you are interested in learning that technique as well.

 

[PGNewbie]

So I figured out why my sheets were coming up empty. Changing this line

For Each h In Source1.Range("M" & Rows.Count).End(xlUp)

To

For Each h In Source1.Range("M2:M6893")

Fixed the issue, but now what I see happening is that it keeps rewriting my value on to row 3, I should have upwards to 200 rows on each sheet starting at row 3, but the row has the very last value that should be there on the sheet

 

[Joe4]

I am not sure what you current version of your code looks like, but in the latest version you posted, you were calculating "lastrow" BEFORE your loop.
So, in your loop, if you don't increment the value of lastrow by one at the end of the loop (i.e. lastrow = lastrow + 1), you are just going to keep overwriting the same row over and over.