Thread: Filter,copy and paste in excel

Do I need to use this line of code in all the Filter codes or only in Filter1 sub?

Set Thatwb = Workbooks.Open("C:\destination.xlsx")

Thanks

Ok This shoud do it.

...

Thanks once again...

How should I use the code?
Should I run the Filter1 sub on each sheet?

My Last post is a complete code example.

You want to run the sub SubMyWorkbook from Module1 at the top which will loop through all the worksheets in the sample workbook. It will take care of all the other scripts.

You can place a button on a worksheet to make things easier to run.

If you want this option let me know I can do that as well.

I may have forgotten one thing and that is are all the worksheets in the sample workbook the same? If not I will need to know what other worksheets you have in that workbook that are not the same as the others. I'm sure the data is different but if the layout are the same you should not have any issues.

...

Thanks for the reply Mike.

As you said, the data are not the same whereas the layout is the same for all the worksheets.
Yes I would like to have a button added to the sheet.

I ran the MyWorkbook sub and found that in the destination workbook, the filter from the 1st worksheet in the sample workbook is repeated 3 times, instead on one from each worksheet.

Please find the attached workbook for reference.

Thaks in advance.

I would hope you dont have anymore surprises...lol

Here is a complete update with button.

...

Thanks a lot Mike...

Works perfect....

Great job Mike...

Thanks once again...

Mike

I have changed the code accordingly and found that sheets 6 and 7 were not written.
The column to filter for sheets 5,6 & 7 is column 9 backward from column P.
code:

For MySheet = 1 To 7
    Select Case (MySheet)
    Case Is = 1: B = 6
    Case Is = 2: B = 5
    Case Is = 3: B = 4
    Case Is = 4: B = 2
    Case Is = 5: B = 9 ' count backwards from Col P to get this number.
    Case Is = 6: B = 9 'count backwards from Col P to get this number.
    Case Is = 7: B = 9 'count backwards from Col P to get this number.
    End Select

Please find the attached workbooks for your reference.
Thaks in advance.

Ok Here are the changes Below:

1st Change the current dim statment to this...

Dim MySheet As Integer, B As Integer, i As Integer, MyNumber As String, BB As Integer

2nd Change this pcs of code to this...

For MySheet = 1 To 7
    Select Case (MySheet)
    Case Is = 1: B = 6
    Case Is = 2: B = 5
    Case Is = 3: B = 4
    Case Is = 4: B = 2
    Case Is = 5: B = 9: BB = 5
    Case Is = 6: B = 9: BB = 6
    Case Is = 7: B = 9: BB = 7
    End Select

3rd Change this pcs of code to this...

For Each cell In R
If MySheet <= 4 Then
    If cell.Value > 0 Then
        MyNumber = cell.Value
        Exit For
    End If
Else
    Select Case (BB)
        Case Is = 5: MyNumber = 28
        Case Is = 6: MyNumber = 39
        Case Is = 7: MyNumber = 16
    End Select
Exit For
End If
Next cell

And Last change this pcs near the bottom to this.

For Each wks In Thatwb.Worksheets
wks.Activate
Range("A1:O1").Select
ActiveWindow.Zoom = True
Range("A1").Select
Next wks

You Know the drill by now, post back any issues.

...

Works perfect...

Thanks a lot Mike.