How to copy filtered values of one column to another worksheet

[rhibrown]

I have this code below to filter on column AH, but I now want to copy all the filtered/visible cells in column M7 downwards and put them in worksheet 'Template' and paste them at the next blank cell in column J. Any ideas?

I need to do this for a couple of different columns as well.

 ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=34, Criteria1:="=*" & StrAP & "*"

 

[Marc L]

As the Range.Copy method works only with the visible cells so obviously you can use it with all the source range…​

​

 

[p45cal]

Since the Template sheet is protected there's not much we can do…

 

[p45cal]

You sent me a private message about my comment regarding protection above.
You're quite right, I don't need to unprotect that sheet to write to it.
There were several things that went wrong when I opened your file; missing references to Common Controls, which in turn fouled up simple vba functions such as Date, overflow errors because you've removed all data, so lines such as:
Template_row = Range("AN39", Range("AN39").End(xlDown)).Rows.Count
return large numbers that the Integer type variable Template_row can't hold, a missing sheet Completed which caused a subscript error, the code hanging needing me to force close Excel because it might have got itself into a long or never-ending loop.
All of this caused me to give up, whereas really I should have persevered and focused on the individual line of code.

You can do something like this:

    'Sheets("Workings").Activate 'not needed for this
    DestnRow = Sheets("Template").Cells(Rows.Count, "J").End(xlUp).Row + 1 'blank row in column J. Can use later for other column-copying so that they align properly
    Set WorkingsTable = Sheets("Workings").ListObjects("Table1") 'easier to set a variable to the list object for later code
    WorkingsTable.Range.AutoFilter Field:=34, Criteria1:="=*" & StrAP & "*"
    WorkingsTable.ListColumns("Invoice Amount (GST Excl)").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "J")
'   WorkingsTable.ListColumns("Another Column Name Here").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "D??") 'for further column copying.
'   WorkingsTable.ListColumns("Yet Another Column Name Here").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "F??")

or using the With…End With construct:

DestnRow = Sheets("Template").Cells(Rows.Count, "J").End(xlUp).Row + 1    'blank row in column J. Can use later for other column-copying so that they align properly
With Sheets("Workings").ListObjects("Table1")
  .Range.AutoFilter Field:=34, Criteria1:="=*" & StrAP & "*"
  .ListColumns("Invoice Amount (GST Excl)").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "J")
'  .ListColumns("Another Column Name Here").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "D??")    'for further column copying.
'  .ListColumns("Yet Another Column Name Here").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "F??")
End With

Marc L is quite right that range.copy works with visible cells and the above code should work as expected. However I have found sometimes, especially when you might Intersect or Union with range that it includes hidden cells too - I haven't worked out exactly when this happens so I often throw in the .specialcells(xlvisible) property too:

WorkingsTable.ListColumns("Invoice Amount (GST Excl)").DataBodyRange.SpecialCells(xlVisible).Copy Sheets("Template").Cells(DestnRow, "J")

Then I'm certain it'll do what I want.

 

[p45cal]

Incidentally, I saw this line:

Worksheets("Workings").Range("Row44:Row45").EntireRow.Hidden = True

and thought 'That won't work', but Excel didn't throw an error with it so I checked the rows and they had been hidden!
So I thought you must've created a named range… nope, couldn't find one.
Then I thought, is this a syntax I've never seen before?
I finally worked it out - I won't say here - it's silly!

You can use instead:

Worksheets("Workings").Rows("44:45").Hidden = True

which won't raise a coder's eyebrow.

 

[rhibrown]

You sent me a private message about my comment regarding protection above.
You're quite right, I don't need to unprotect that sheet to write to it.
There were several things that went wrong when I opened your file; missing references to Common Controls, which in turn fouled up simple vba functions such as Date, overflow errors because you've removed all data, so lines such as:
Template_row = Range("AN39", Range("AN39").End(xlDown)).Rows.Count
return large numbers that the Integer type variable Template_row can't hold, a missing sheet Completed which caused a subscript error, the code hanging needing me to force close Excel because it might have got itself into a long or never-ending loop.
All of this caused me to give up, whereas really I should have persevered and focused on the individual line of code.

You can do something like this:

    'Sheets("Workings").Activate 'not needed for this
    DestnRow = Sheets("Template").Cells(Rows.Count, "J").End(xlUp).Row + 1 'blank row in column J. Can use later for other column-copying so that they align properly
    Set WorkingsTable = Sheets("Workings").ListObjects("Table1") 'easier to set a variable to the list object for later code
    WorkingsTable.Range.AutoFilter Field:=34, Criteria1:="=*" & StrAP & "*"
    WorkingsTable.ListColumns("Invoice Amount (GST Excl)").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "J")
'   WorkingsTable.ListColumns("Another Column Name Here").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "D??") 'for further column copying.
'   WorkingsTable.ListColumns("Yet Another Column Name Here").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "F??")

or using the With…End With construct:

DestnRow = Sheets("Template").Cells(Rows.Count, "J").End(xlUp).Row + 1    'blank row in column J. Can use later for other column-copying so that they align properly
With Sheets("Workings").ListObjects("Table1")
  .Range.AutoFilter Field:=34, Criteria1:="=*" & StrAP & "*"
  .ListColumns("Invoice Amount (GST Excl)").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "J")
'  .ListColumns("Another Column Name Here").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "D??")    'for further column copying.
'  .ListColumns("Yet Another Column Name Here").DataBodyRange.Copy Sheets("Template").Cells(DestnRow, "F??")
End With

Marc L is quite right that range.copy works with visible cells and the above code should work as expected. However I have found sometimes, especially when you might Intersect or Union with range that it includes hidden cells too - I haven't worked out exactly when this happens so I often throw in the .specialcells(xlvisible) property too:

WorkingsTable.ListColumns("Invoice Amount (GST Excl)").DataBodyRange.SpecialCells(xlVisible).Copy Sheets("Template").Cells(DestnRow, "J")

Then I'm certain it'll do what I want.

Thank you so much for your explanation! It really helped - I greatly appreciate it!!!