Trying to eliminate having to activate the destination worksheet (ws2) in my macro

sspatriots

Well-known Member
Joined
Nov 22, 2011
Messages
585
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm not sure if the bit of code below is enough to get the help I need or not. The code is extremely long, and I can post more of it if needed. This bit basically copies a filtered portion of one column in one table to a second table in another worksheet. Trying to figure out how to re-write this and avoid having to activate "ws2" if possible. Right now as it stands, when I remove the ws2 activate line, the destination table doesn't populate the information in the destination table on the second worksheet. Thanks, SS

VBA Code:
'Safety
EQPT3:

    '    ws1.Activate

    ws1.ListObjects("G2JobList").Range.AutoFilter   'Clear previous filters
    
    ws1.ListObjects("G2JobList").Range.AutoFilter Field:=col99, Criteria1:="<>"
    ws1.ListObjects("G2JobList").Range.AutoFilter Field:=col5, Criteria1:="="
    ws1.ListObjects("G2JobList").Range.AutoFilter Field:=col6, Criteria1:=">=" & Date, Operator:= _
        xlAnd, Criteria2:="<=" & Date + 28

    With tb1

        On Error GoTo EQPT4

        If ws1.Range("G2JobList[[#All],[Job Name]]").SpecialCells(xlCellTypeVisible).Count > 1 Then
            ws1.Range("G2JobList[[Job Name]]").SpecialCells(xlCellTypeVisible).Copy


                ws2.Activate

            With ws2.Range("Order_By_Report[[Job Name]]")
                n = Columns(.Column).Resize(, .Columns.Count).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                Cells(n + 1, .Column).PasteSpecial xlPasteValues
            End With
        End If
    End With    'Temporary

'Governor    'Temporary
EQPT4:    'Temporary
 
Just incorporated all of my other column code into this and seems to work great. I'm not sure who to mark the solution under for this one because it came from both of you. Thanks again for all the help. SS
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,223,941
Messages
6,175,537
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top