Looking for an easier solution to a table copy

maywal

New Member
Joined
Dec 19, 2016
Messages
29
Hi,

I have a monitoring sheet I am using for a small business and it monitors everything, working great. However, there are certain aspects I would like the employees to see. I do not want them to see the whole sheet and I do not want them to be able to edit it. I am using Microsoft365. I am aware I can share for viewing only.

However, I have duplicated the sheet so that it shows only the columns I require. I then filter it to remove any completed work however it does not automatically update and I need to continuously unfilter and filter again. Is there a way of automatically updating only the active jobs whilst sharing only select columns?

Thanks,
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
This is what I am doing already however in this sheet I have the columns I want to show. I then want to filter one column to show only the work that is not completed (I have a column monitoring the stage of the work). So I filter and remove completed works but this does not update automatically. I then have to go back into this sheet frequently as work updates and reselect completed then unselect it to get the updated works. I want to be able to do this automatically as I don't want pages and pages of irrelevant data. Just the relevant date for right now.
 
Upvote 0
A simple solution would be to use VBA to replicate what you are doing and then a button press would give you what you want in about 2 seconds

VBA would simply
- clear "staff" sheet
- filter table as required
- copy the required columns to "staff" sheet
- reset filter on table

Would you like to try that?
If so
- how many columns are in the table?
- which columns do you want copying to "staff" sheet?
- which column is completed\not completed
- what are the value that denotes completed\not completed

Please provide the info like this to make it easy to follow
- how many columns are in the table? 12
- which columns do you want copying to "staff" sheet? 1,2,3,4,6,9
- which column is completed\not completed 10
- what are the value that denotes completed\not completed completed = "yes" not completed = "no"

Are you using a structured Excel Table (created with Insert Table) or is it tabulated data with a data filter?
 
Last edited:
Upvote 0
A simple solution would be to use VBA to replicate what you are doing and then a button press would give you what you want in about 2 seconds

VBA would simply
- clear "staff" sheet
- filter table as required
- copy the required columns to "staff" sheet
- reset filter on table

Would you like to try that?
If so
- how many columns are in the table?
- which columns do you want copying to "staff" sheet?
- which column is completed\not completed
- what are the value that denotes completed\not completed

Please provide the info like this to make it easy to follow
- how many columns are in the table? 12
- which columns do you want copying to "staff" sheet? 1,2,3,4,6,9
- which column is completed\not completed 10
- what are the value that denotes completed\not completed completed = "yes" not completed = "no"

Are you using a structured Excel Table (created with Insert Table) or is it tabulated data with a data filter?


Hello,

Yes if this will work that would be great.
Info below:

- how many columns are in the table? 23
- which columns do you want copying to "staff" sheet? 2,3,4,5,6, 12,13,17
- which column is completed\not completed 1
- what are the value that denotes completed\not completed completed = "completed" not completed = "booked", "cancelled", "typing", "T&C Sent", "T&C accepted", "invoice sent"
 
Upvote 0
So you want?
columns : 2,3,4,5,6,12,13,17
rows : everything that is not flagged "Completed" in column 10

You did not reply to
Are you using a structured Excel Table (created with Insert Table) or is it tabulated data with a data filter?
 
Upvote 0
So you want?
columns : 2,3,4,5,6,12,13,17
rows : everything that is not flagged "Completed" in column 10

You did not reply to
Are you using a structured Excel Table (created with Insert Table) or is it tabulated data with a data filter?


This is in a structured excel table. The column that denotes completed or not is Column 1. Yes I want

columns : 2,3,4,5,6,12,13,17
rows : everything that is not flagged "Completed" in column 1

Thanks,
 
Upvote 0
Test on a COPY of your workbook!

This works for me
- add the code as instructed below
- amend sheet name StaffSheet
- {ALT}{F8} to list the macros in Excel
- run CopyTable
- if it works add a button to the sheet and assign the macro to it

Code goes in sheet module of sheet containing the original table
right-click sheet tab \ select View Code \ paste code below into code window \ and close code window (X in top right corner)
Code:
Option Explicit
Sub CopyTable()
[I][COLOR=#006400]'variables[/COLOR][/I]
    Const StaffSheet = "[COLOR=#ff0000]StaffSheet[/COLOR]"
    Const cols = "2, 3, 4, 5, 6, 12, 13, 17"
    Dim tbl As ListObject, wsS As Worksheet, uRng As Range, Rng As Range, col As Variant, colArray As Variant
    Set wsS = Sheets(StaffSheet)
    Set tbl = Me.ListObjects(1)
    colArray = Split(cols, ",")
[I][COLOR=#006400]'clear old data and reset filter[/COLOR][/I]
    Optimise True
    On Error Resume Next
    AllData wsS
    wsS.Cells.Clear
    AllData Me
[I][COLOR=#006400]'set filter and set range to copy[/COLOR][/I]
    With tbl
        .Range.AutoFilter Field:=1, Criteria1:="<>completed", Operator:=xlAnd
        For Each col In colArray
            Set Rng = .ListColumns(CInt(col)).Range
            If uRng Is Nothing Then Set uRng = Rng Else Set uRng = Union(uRng, Rng)
        Next
    End With
[I][COLOR=#006400]'copy and paste[/COLOR][/I]
    uRng.SpecialCells(xlCellTypeVisible).Copy
    With wsS.Range("A1")
        .PasteSpecial (xlPasteFormats)
        .PasteSpecial (xlPasteValues)
        .CurrentRegion.EntireColumn.AutoFit
        .AutoFilter
    End With
[I][COLOR=#006400]'tidy up[/COLOR][/I]
    Application.CutCopyMode = False
    AllData Me
    wsS.Select
    Optimise False
End Sub

Private Sub AllData(ws As Worksheet)
    On Error Resume Next
    ws.ShowAllData
    On Error GoTo 0
End Sub

Private Sub Optimise(TrueFalse As Boolean)
    With Application
        .ScreenUpdating = Not TrueFalse
        .EnableEvents = Not TrueFalse
        .Calculation = xlCalculationAutomatic
        If TrueFalse Then .Calculation = xlCalculationManual
    End With
End Sub

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
1
Column
01
Column
02
Column
03
Column
04
Column
05
Column
06
Column
07
Column
08
Column
09
Column
10
2
bookedjob001
1,740.48​
16/02/2019​
84​
63​
45​
40​
47​
72​
3
cancelledjob002
1,305.45​
19/02/2019​
88​
89​
87​
45​
66​
69​
4
typingjob003
1,305.45​
19/02/2019​
30​
80​
64​
20​
93​
31​
5
T&C Sentjob004
1,740.44​
15/02/2019​
32​
42​
87​
49​
35​
59​
6
T&C acceptedjob005
1,305.45​
19/02/2019​
65​
33​
12​
70​
64​
51​
7
invoice sentjob006
2,175.55​
15/02/2019​
35​
46​
84​
30​
17​
99​
8
completedjob007
1,740.56​
18/02/2019​
32​
96​
84​
13​
30​
32​
9
completedjob008
435.15​
19/02/2019​
68​
44​
86​
53​
11​
48​
10
completedjob009
435.14​
18/02/2019​
60​
47​
54​
72​
85​
97​
11
typingjob010
435.14​
18/02/2019​
45​
50​
53​
48​
30​
75​
12
T&C Sentjob011
435.11​
15/02/2019​
73​
19​
13​
89​
71​
84​
13
T&C acceptedjob012
2,175.55​
15/02/2019​
31​
87​
99​
64​
27​
35​
14
invoice sentjob013
2,175.70​
18/02/2019​
48​
36​
66​
61​
37​
83​
15
invoice sentjob014
435.14​
18/02/2019​
61​
17​
57​
69​
29​
37​
16
invoice sentjob015
870.28​
18/02/2019​
11​
44​
46​
26​
65​
44​
Sheet: TableSheet

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
1
Column
02
Column
03
Column
04
Column
05
Column
06
Column
12
Column
13
Column
17
2
job001
1,740.48​
16/02/2019​
84​
63​
34​
27​
60​
3
job002
1,305.45​
19/02/2019​
88​
89​
82​
23​
61​
4
job003
1,305.45​
19/02/2019​
30​
80​
64​
91​
79​
5
job004
1,740.44​
15/02/2019​
32​
42​
87​
31​
57​
6
job005
1,305.45​
19/02/2019​
65​
33​
39​
86​
29​
7
job006
2,175.55​
15/02/2019​
35​
46​
90​
57​
64​
8
job010
435.14​
18/02/2019​
45​
50​
43​
59​
24​
9
job011
435.11​
15/02/2019​
73​
19​
55​
48​
78​
10
job012
2,175.55​
15/02/2019​
31​
87​
19​
78​
11​
11
job013
2,175.70​
18/02/2019​
48​
36​
11​
91​
23​
12
job014
435.14​
18/02/2019​
61​
17​
53​
99​
16​
13
job015
870.28​
18/02/2019​
11​
44​
81​
67​
68​
14
Sheet: StaffSheet
 
Upvote 0
Test on a COPY of your workbook!

This works for me
- add the code as instructed below
- amend sheet name StaffSheet
- {ALT}{F8} to list the macros in Excel
- run CopyTable
- if it works add a button to the sheet and assign the macro to it

Code goes in sheet module of sheet containing the original table
right-click sheet tab \ select View Code \ paste code below into code window \ and close code window (X in top right corner)
Code:
Option Explicit
Sub CopyTable()
[I][COLOR=#006400]'variables[/COLOR][/I]
    Const StaffSheet = "[COLOR=#ff0000]StaffSheet[/COLOR]"
    Const cols = "2, 3, 4, 5, 6, 12, 13, 17"
    Dim tbl As ListObject, wsS As Worksheet, uRng As Range, Rng As Range, col As Variant, colArray As Variant
    Set wsS = Sheets(StaffSheet)
    Set tbl = Me.ListObjects(1)
    colArray = Split(cols, ",")
[I][COLOR=#006400]'clear old data and reset filter[/COLOR][/I]
    Optimise True
    On Error Resume Next
    AllData wsS
    wsS.Cells.Clear
    AllData Me
[I][COLOR=#006400]'set filter and set range to copy[/COLOR][/I]
    With tbl
        .Range.AutoFilter Field:=1, Criteria1:="<>completed", Operator:=xlAnd
        For Each col In colArray
            Set Rng = .ListColumns(CInt(col)).Range
            If uRng Is Nothing Then Set uRng = Rng Else Set uRng = Union(uRng, Rng)
        Next
    End With
[I][COLOR=#006400]'copy and paste[/COLOR][/I]
    uRng.SpecialCells(xlCellTypeVisible).Copy
    With wsS.Range("A1")
        .PasteSpecial (xlPasteFormats)
        .PasteSpecial (xlPasteValues)
        .CurrentRegion.EntireColumn.AutoFit
        .AutoFilter
    End With
[I][COLOR=#006400]'tidy up[/COLOR][/I]
    Application.CutCopyMode = False
    AllData Me
    wsS.Select
    Optimise False
End Sub

Private Sub AllData(ws As Worksheet)
    On Error Resume Next
    ws.ShowAllData
    On Error GoTo 0
End Sub

Private Sub Optimise(TrueFalse As Boolean)
    With Application
        .ScreenUpdating = Not TrueFalse
        .EnableEvents = Not TrueFalse
        .Calculation = xlCalculationAutomatic
        If TrueFalse Then .Calculation = xlCalculationManual
    End With
End Sub

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
Column
01
Column
02
Column
03
Column
04
Column
05
Column
06
Column
07
Column
08
Column
09
Column
10
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
bookedjob001
1,740.48​
16/02/2019​
84​
63​
45​
40​
47​
72​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
cancelledjob002
1,305.45​
19/02/2019​
88​
89​
87​
45​
66​
69​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
typingjob003
1,305.45​
19/02/2019​
30​
80​
64​
20​
93​
31​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
T&C Sentjob004
1,740.44​
15/02/2019​
32​
42​
87​
49​
35​
59​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
T&C acceptedjob005
1,305.45​
19/02/2019​
65​
33​
12​
70​
64​
51​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
invoice sentjob006
2,175.55​
15/02/2019​
35​
46​
84​
30​
17​
99​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
completedjob007
1,740.56​
18/02/2019​
32​
96​
84​
13​
30​
32​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
completedjob008
435.15​
19/02/2019​
68​
44​
86​
53​
11​
48​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
completedjob009
435.14​
18/02/2019​
60​
47​
54​
72​
85​
97​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
typingjob010
435.14​
18/02/2019​
45​
50​
53​
48​
30​
75​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
T&C Sentjob011
435.11​
15/02/2019​
73​
19​
13​
89​
71​
84​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
T&C acceptedjob012
2,175.55​
15/02/2019​
31​
87​
99​
64​
27​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
invoice sentjob013
2,175.70​
18/02/2019​
48​
36​
66​
61​
37​
83​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
invoice sentjob014
435.14​
18/02/2019​
61​
17​
57​
69​
29​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
invoice sentjob015
870.28​
18/02/2019​
11​
44​
46​
26​
65​
44​

<tbody>
</tbody>
Sheet: TableSheet

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
Column
02
Column
03
Column
04
Column
05
Column
06
Column
12
Column
13
Column
17
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
job001
1,740.48​
16/02/2019​
84​
63​
34​
27​
60​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
job002
1,305.45​
19/02/2019​
88​
89​
82​
23​
61​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
job003
1,305.45​
19/02/2019​
30​
80​
64​
91​
79​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
job004
1,740.44​
15/02/2019​
32​
42​
87​
31​
57​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
job005
1,305.45​
19/02/2019​
65​
33​
39​
86​
29​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
job006
2,175.55​
15/02/2019​
35​
46​
90​
57​
64​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
job010
435.14​
18/02/2019​
45​
50​
43​
59​
24​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
job011
435.11​
15/02/2019​
73​
19​
55​
48​
78​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
job012
2,175.55​
15/02/2019​
31​
87​
19​
78​
11​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
job013
2,175.70​
18/02/2019​
48​
36​
11​
91​
23​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
job014
435.14​
18/02/2019​
61​
17​
53​
99​
16​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
job015
870.28​
18/02/2019​
11​
44​
81​
67​
68​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​

<tbody>
</tbody>
Sheet: StaffSheet

<tbody>
</tbody>

Hi

I am getting Compile error: Invalid use of Me keyword

Am I copying this wrong?
 
Upvote 0
Me keyword - what is supposed to happen

In ThisWorkBook Module - returns workbook name
Code:
Sub Who_Am_I()
    MsgBox Me.Name
End Sub
In a sheet module - returns sheet name
Code:
Sub Who_Am_I()
    MsgBox Me.Name
End Sub
in a UserForm Module - clicking on the form returns UserForm name
Code:
Private Sub UserForm_Click()
    MsgBox Me.Name
End Sub
In a standard module - returns Invalid use of Me keyword
Code:
Sub Who_Am_I()
    MsgBox Me.Name
End Sub

But, I have also occasionally encountered your problem despite putting the code in a sheet module
- so check that you have put the code in the SHEET module of the sheet containing the table
- right-click on Sheet Tab and View Code
- if it is there then it is in the correct place
- check you have not also inadvertently pasted it into a standard module - delete if found!

Assuming the code is already in the correct place
- replace procedure CopyTable with code below and replace the green names with the correct sheet names

Code:
Sub CopyTable()
'variables
    Const TableSheet = "[COLOR=#006400]TableSheet[/COLOR]"
    Const StaffSheet = "[COLOR=#006400]StaffSheet[/COLOR]"
    Const cols = "2, 3, 4, 5, 6, 12, 13, 17"
    Dim tbl As ListObject, wsS As Worksheet, [COLOR=#ff0000]wsT As Worksheet,[/COLOR] uRng As Range, Rng As Range, col As Variant, colArray As Variant
    Set wsS = Sheets(StaffSheet)
    [COLOR=#ff0000]Set wsT = Sheets(TableSheet)[/COLOR]
    Set tbl = [COLOR=#ff0000]wsT[/COLOR].ListObjects(1)
    colArray = Split(cols, ",")
'clear old data and reset filter
    Optimise True
    On Error Resume Next
    AllData wsS
    wsS.Cells.Clear
    AllData [COLOR=#ff0000]wsT[/COLOR]
'set filter and set range to copy
    With tbl
        .Range.AutoFilter Field:=1, Criteria1:="<>completed", Operator:=xlAnd
        For Each col In colArray
            Set Rng = .ListColumns(CInt(col)).Range
            If uRng Is Nothing Then Set uRng = Rng Else Set uRng = Union(uRng, Rng)
        Next
    End With
'copy and paste
    uRng.SpecialCells(xlCellTypeVisible).Copy
    With wsS.Range("A1")
        .PasteSpecial (xlPasteFormats)
        .PasteSpecial (xlPasteValues)
        .CurrentRegion.EntireColumn.AutoFit
        .AutoFilter
    End With
'tidy up
    Application.CutCopyMode = False
    AllData [COLOR=#ff0000]wsT[/COLOR]
    wsS.Select
    Optimise False
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,202,957
Messages
6,052,771
Members
444,600
Latest member
margr

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