VBA Selection Macro

[amattey]

Hi,

I am writing a macro that will filter the data from one workbook sheet, and past it into another workbooks sheet. After the data is filter I want to copy just the filtered data from columns F,G,H as code below, but it does not work. Is there anything missing I should consider? Will much appreciate the help..

ActiveSheet.Range("$A$1:$AC$1654").AutoFilter Field:=1, Criteria1:="PCM"
ActiveSheet.Range("$A$1:$AC$1654").AutoFilter Field:=3, Criteria1:="N"
Columns("F:H").Offset(1, 0).Select
Selection.Copy

 

[wigi]

Like this:

Sub shortercode()

With Range("A1:AC1654")
    .AutoFilter Field:=1, Criteria1:="PCM"
    .AutoFilter Field:=3, Criteria1:="N"
    .Columns("F:H").Offset(1).SpecialCells(xlCellTypeVisible).Copy
End With

End Sub

 

[Weaver]

I think you're going to have a problem with this because of the

.columns("F:H").offset(1)

part, since it's trying to define a range that goes 1 row further than the total size of the sheet.

Try something like

Sub shortercode()
With Range("A1:AC1654")
    .AutoFilter Field:=1, Criteria1:="PCM"
    .AutoFilter Field:=3, Criteria1:="N"
    .Columns("F:H").Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
End With
End Sub

 

[wigi]

Weaver,

I do not know if you tested it, but can you please run:

Sub shortercode()

MsgBox Range("A1:AC1654").Columns("F:H").Offset(1).Address

End Sub

 

[amattey]

Wigi it does work. I had another question, I have 3 columns in one sheet, The first one is a date formated (mm,dd,yyyy), the second one is an output column, and the third one is the input. I wanted to create a formula that will select whatever is in the third column as the input and if column 1 is 1 year less or 2 years after a date that is located in another ceel (different sheet), to say something like "Do this year". Could you advise in how to get started, I wrote something but even I looking at it does not make much sense.

 

[wigi]

Please start a different topic.
The current topic is solved, and has a different subject than your new question.

 

[Weaver]

Weaver,

I do not know if you tested it, but can you please run:

Sub shortercode()
 
MsgBox Range("A1:AC1654").Columns("F:H").Offset(1).Address
 
End Sub

Apologies - my bad, the code I was testing with didn't initially have a range, so I was working with the entire column