Copy column width from one table to another then filter VBA

[Asher]

I keep getting errors and I tried to just copy this code from a part of a recorded macro and input it into my existing subroutines but it's not working...

Here was the first set of code:

Code: Select all

    Sheets("Sheet 1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet 2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Sheet 2_Table[[#Headers],[Column 3]]").Select
    ActiveSheet.ListObjects("Sheet 2_Table").Range.AutoFilter Field:=8, Criteria1:= _
        "=*Criteria Text*", Operator:=xlAnd
    ActiveSheet.ListObjects("Sheet 2_Table").Range.AutoFilter Field:=3, Criteria1:= _
        "Criteria Text"

But then I thought I should define the initial selections since this chunk of code is being plunked in the middle of my larger code so that is like this:

Code: Select all

    Sheets("Sheet 1").Select
    Range("Sheet 1_Table").Select
    Selection.Copy
    Sheets("Sheet 2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Sheet 2_Table[[#Headers],[Column 3]]").Select
    ActiveSheet.ListObjects("Sheet 2_Table").Range.AutoFilter Field:=8, Criteria1:= _
        "=*Criteria Text*", Operator:=xlAnd
    ActiveSheet.ListObjects("Sheet 2_Table").Range.AutoFilter Field:=3, Criteria1:= _
        "Criteria Text"

But then it gave me an error on the SpecialCells selection thing so i changed it to this:

Code: Select all

    Sheets("Sheet 1").Select
    Range("Sheet 1_Table").Select
    Selection.Copy
    Sheets("Sheet 2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Sheet 2_Table[[#Headers],[Column 3]]").Select
    ActiveSheet.ListObjects("Sheet 2_Table").Range.AutoFilter Field:=8, Criteria1:= _
        "=*Criteria Text*", Operator:=xlAnd
    ActiveSheet.ListObjects("Sheet 2_Table").Range.AutoFilter Field:=3, Criteria1:= _
        "Criteria Text"

And I get an error on the PasteSpecial ... ugh

So maybe I need a sub with a with statement for just the pasting column widths then do the filter separately (Because I have to do this for 6 tables)?

[HansV]

You want to copy the column widths of the range Sheet 1_Table on Sheet 1, but from the code it's not clear where you want to paste them - it depends on what happens to be selected on Sheet 2.

[Asher]

I'm selecting the Sheet 2_Table to have the columns pasted to.
This is how the recorded macro did the selection and paste:

Code: Select all

Sheets("Sheet 2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

[HansV]

Does this do what you want?

Code: Select all

    Sheets("Sheet 1").Range("Sheet 1_Table").Copy
    With Sheets("Sheet 2").Range("Sheet 2_Table")
        .PasteSpecial Paste:=xlPasteColumnWidths
        .AutoFilter Field:=8, Criteria1:="=*Criteria Text*"
        .AutoFilter Field:=3, Criteria1:="Criteria Text"
    End With

[Asher]

Why yes it does do what I needed! Thanks again Hans.

And now I think I'm starting to get the hang of what you mean when you say you don' have to .Select everything.

Like when you did:

Code: Select all

Sheets("Sheet 1").Range("Sheet 1_Table").Copy

It wasn't necessary to do the:

Code: Select all

Sheets("Sheet 1").Select

[HansV]

Yes, indeed. When you record a macro, Excel will usually include selecting cells in the code. In many situations, you can edit the recorded code afterwards to remove the selecting.

Here is a simple example. Assume that Sheet1 is the active sheet.

Sheets("Sheet2").Select
Range("A1:C5").Select
Selection.NumberFormat = "$#,##0.00#
Sheets("Sheet1").Select

This can be shortened to

Sheets("Sheet2").Range("A1:C5").NumberFormat = "$#,##0.00#

This is not only shorter, but also more efficient, because switching between sheets and selecting cells causes the display to be updated.

[Asher]

This can be shortened to

Sheets("Sheet2").Range("A1:C5").NumberFormat = "$#,##0.00#

This is not only shorter, but also more efficient, because switching between sheets and selecting cells causes the display to be updated.

And so if I had more than one command for the same sheet I can just add a With statement and put a . before the commands...?

So instead of:

Code: Select all

Sheets("Sheet 1").Select
ActiveSheet.Range("Sheet 1_Table[#All]").WrapText = True
ActiveSheet.Range("Sheet 1_Table[#All]").RowHeight = 60

I could do:

Code: Select all

Sheets("Sheet 1").Range("Sheet 1_Table[#All]").WrapText = True
Sheets("Sheet 1").Range("Sheet 1_Table[#All]").RowHeight = 60

Or, better yet, I can do:

Code: Select all

    With Sheets("Sheet 1").Range("Sheet 1_Table[#All]")
        .WrapText = True
        .RowHeight = 60
    End With

[HansV]

Yep, you got it!