Copy List Columns
 

I have a table in excel I would like to copy, the table has many records in
it. I have code that will filter the data to show only a few records. I need
to copy just the visible data from 3 consecutive columns and paste into
another workbook. Is there a way to copy all 3 columns at once?

Copy List Columns
 

After filtering, you can use SpecialCells to select only visible cells, then
copy & paste into the other workbook. This example creates a second workbook,
applies an autofilter in the the first workbook, selects only visible cells
in columns C-E, then copies & pastes those columns into the second workbook:

Sub Macro1()
Dim NewWB As Workbook, StartWB As Workbook
Set StartWB = ActiveWorkbook
Workbooks.Add
Set NewWB = ActiveWorkbook
StartWB.Activate
Sheets("Sheet1").Select

'Apply the filter here
Range("C1").Select
Selection.CurrentRegion.Select
Selection.AutoFilter Field:=1, Criteria1:="x"

Columns("C:E").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
NewWB.Activate
Sheets("Sheet1").Select
Range("C1").Select
ActiveSheet.Paste
'Free object variables when done
Set StartWB = Nothing
Set NewWB = Nothing
End Sub

Hope this helps,

Hutch

"Brad" wrote:

I have a table in excel I would like to copy, the table has many records in
it. I have code that will filter the data to show only a few records. I need
to copy just the visible data from 3 consecutive columns and paste into
another workbook. Is there a way to copy all 3 columns at once?

Copy List Columns
 

is there a way to do this without selecting the columns?

The reason I ask is because, this code is executing inside of a loop that
is heavily reliant on ActiveCell.offset(x , 1).

"Tom Hutchins" wrote:

After filtering, you can use SpecialCells to select only visible cells, then
copy & paste into the other workbook. This example creates a second workbook,
applies an autofilter in the the first workbook, selects only visible cells
in columns C-E, then copies & pastes those columns into the second workbook:

Sub Macro1()
Dim NewWB As Workbook, StartWB As Workbook
Set StartWB = ActiveWorkbook
Workbooks.Add
Set NewWB = ActiveWorkbook
StartWB.Activate
Sheets("Sheet1").Select

'Apply the filter here
Range("C1").Select
Selection.CurrentRegion.Select
Selection.AutoFilter Field:=1, Criteria1:="x"

Columns("C:E").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
NewWB.Activate
Sheets("Sheet1").Select
Range("C1").Select
ActiveSheet.Paste
'Free object variables when done
Set StartWB = Nothing
Set NewWB = Nothing
End Sub

Hope this helps,

Hutch

"Brad" wrote:

I have a table in excel I would like to copy, the table has many records in
it. I have code that will filter the data to show only a few records. I need
to copy just the visible data from 3 consecutive columns and paste into
another workbook. Is there a way to copy all 3 columns at once?

Copy List Columns
 

Here is an example

Sub Macro1()
Dim r As Range
Set r = Range("A1")
Application.ScreenUpdating = False
r.AutoFilter
r.AutoFilter Field:=1, Criteria1:="Apples"
r.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
r.AutoFilter
End Sub