Error:

[SBF12345]

Greetings again,

I am receiving an error "wrong number of arguments or invalid property assignment" on the line below:

Set Fnd = MAIN.Worksheets("M_" & AC).Range.Find(What:=COM.Worksheets(AC).Cells(j, 1).Value, After:=MAIN.Worksheets("M_" & AC).Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)

Also, some of the variables I am using included in this line:

Dim Fnd As Range
Set COM = Workbooks("COMM_COMBINED.xls")
Set MAIN = Workbooks("COMM_LD_MAIN.xls")

j = 3

For Each ws In COM.Worksheets    
    AC = ws.Name
    
    Do While COM.Worksheets(AC).Cells(j, 1) <> ""
    
        Set Fnd = MAIN.Worksheets("M_" & AC).Range.Find(What:=COM.Worksheets(AC).Cells(j, 1).Value, After:=MAIN.Worksheets("M_" & AC).Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
        
        If Not Fnd Is Nothing Then
        
            MAIN.Worksheets("M_" & AC).Range(Fnd, Fnd.End(xlToRight)).Copy
            COM.Worksheets(AC).Activate
            Cells(j, 1).Select
            Selection.End(xlToRight).Offset(0, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
        End If
        
        j = j + 1
        
    Loop
    
    COM.Worksheets(AC).Activate
            Cells(j, 1).Select
            b = Selection.End(xlToRight).Offset(0, 1).Column
    
    Next ws

My intention with this chunk is to loop through dates on one worksheet to test whether or not the same date is included on another sheet by a similar name in a different workbook. If there are matching dates then I would like to copy the range from the MAIN workbook using the current fnd range variable and paste it on another sheet.

Thanks for all your help and input!

 

[StephenCrump]

I did rewrite it with a set of loops and it seems to be working well and doing so within a reasonable amount of time.

I am glad you worked out that you needed another level of looping ... it wasn't clear from your previous posts that there might be multiple find results for each value of COMVAR.

I am guessing that you want to start at row 3 in each worksheet? If so, you'll need j=3 inside the For each ws ... loop

You can also do this more efficiently by using AutoFilter. For each value of COMVAR, filter for that value and simply copy all the filtered results in one go. You can use the macro recorder to help you with the syntax. It will be much faster than comparing values line by line.