How to select range of Active Window

I want to say thank you Zurn, Bob, Ivan & Rick for responded to my row
variable need. I'm getting there, but now another problem surfaced after
adding the variable row code.

What I am trying to achieve is as follows:
1. I first open the PAS DATA workbook with Excel.
2. I then run the macros to (1) Open a new workbook based on a template
named CSR_Report.xlt, which creates a workbook named CSR_Report1.
3. I then want to select/copy specific columns and all rows starting from A2
from the PAS Data workbook.
4. I then want to paste the PAS Data into the workbook CSR_Report1 at
column/row A2. This is where my next problem begins. The code Activates the
window for the CSR_Report1, but the next line of code that is to select the
range on that worksheet produces a "Runtime error '1004'. Below is the
current code. Can anyone help with the next problem?
************
Sub CSRData()
'
' Macro recorded 4/25/2006'
Workbooks.Add Template:= _
"C:\Documents and Settings\Owner\My documents\PAS\CSR_PAS_Report.xlt"
End Sub
***************************************
Sub CopyPAS()
'
' Macro recorded 4/25/2006

Windows("PAS Data.xls").Activate
Set rng1 = Range("a2:U2")
Set rng1 = Range(rng1, rng1.End(xlDown))
Selection.Copy
Windows("CSR_PAS_Report1").Activate
Range("A2").Select 'this is where the code fails
ActiveSheet.Paste
Selection.AutoFilter Field:=19, Criteria1:=">=0", Operator:=xlAnd, _
Criteria2:="<=30"
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Select
Selection.EntireColumn.Hidden = True

End Sub

TIA
Jan

It seems to me that it "falls down" here because although you are activating the window you are not selecting which sheet it should select the range on..........of course i may be wrong on this as i am just a fledgling at programming myself!

Regards,
Simon

Windows("PAS Data.xls").Activate
Set rng1 = Range("a2:U2")
Set rng1 = Range(rng1, rng1.End(xlDown))
Selection.Copy
Windows("CSR_PAS_Report1").Activate
Sheets("YOUR SHEET NAME").select'Try adding this line!
Range("A2").Select 'this is where the code fails
ActiveSheet.Paste
Selection.AutoFilter Field:=19, Criteria1:=">=0", Operator:=xlAnd, _
Criteria2:="<=30"
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
Selection.EntireColumn.Hidden = True

Jan

Also your code does not tell what range to select before you copy.
This is ok if you manually select the range before you run the macro but not if you want to copy the range that equals rng1

Simon,
I haven't actually tried your suggestion because I wonder if I will have a
problem. Both workbooks have the same worksheet name; per company request.
Is there some code that would "select" the specific workbook by name?

TIA
Jan

"Simon Lloyd" wrote:

>
> It seems to me that it "falls down" here because although you are
> activating the window you are not selecting which sheet it should
> select the range on..........of course i may be wrong on this as i am
> just a fledgling at programming myself!
>
> Regards,
> Simon
>
> Windows("PAS Data.xls").Activate
> Set rng1 = Range("a2:U2")
> Set rng1 = Range(rng1, rng1.End(xlDown))
> Selection.Copy
> Windows("CSR_PAS_Report1").Activate
> Sheets("YOUR SHEET NAME").select'Try adding this line!
> Range("A2").Select 'this is where the code fails
> ActiveSheet.Paste
> Selection.AutoFilter Field:=19, Criteria1:=">=0", Operator:=xlAnd, _
> Criteria2:="<=30"
> Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> Selection.EntireColumn.Hidden = True
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=537064
>
>

To test, I changed the worksheet name to Projects and added the following
line above "Range ("A2").Select".

Worksheets("Projects").Select
Range("A2").select. "Code still fails at this point & returns Runtime error
'1004'"

Any other suggestions?

TIA
Jan

"mudraker" wrote:

>
> Jan
>
> Also your code does not tell what range to select before you copy.
> This is ok if you manually select the range before you run the macro
> but not if you want to copy the range that equals rng1
>
>
> --
> mudraker
> ------------------------------------------------------------------------
> mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
> View this thread: http://www.excelforum.com/showthread...hreadid=537064
>
>

Hi Jan,

Sub CopyPAS()
'
' Macro recorded 4/25/2006

Windows("PAS Data.xls").Activate
Set rng1 = Range("a2:U2")
Set rng1 = Range(rng1, rng1.End(xlDown))
Selection.Copy
' Windows("CSR_PAS_Report1").Activate
workbooks("CSR_PAS_Report").worksheets("CSR_PAS_Report1").activate
Range("A2").Select 'this is where the code fails
'should not fail now
ActiveSheet.Paste
Selection.AutoFilter Field:=19, Criteria1:=">=0", Operator:=xlAnd,
_
Criteria2:="<=30"
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Select
Selection.EntireColumn.Hidden = True

End Sub

Change names to appropriate, if wrong. Please note that I commented one
line out of the code (' Windows("CSR_PAS_Report1").Activate). Please
also note that the workbooks must be already saved (this will fail with
Book1 that Excel creates as new workbook while opening the application,
for example).

Regards,
Ivan

Regards,
Ivan

Hello Again Jan, Its Rick, I took your code and rewrote it to what I
think your trying to accomplish. That is copy a range from one workbook to
another,then us a autofilter. I used some the same code I sent you last
night to select the complete range. So you don't worry finding the lastrow
of the range, it done for you in the code. Make sure both works books are
open before you run this code. If not you'll get error with the first couple
lines of code. Also make sure to change the worksheet names in the code to
match the worksheets that your coping from an to. If you have any question
drop me a line here or email me at [email protected]

Enjoy, Rick (FBKS,AK)


Option Explicit

Sub CopyPAS()
Dim wbk1 As Workbook, wbk2 As Workbook
Dim wsht1 As Worksheet, wsht2 As Worksheet
Dim rng1 As Range

Set wbk1 = Workbooks("PAS Data.xls")
Set wbk2 = Workbooks("CSR_PAS_Report1.xls")
Set wsht1 = wbk1.Worksheets("Sheet1") '' <-Change to sheet name for ("PAS
Data.xls")
Set wsht2 = wbk2.Worksheets("Sheet1") '' <-Change to sheet name for
("CSR_PAS_REPORT1.xls")

wsht1.Activate '' <"PAS Data.xls">
Set rng1 = wsht1.Range(Range("A2").End(xlDown),
Range("A2").End(xlToRight))
rng1.Copy Destination:=wsht2.Range("A2")

wsht2.Activate '' <"CSR_PAS_Report1.xls">
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden = True
Range("A2").End(xlToRight).AutoFilter Field:=19, _
Criteria1:=">=0", Operator:=xlAnd, _
Criteria2:="<=30"

End Sub



"Jan" <[email protected]> wrote in message
news:[email protected]...
> Simon,
> I haven't actually tried your suggestion because I wonder if I will have a
> problem. Both workbooks have the same worksheet name; per company
request.
> Is there some code that would "select" the specific workbook by name?
>
> TIA
> Jan
>
> "Simon Lloyd" wrote:
>
> >
> > It seems to me that it "falls down" here because although you are
> > activating the window you are not selecting which sheet it should
> > select the range on..........of course i may be wrong on this as i am
> > just a fledgling at programming myself!
> >
> > Regards,
> > Simon
> >
> > Windows("PAS Data.xls").Activate
> > Set rng1 = Range("a2:U2")
> > Set rng1 = Range(rng1, rng1.End(xlDown))
> > Selection.Copy
> > Windows("CSR_PAS_Report1").Activate
> > Sheets("YOUR SHEET NAME").select'Try adding this line!
> > Range("A2").Select 'this is where the code fails
> > ActiveSheet.Paste
> > Selection.AutoFilter Field:=19, Criteria1:=">=0", Operator:=xlAnd, _
> > Criteria2:="<=30"
> > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> > Selection.EntireColumn.Hidden = True
> >
> >
> > --
> > Simon Lloyd
> > ------------------------------------------------------------------------
> > Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> > View this thread:
http://www.excelforum.com/showthread...hreadid=537064
> >
> >

Rick,

I have enter your code exactly as provided, except I changed the workbook
and worksheet names to what they are. I have also insured that the template
that is opened has been saved first before running the code.
Problem: The PAS Data has information in every cell in column A; but not all
cells in the rows have data. So row/column A2 has data in the first 4 cells
to the right. Consequently with the selection routine, it is only picking up
the first 4 columns and copying them to workbook 2 (CSR_Report). The range
needs to select columns A thru S.


The autofilter code is being set on the PAS Data workbook, when it should be
set on the CSR_report. I don't know why that would be since the CSR_Report
is the activte window.

Thanks for your help.
Jan



"Rick Hansen" wrote:

> Hello Again Jan, Its Rick, I took your code and rewrote it to what I
> think your trying to accomplish. That is copy a range from one workbook to
> another,then us a autofilter. I used some the same code I sent you last
> night to select the complete range. So you don't worry finding the lastrow
> of the range, it done for you in the code. Make sure both works books are
> open before you run this code. If not you'll get error with the first couple
> lines of code. Also make sure to change the worksheet names in the code to
> match the worksheets that your coping from an to. If you have any question
> drop me a line here or email me at [email protected]
>
> Enjoy, Rick (FBKS,AK)
>
>
> Option Explicit
>
> Sub CopyPAS()
> Dim wbk1 As Workbook, wbk2 As Workbook
> Dim wsht1 As Worksheet, wsht2 As Worksheet
> Dim rng1 As Range
>
> Set wbk1 = Workbooks("PAS Data.xls")
> Set wbk2 = Workbooks("CSR_PAS_Report1.xls")
> Set wsht1 = wbk1.Worksheets("Sheet1") '' <-Change to sheet name for ("PAS
> Data.xls")
> Set wsht2 = wbk2.Worksheets("Sheet1") '' <-Change to sheet name for
> ("CSR_PAS_REPORT1.xls")
>
> wsht1.Activate '' <"PAS Data.xls">
> Set rng1 = wsht1.Range(Range("A2").End(xlDown),
> Range("A2").End(xlToRight))
> rng1.Copy Destination:=wsht2.Range("A2")
>
> wsht2.Activate '' <"CSR_PAS_Report1.xls">
> Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden = True
> Range("A2").End(xlToRight).AutoFilter Field:=19, _
> Criteria1:=">=0", Operator:=xlAnd, _
> Criteria2:="<=30"
>
> End Sub
>
>
>
> "Jan" <[email protected]> wrote in message
> news:[email protected]...
> > Simon,
> > I haven't actually tried your suggestion because I wonder if I will have a
> > problem. Both workbooks have the same worksheet name; per company
> request.
> > Is there some code that would "select" the specific workbook by name?
> >
> > TIA
> > Jan
> >
> > "Simon Lloyd" wrote:
> >
> > >
> > > It seems to me that it "falls down" here because although you are
> > > activating the window you are not selecting which sheet it should
> > > select the range on..........of course i may be wrong on this as i am
> > > just a fledgling at programming myself!
> > >
> > > Regards,
> > > Simon
> > >
> > > Windows("PAS Data.xls").Activate
> > > Set rng1 = Range("a2:U2")
> > > Set rng1 = Range(rng1, rng1.End(xlDown))
> > > Selection.Copy
> > > Windows("CSR_PAS_Report1").Activate
> > > Sheets("YOUR SHEET NAME").select'Try adding this line!
> > > Range("A2").Select 'this is where the code fails
> > > ActiveSheet.Paste
> > > Selection.AutoFilter Field:=19, Criteria1:=">=0", Operator:=xlAnd, _
> > > Criteria2:="<=30"
> > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> > > Selection.EntireColumn.Hidden = True
> > >
> > >
> > > --
> > > Simon Lloyd
> > > ------------------------------------------------------------------------
> > > Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=537064
> > >
> > >
>
>
>

Ivan,

I have also enter your code exactly as provided, except I change the
workbook and worksheet names to what they actually are. I have also insured
that both workbooks are open.

The code fails at Workbooks("CSR_Report").Worksheets("Projects").Activate.
I get a Runtime error 9...subscript out of range.

Thanks for all your help.
Jan

"Ivan Raiminius" wrote:

> Hi Jan,
>
> Sub CopyPAS()
> '
> ' Macro recorded 4/25/2006
>
> Windows("PAS Data.xls").Activate
> Set rng1 = Range("a2:U2")
> Set rng1 = Range(rng1, rng1.End(xlDown))
> Selection.Copy
> ' Windows("CSR_PAS_Report1").Activate
> workbooks("CSR_PAS_Report").worksheets("CSR_PAS_Report1").activate
> Range("A2").Select 'this is where the code fails
> 'should not fail now
> ActiveSheet.Paste
> Selection.AutoFilter Field:=19, Criteria1:=">=0", Operator:=xlAnd,
> _
> Criteria2:="<=30"
> Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Select
> Selection.EntireColumn.Hidden = True
>
> End Sub
>
> Change names to appropriate, if wrong. Please note that I commented one
> line out of the code (' Windows("CSR_PAS_Report1").Activate). Please
> also note that the workbooks must be already saved (this will fail with
> Book1 that Excel creates as new workbook while opening the application,
> for example).
>
> Regards,
> Ivan
>
> Regards,
> Ivan
>
>

Good Morning Jan,
I've made changes to the code, to select columns A thru S, and down to
the lastrow. (See Code). I have tested the code a seem to run ok. I also
changed the workbook name of "CSR_PAS_Report1.xlt " to "CSR_PAS_Report1".
Now the CSR_PAS_Report well activate with the autofilter. I believe this
help you meet your goal you were seeking for this of many execl projects.

Enjoy, Rick (Fbks,AK)


Option Explicit

Sub CopyPAS()
Dim wbk1 As Workbook, wbk2 As Workbook
Dim wsht1 As Worksheet, wsht2 As Worksheet
Dim rng1 As Range
Dim LastRow As Long

Set wbk1 = Workbooks("PAS Data.xls")
Set wbk2 = Workbooks("CSR_PAS_Report1")
Set wsht1 = wbk1.Worksheets("Sheet1") ' <-Change to sheet name for
("PASData.xls ")
Set wsht2 = wbk2.Worksheets("Sheet1") ' <-Change to sheet name for
("CSR_PAS_REPORT1")

wsht1.Activate ' <"PAS Data.xls">
LastRow = wsht1.Range("A2").End(xlDown).Row
Set rng1 = wsht1.Range("A2:S" & LastRow) ' select col's A thru S thru
lastrow
rng1.Copy Destination:=wsht2.Range("A2")

wsht2.Activate ' <"CSR_PAS_Report1">
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden = True
Range("A2").End(xlToRight).AutoFilter Field:=19, _
Criteria1:=">=0", Operator:=xlAnd, _
Criteria2:="<=30"

End Sub


"Jan" <[email protected]> wrote in message
news:[email protected]...
> Rick,
>
> I have enter your code exactly as provided, except I changed the workbook
> and worksheet names to what they are. I have also insured that the
template
> that is opened has been saved first before running the code.
> Problem: The PAS Data has information in every cell in column A; but not
all
> cells in the rows have data. So row/column A2 has data in the first 4
cells
> to the right. Consequently with the selection routine, it is only picking
up
> the first 4 columns and copying them to workbook 2 (CSR_Report). The range
> needs to select columns A thru S.
>
>
> The autofilter code is being set on the PAS Data workbook, when it should
be
> set on the CSR_report. I don't know why that would be since the
CSR_Report
> is the activte window.
>
> Thanks for your help.
> Jan
>
>
>
> "Rick Hansen" wrote:
>
> > Hello Again Jan, Its Rick, I took your code and rewrote it to what I
> > think your trying to accomplish. That is copy a range from one workbook
to
> > another,then us a autofilter. I used some the same code I sent you last
> > night to select the complete range. So you don't worry finding the
lastrow
> > of the range, it done for you in the code. Make sure both works books
are
> > open before you run this code. If not you'll get error with the first
couple
> > lines of code. Also make sure to change the worksheet names in the code
to
> > match the worksheets that your coping from an to. If you have any
question
> > drop me a line here or email me at [email protected]
> >
> > Enjoy, Rick (FBKS,AK)
> >
> >
> > Option Explicit
> >
> > Sub CopyPAS()
> > Dim wbk1 As Workbook, wbk2 As Workbook
> > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > Dim rng1 As Range
> >
> > Set wbk1 = Workbooks("PAS Data.xls")
> > Set wbk2 = Workbooks("CSR_PAS_Report1.xls")
> > Set wsht1 = wbk1.Worksheets("Sheet1") '' <-Change to sheet name for
("PAS
> > Data.xls")
> > Set wsht2 = wbk2.Worksheets("Sheet1") '' <-Change to sheet name for
> > ("CSR_PAS_REPORT1.xls")
> >
> > wsht1.Activate '' <"PAS Data.xls">
> > Set rng1 = wsht1.Range(Range("A2").End(xlDown),
> > Range("A2").End(xlToRight))
> > rng1.Copy Destination:=wsht2.Range("A2")
> >
> > wsht2.Activate '' <"CSR_PAS_Report1.xls">
> > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
True
> > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > Criteria1:=">=0", Operator:=xlAnd, _
> > Criteria2:="<=30"
> >
> > End Sub
> >
> >
> >
> > "Jan" <[email protected]> wrote in message
> > news:[email protected]...
> > > Simon,
> > > I haven't actually tried your suggestion because I wonder if I will
have a
> > > problem. Both workbooks have the same worksheet name; per company
> > request.
> > > Is there some code that would "select" the specific workbook by name?
> > >
> > > TIA
> > > Jan
> > >
> > > "Simon Lloyd" wrote:
> > >
> > > >
> > > > It seems to me that it "falls down" here because although you are
> > > > activating the window you are not selecting which sheet it should
> > > > select the range on..........of course i may be wrong on this as i
am
> > > > just a fledgling at programming myself!
> > > >
> > > > Regards,
> > > > Simon
> > > >
> > > > Windows("PAS Data.xls").Activate
> > > > Set rng1 = Range("a2:U2")
> > > > Set rng1 = Range(rng1, rng1.End(xlDown))
> > > > Selection.Copy
> > > > Windows("CSR_PAS_Report1").Activate
> > > > Sheets("YOUR SHEET NAME").select'Try adding this line!
> > > > Range("A2").Select 'this is where the code fails
> > > > ActiveSheet.Paste
> > > > Selection.AutoFilter Field:=19, Criteria1:=">=0", Operator:=xlAnd, _
> > > > Criteria2:="<=30"
> > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> > > > Selection.EntireColumn.Hidden = True
> > > >
> > > >
> > > > --
> > > > Simon Lloyd
> > >
> ------------------------------------------------------------------------
> > > > Simon Lloyd's Profile:
> > http://www.excelforum.com/member.php...fo&userid=6708
> > > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=537064
> > > >
> > > >
> >
> >
> >

Hi Rick,

I'm almost there. The data from PAS Data sheet is now copied to the CSR_PAS
Report. However, the autofilter and the columns hidden are happening on the
PAS data sheet. They should happen on the CSR_PAS report. The code reads to
activate the CSR_PAS report, but how is it being selected to apply the
autofilter and hide the specific columns?

I can't thank you enough for your help with this.

Jan

"Rick Hansen" wrote:

> Good Morning Jan,
> I've made changes to the code, to select columns A thru S, and down to
> the lastrow. (See Code). I have tested the code a seem to run ok. I also
> changed the workbook name of "CSR_PAS_Report1.xlt " to "CSR_PAS_Report1".
> Now the CSR_PAS_Report well activate with the autofilter. I believe this
> help you meet your goal you were seeking for this of many execl projects.
>
> Enjoy, Rick (Fbks,AK)
>
>
> Option Explicit
>
> Sub CopyPAS()
> Dim wbk1 As Workbook, wbk2 As Workbook
> Dim wsht1 As Worksheet, wsht2 As Worksheet
> Dim rng1 As Range
> Dim LastRow As Long
>
> Set wbk1 = Workbooks("PAS Data.xls")
> Set wbk2 = Workbooks("CSR_PAS_Report1")
> Set wsht1 = wbk1.Worksheets("Sheet1") ' <-Change to sheet name for
> ("PASData.xls ")
> Set wsht2 = wbk2.Worksheets("Sheet1") ' <-Change to sheet name for
> ("CSR_PAS_REPORT1")
>
> wsht1.Activate ' <"PAS Data.xls">
> LastRow = wsht1.Range("A2").End(xlDown).Row
> Set rng1 = wsht1.Range("A2:S" & LastRow) ' select col's A thru S thru
> lastrow
> rng1.Copy Destination:=wsht2.Range("A2")
>
> wsht2.Activate ' <"CSR_PAS_Report1">
> Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden = True
> Range("A2").End(xlToRight).AutoFilter Field:=19, _
> Criteria1:=">=0", Operator:=xlAnd, _
> Criteria2:="<=30"
>
> End Sub
>
>
> "Jan" <[email protected]> wrote in message
> news:[email protected]...
> > Rick,
> >
> > I have enter your code exactly as provided, except I changed the workbook
> > and worksheet names to what they are. I have also insured that the
> template
> > that is opened has been saved first before running the code.
> > Problem: The PAS Data has information in every cell in column A; but not
> all
> > cells in the rows have data. So row/column A2 has data in the first 4
> cells
> > to the right. Consequently with the selection routine, it is only picking
> up
> > the first 4 columns and copying them to workbook 2 (CSR_Report). The range
> > needs to select columns A thru S.
> >
> >
> > The autofilter code is being set on the PAS Data workbook, when it should
> be
> > set on the CSR_report. I don't know why that would be since the
> CSR_Report
> > is the activte window.
> >
> > Thanks for your help.
> > Jan
> >
> >
> >
> > "Rick Hansen" wrote:
> >
> > > Hello Again Jan, Its Rick, I took your code and rewrote it to what I
> > > think your trying to accomplish. That is copy a range from one workbook
> to
> > > another,then us a autofilter. I used some the same code I sent you last
> > > night to select the complete range. So you don't worry finding the
> lastrow
> > > of the range, it done for you in the code. Make sure both works books
> are
> > > open before you run this code. If not you'll get error with the first
> couple
> > > lines of code. Also make sure to change the worksheet names in the code
> to
> > > match the worksheets that your coping from an to. If you have any
> question
> > > drop me a line here or email me at [email protected]
> > >
> > > Enjoy, Rick (FBKS,AK)
> > >
> > >
> > > Option Explicit
> > >
> > > Sub CopyPAS()
> > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > Dim rng1 As Range
> > >
> > > Set wbk1 = Workbooks("PAS Data.xls")
> > > Set wbk2 = Workbooks("CSR_PAS_Report1.xls")
> > > Set wsht1 = wbk1.Worksheets("Sheet1") '' <-Change to sheet name for
> ("PAS
> > > Data.xls")
> > > Set wsht2 = wbk2.Worksheets("Sheet1") '' <-Change to sheet name for
> > > ("CSR_PAS_REPORT1.xls")
> > >
> > > wsht1.Activate '' <"PAS Data.xls">
> > > Set rng1 = wsht1.Range(Range("A2").End(xlDown),
> > > Range("A2").End(xlToRight))
> > > rng1.Copy Destination:=wsht2.Range("A2")
> > >
> > > wsht2.Activate '' <"CSR_PAS_Report1.xls">
> > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
> True
> > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > Criteria1:=">=0", Operator:=xlAnd, _
> > > Criteria2:="<=30"
> > >
> > > End Sub
> > >
> > >
> > >
> > > "Jan" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Simon,
> > > > I haven't actually tried your suggestion because I wonder if I will
> have a
> > > > problem. Both workbooks have the same worksheet name; per company
> > > request.
> > > > Is there some code that would "select" the specific workbook by name?
> > > >
> > > > TIA
> > > > Jan
> > > >
> > > > "Simon Lloyd" wrote:
> > > >
> > > > >
> > > > > It seems to me that it "falls down" here because although you are
> > > > > activating the window you are not selecting which sheet it should
> > > > > select the range on..........of course i may be wrong on this as i
> am
> > > > > just a fledgling at programming myself!
> > > > >
> > > > > Regards,
> > > > > Simon
> > > > >
> > > > > Windows("PAS Data.xls").Activate
> > > > > Set rng1 = Range("a2:U2")
> > > > > Set rng1 = Range(rng1, rng1.End(xlDown))
> > > > > Selection.Copy
> > > > > Windows("CSR_PAS_Report1").Activate
> > > > > Sheets("YOUR SHEET NAME").select'Try adding this line!
> > > > > Range("A2").Select 'this is where the code fails
> > > > > ActiveSheet.Paste
> > > > > Selection.AutoFilter Field:=19, Criteria1:=">=0", Operator:=xlAnd, _
> > > > > Criteria2:="<=30"
> > > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> > > > > Selection.EntireColumn.Hidden = True
> > > > >
> > > > >
> > > > > --
> > > > > Simon Lloyd
> > > >
> > ------------------------------------------------------------------------
> > > > > Simon Lloyd's Profile:
> > > http://www.excelforum.com/member.php...fo&userid=6708
> > > > > View this thread:
> > > http://www.excelforum.com/showthread...hreadid=537064
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Hi Jan, I just run my code again and everything worked great. Make sure
there is no type O's in your code. The only thing I can see possible is
wrong is type O in the line of code after Copy, ie it should read:
wsht2.Activate ' <"CSR_PAS_Report1">.
If this line isn't correct , it it won't activate " CSR_PAS report,
causing the autofilter to execute on the PAS Data sheet. If these doesn''t
help, maybe you could email your complete spread so I can look at your code.
Let how it turns out. I got run, I check back late tonight.. One other
thing. Where is marco being execute from, a different work book, or from,
the PAS Data sheet ?

keep trying, Rick


"Jan" <[email protected]> wrote in message
news:[email protected]...
> Hi Rick,
>
> I'm almost there. The data from PAS Data sheet is now copied to the
CSR_PAS
> Report. However, the autofilter and the columns hidden are happening on
the
> PAS data sheet. They should happen on the CSR_PAS report. The code reads
to
> activate the CSR_PAS report, but how is it being selected to apply the
> autofilter and hide the specific columns?
>
> I can't thank you enough for your help with this.
>
> Jan
>
> "Rick Hansen" wrote:
>
> > Good Morning Jan,
> > I've made changes to the code, to select columns A thru S, and down
to
> > the lastrow. (See Code). I have tested the code a seem to run ok. I also
> > changed the workbook name of "CSR_PAS_Report1.xlt " to
"CSR_PAS_Report1".
> > Now the CSR_PAS_Report well activate with the autofilter. I believe
this
> > help you meet your goal you were seeking for this of many execl
projects.
> >
> > Enjoy, Rick (Fbks,AK)
> >
> >
> > Option Explicit
> >
> > Sub CopyPAS()
> > Dim wbk1 As Workbook, wbk2 As Workbook
> > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > Dim rng1 As Range
> > Dim LastRow As Long
> >
> > Set wbk1 = Workbooks("PAS Data.xls")
> > Set wbk2 = Workbooks("CSR_PAS_Report1")
> > Set wsht1 = wbk1.Worksheets("Sheet1") ' <-Change to sheet name for
> > ("PASData.xls ")
> > Set wsht2 = wbk2.Worksheets("Sheet1") ' <-Change to sheet name for
> > ("CSR_PAS_REPORT1")
> >
> > wsht1.Activate ' <"PAS Data.xls">
> > LastRow = wsht1.Range("A2").End(xlDown).Row
> > Set rng1 = wsht1.Range("A2:S" & LastRow) ' select col's A thru S
thru
> > lastrow
> > rng1.Copy Destination:=wsht2.Range("A2")
> >
> > wsht2.Activate ' <"CSR_PAS_Report1">
> > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
True
> > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > Criteria1:=">=0", Operator:=xlAnd, _
> > Criteria2:="<=30"
> >
> > End Sub
> >
> >
> > "Jan" <[email protected]> wrote in message
> > news:[email protected]...
> > > Rick,
> > >
> > > I have enter your code exactly as provided, except I changed the
workbook
> > > and worksheet names to what they are. I have also insured that the
> > template
> > > that is opened has been saved first before running the code.
> > > Problem: The PAS Data has information in every cell in column A; but
not
> > all
> > > cells in the rows have data. So row/column A2 has data in the first 4
> > cells
> > > to the right. Consequently with the selection routine, it is only
picking
> > up
> > > the first 4 columns and copying them to workbook 2 (CSR_Report). The
range
> > > needs to select columns A thru S.
> > >
> > >
> > > The autofilter code is being set on the PAS Data workbook, when it
should
> > be
> > > set on the CSR_report. I don't know why that would be since the
> > CSR_Report
> > > is the activte window.
> > >
> > > Thanks for your help.
> > > Jan
> > >
> > >
> > >
> > > "Rick Hansen" wrote:
> > >
> > > > Hello Again Jan, Its Rick, I took your code and rewrote it to
what I
> > > > think your trying to accomplish. That is copy a range from one
workbook
> > to
> > > > another,then us a autofilter. I used some the same code I sent you
last
> > > > night to select the complete range. So you don't worry finding the
> > lastrow
> > > > of the range, it done for you in the code. Make sure both works
books
> > are
> > > > open before you run this code. If not you'll get error with the
first
> > couple
> > > > lines of code. Also make sure to change the worksheet names in the
code
> > to
> > > > match the worksheets that your coping from an to. If you have any
> > question
> > > > drop me a line here or email me at [email protected]
> > > >
> > > > Enjoy, Rick (FBKS,AK)
> > > >
> > > >
> > > > Option Explicit
> > > >
> > > > Sub CopyPAS()
> > > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > > Dim rng1 As Range
> > > >
> > > > Set wbk1 = Workbooks("PAS Data.xls")
> > > > Set wbk2 = Workbooks("CSR_PAS_Report1.xls")
> > > > Set wsht1 = wbk1.Worksheets("Sheet1") '' <-Change to sheet name for
> > ("PAS
> > > > Data.xls")
> > > > Set wsht2 = wbk2.Worksheets("Sheet1") '' <-Change to sheet name for
> > > > ("CSR_PAS_REPORT1.xls")
> > > >
> > > > wsht1.Activate '' <"PAS Data.xls">
> > > > Set rng1 = wsht1.Range(Range("A2").End(xlDown),
> > > > Range("A2").End(xlToRight))
> > > > rng1.Copy Destination:=wsht2.Range("A2")
> > > >
> > > > wsht2.Activate '' <"CSR_PAS_Report1.xls">
> > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden
=
> > True
> > > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > > Criteria1:=">=0", Operator:=xlAnd, _
> > > > Criteria2:="<=30"
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > > "Jan" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Simon,
> > > > > I haven't actually tried your suggestion because I wonder if I
will
> > have a
> > > > > problem. Both workbooks have the same worksheet name; per company
> > > > request.
> > > > > Is there some code that would "select" the specific workbook by
name?
> > > > >
> > > > > TIA
> > > > > Jan
> > > > >
> > > > > "Simon Lloyd" wrote:
> > > > >
> > > > > >
> > > > > > It seems to me that it "falls down" here because although you
are
> > > > > > activating the window you are not selecting which sheet it
should
> > > > > > select the range on..........of course i may be wrong on this as
i
> > am
> > > > > > just a fledgling at programming myself!
> > > > > >
> > > > > > Regards,
> > > > > > Simon
> > > > > >
> > > > > > Windows("PAS Data.xls").Activate
> > > > > > Set rng1 = Range("a2:U2")
> > > > > > Set rng1 = Range(rng1, rng1.End(xlDown))
> > > > > > Selection.Copy
> > > > > > Windows("CSR_PAS_Report1").Activate
> > > > > > Sheets("YOUR SHEET NAME").select'Try adding this line!
> > > > > > Range("A2").Select 'this is where the code fails
> > > > > > ActiveSheet.Paste
> > > > > > Selection.AutoFilter Field:=19, Criteria1:=">=0",
Operator:=xlAnd, _
> > > > > > Criteria2:="<=30"
> > > > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> > > > > > Selection.EntireColumn.Hidden = True
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Simon Lloyd
> > > > >
> >
> ------------------------------------------------------------------------
> > > > > > Simon Lloyd's Profile:
> > > > http://www.excelforum.com/member.php...fo&userid=6708
> > > > > > View this thread:
> > > > http://www.excelforum.com/showthread...hreadid=537064
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Hi Rick,

Below is the exact copy of the code. When I run the code I am initially on
the PAS Data workbook. Thanks much.

****************
Sub CSRNewFile()
'
' CSRNewFile Macro
' Macro recorded 4/25/2006
'
Workbooks.Add Template:= _
"C:\Documents and Settings\Owner\My
Documents\Jan\Chase\PAS\CSR_Pas_Report.xlt"
End Sub
***************
Sub CopyPAS()
Dim wbk1 As Workbook, wbk2 As Workbook
Dim wsht1 As Worksheet, wsht2 As Worksheet
Dim rng1 As Range
Dim LastRow As Long

Set wbk1 = Workbooks("PAS Data.xls")
Set wbk2 = Workbooks("CSR_PAS_Report1") '
Set wsht1 = wbk1.Worksheets("Label Number")
Set wsht2 = wbk2.Worksheets("Projects")
wsht1.Activate
LastRow = wsht1.Range("A2").End(xlDown).Row
Set rng1 = wsht1.Range("A2:S" & LastRow)
rng1.Copy Destination:=wsht2.Range("A2")
wsht2.Activate
wbk2.Worksheets("Projects").Select
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden = True
Range("A2").End(xlToRight).AutoFilter Field:=19, _
Criteria1:=">=0", Operator:=xlAnd, _
Criteria2:="<=30"

End Sub



"Jan" wrote:

> Hi Rick,
>
> I'm almost there. The data from PAS Data sheet is now copied to the CSR_PAS
> Report. However, the autofilter and the columns hidden are happening on the
> PAS data sheet. They should happen on the CSR_PAS report. The code reads to
> activate the CSR_PAS report, but how is it being selected to apply the
> autofilter and hide the specific columns?
>
> I can't thank you enough for your help with this.
>
> Jan
>
> "Rick Hansen" wrote:
>
> > Good Morning Jan,
> > I've made changes to the code, to select columns A thru S, and down to
> > the lastrow. (See Code). I have tested the code a seem to run ok. I also
> > changed the workbook name of "CSR_PAS_Report1.xlt " to "CSR_PAS_Report1".
> > Now the CSR_PAS_Report well activate with the autofilter. I believe this
> > help you meet your goal you were seeking for this of many execl projects.
> >
> > Enjoy, Rick (Fbks,AK)
> >
> >
> > Option Explicit
> >
> > Sub CopyPAS()
> > Dim wbk1 As Workbook, wbk2 As Workbook
> > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > Dim rng1 As Range
> > Dim LastRow As Long
> >
> > Set wbk1 = Workbooks("PAS Data.xls")
> > Set wbk2 = Workbooks("CSR_PAS_Report1")
> > Set wsht1 = wbk1.Worksheets("Sheet1") ' <-Change to sheet name for
> > ("PASData.xls ")
> > Set wsht2 = wbk2.Worksheets("Sheet1") ' <-Change to sheet name for
> > ("CSR_PAS_REPORT1")
> >
> > wsht1.Activate ' <"PAS Data.xls">
> > LastRow = wsht1.Range("A2").End(xlDown).Row
> > Set rng1 = wsht1.Range("A2:S" & LastRow) ' select col's A thru S thru
> > lastrow
> > rng1.Copy Destination:=wsht2.Range("A2")
> >
> > wsht2.Activate ' <"CSR_PAS_Report1">
> > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden = True
> > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > Criteria1:=">=0", Operator:=xlAnd, _
> > Criteria2:="<=30"
> >
> > End Sub
> >
> >
> > "Jan" <[email protected]> wrote in message
> > news:[email protected]...
> > > Rick,
> > >
> > > I have enter your code exactly as provided, except I changed the workbook
> > > and worksheet names to what they are. I have also insured that the
> > template
> > > that is opened has been saved first before running the code.
> > > Problem: The PAS Data has information in every cell in column A; but not
> > all
> > > cells in the rows have data. So row/column A2 has data in the first 4
> > cells
> > > to the right. Consequently with the selection routine, it is only picking
> > up
> > > the first 4 columns and copying them to workbook 2 (CSR_Report). The range
> > > needs to select columns A thru S.
> > >
> > >
> > > The autofilter code is being set on the PAS Data workbook, when it should
> > be
> > > set on the CSR_report. I don't know why that would be since the
> > CSR_Report
> > > is the activte window.
> > >
> > > Thanks for your help.
> > > Jan
> > >
> > >
> > >
> > > "Rick Hansen" wrote:
> > >
> > > > Hello Again Jan, Its Rick, I took your code and rewrote it to what I
> > > > think your trying to accomplish. That is copy a range from one workbook
> > to
> > > > another,then us a autofilter. I used some the same code I sent you last
> > > > night to select the complete range. So you don't worry finding the
> > lastrow
> > > > of the range, it done for you in the code. Make sure both works books
> > are
> > > > open before you run this code. If not you'll get error with the first
> > couple
> > > > lines of code. Also make sure to change the worksheet names in the code
> > to
> > > > match the worksheets that your coping from an to. If you have any
> > question
> > > > drop me a line here or email me at [email protected]
> > > >
> > > > Enjoy, Rick (FBKS,AK)
> > > >
> > > >
> > > > Option Explicit
> > > >
> > > > Sub CopyPAS()
> > > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > > Dim rng1 As Range
> > > >
> > > > Set wbk1 = Workbooks("PAS Data.xls")
> > > > Set wbk2 = Workbooks("CSR_PAS_Report1.xls")
> > > > Set wsht1 = wbk1.Worksheets("Sheet1") '' <-Change to sheet name for
> > ("PAS
> > > > Data.xls")
> > > > Set wsht2 = wbk2.Worksheets("Sheet1") '' <-Change to sheet name for
> > > > ("CSR_PAS_REPORT1.xls")
> > > >
> > > > wsht1.Activate '' <"PAS Data.xls">
> > > > Set rng1 = wsht1.Range(Range("A2").End(xlDown),
> > > > Range("A2").End(xlToRight))
> > > > rng1.Copy Destination:=wsht2.Range("A2")
> > > >
> > > > wsht2.Activate '' <"CSR_PAS_Report1.xls">
> > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
> > True
> > > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > > Criteria1:=">=0", Operator:=xlAnd, _
> > > > Criteria2:="<=30"
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > > "Jan" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Simon,
> > > > > I haven't actually tried your suggestion because I wonder if I will
> > have a
> > > > > problem. Both workbooks have the same worksheet name; per company
> > > > request.
> > > > > Is there some code that would "select" the specific workbook by name?
> > > > >
> > > > > TIA
> > > > > Jan
> > > > >
> > > > > "Simon Lloyd" wrote:
> > > > >
> > > > > >
> > > > > > It seems to me that it "falls down" here because although you are
> > > > > > activating the window you are not selecting which sheet it should
> > > > > > select the range on..........of course i may be wrong on this as i
> > am
> > > > > > just a fledgling at programming myself!
> > > > > >
> > > > > > Regards,
> > > > > > Simon
> > > > > >
> > > > > > Windows("PAS Data.xls").Activate
> > > > > > Set rng1 = Range("a2:U2")
> > > > > > Set rng1 = Range(rng1, rng1.End(xlDown))
> > > > > > Selection.Copy
> > > > > > Windows("CSR_PAS_Report1").Activate
> > > > > > Sheets("YOUR SHEET NAME").select'Try adding this line!
> > > > > > Range("A2").Select 'this is where the code fails
> > > > > > ActiveSheet.Paste
> > > > > > Selection.AutoFilter Field:=19, Criteria1:=">=0", Operator:=xlAnd, _
> > > > > > Criteria2:="<=30"
> > > > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> > > > > > Selection.EntireColumn.Hidden = True
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Simon Lloyd
> > > > >
> > > ------------------------------------------------------------------------
> > > > > > Simon Lloyd's Profile:
> > > > http://www.excelforum.com/member.php...fo&userid=6708
> > > > > > View this thread:
> > > > http://www.excelforum.com/showthread...hreadid=537064
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Hello again Jan, With the code you sent me I figure out what was going
wrong. Part of the problem was in the CSRNewFile() . Where you added a new
Workbook. None the worksheets were rename to "Projects" in the new
workbook. This is required so excel know where to paste the data once you
copy in from "PAS Data" sheet. (Also for AutoFilter). I also found out that
excel assign a new workbook name when using a template. (for example XX_
report1, XX_report2.....) Since the workbook name is changing everytime you
excute the CSRNewFile() I had to save the workbook name in Public or
sometime know as Golbal variable. I name the Public variable "WkBkName.
This variable is now use in CSRNewFile() to rename "Sheet1" in
CSR_Pas_Report sheet. And The same variable is in CopPas() for workbook name
to identify CSR_Pas_Report(x). Now the code should do what yo want it to
do..

But first copy the two macro below to new code module. Please make sure that
Option Explicit
Public WkBkName As String
are at the very top of the code module sheet. Then go ahead and test the
code. I believe this should do it for you. I have run several test with
this new code and all checked out good. Anymore question drop a line here or
by email..
Good Luck, I enjoyed helping....

Rick, (Fbks, Ak)

(New Code)
==========================
Option Explicit
Public WkBkName As String

Sub CSRNewFile()
Workbooks.Add Template:= _
"C:\Documents and Settings\Owner\My
Documents\Jan\Chase\PAS\CSR_Pas_Report.xlt"

'> save active workbook name in public variable
WkBkName = ActiveWorkbook.Name 'get csr_pas_reportX (x=number) name of
workbook
With Workbooks(WkBkName) ' rename "sheet1" to "Projects"
.Worksheets("Sheet1").Name = "Projects"
End With
End Sub

Sub CopyPAS()
Dim wbk1 As Workbook, wbk2 As Workbook
Dim wsht1 As Worksheet, wsht2 As Worksheet
Dim rng1 As Range
Dim LastRow As Long

Set wbk1 = Workbooks("PAS Data.xls")
Set wbk2 = Workbooks(WkBkName) ' <- use public var for workbook name
Set wsht1 = wbk1.Worksheets("Label Number")
Set wsht2 = wbk2.Worksheets("Projects")
wsht1.Activate
LastRow = wsht1.Range("A2").End(xlDown).Row
Set rng1 = wsht1.Range("A2:S" & LastRow)
rng1.Copy Destination:=wsht2.Range("A2")
wsht2.Activate
wbk2.Worksheets("Projects").Select
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden = True
Range("A2").End(xlToRight).AutoFilter Field:=19, _
Criteria1:=">=0", Operator:=xlAnd, _
Criteria2:="<=30"

End Sub
==========================================

"Jan" <[email protected]> wrote in message
news:[email protected]...
> Hi Rick,
>
> Below is the exact copy of the code. When I run the code I am initially
on
> the PAS Data workbook. Thanks much.
>
> ****************
> Sub CSRNewFile()
> '
> ' CSRNewFile Macro
> ' Macro recorded 4/25/2006
> '
> Workbooks.Add Template:= _
> "C:\Documents and Settings\Owner\My
> Documents\Jan\Chase\PAS\CSR_Pas_Report.xlt"
> End Sub
> ***************
> Sub CopyPAS()
> Dim wbk1 As Workbook, wbk2 As Workbook
> Dim wsht1 As Worksheet, wsht2 As Worksheet
> Dim rng1 As Range
> Dim LastRow As Long
>
> Set wbk1 = Workbooks("PAS Data.xls")
> Set wbk2 = Workbooks("CSR_PAS_Report1") '
> Set wsht1 = wbk1.Worksheets("Label Number")
> Set wsht2 = wbk2.Worksheets("Projects")
> wsht1.Activate
> LastRow = wsht1.Range("A2").End(xlDown).Row
> Set rng1 = wsht1.Range("A2:S" & LastRow)
> rng1.Copy Destination:=wsht2.Range("A2")
> wsht2.Activate
> wbk2.Worksheets("Projects").Select
> Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
True
> Range("A2").End(xlToRight).AutoFilter Field:=19, _
> Criteria1:=">=0", Operator:=xlAnd, _
> Criteria2:="<=30"
>
> End Sub
>
>
>
> "Jan" wrote:
>
> > Hi Rick,
> >
> > I'm almost there. The data from PAS Data sheet is now copied to the
CSR_PAS
> > Report. However, the autofilter and the columns hidden are happening on
the
> > PAS data sheet. They should happen on the CSR_PAS report. The code
reads to
> > activate the CSR_PAS report, but how is it being selected to apply the
> > autofilter and hide the specific columns?
> >
> > I can't thank you enough for your help with this.
> >
> > Jan
> >
> > "Rick Hansen" wrote:
> >
> > > Good Morning Jan,
> > > I've made changes to the code, to select columns A thru S, and
down to
> > > the lastrow. (See Code). I have tested the code a seem to run ok. I
also
> > > changed the workbook name of "CSR_PAS_Report1.xlt " to
"CSR_PAS_Report1".
> > > Now the CSR_PAS_Report well activate with the autofilter. I believe
this
> > > help you meet your goal you were seeking for this of many execl
projects.
> > >
> > > Enjoy, Rick (Fbks,AK)
> > >
> > >
> > > Option Explicit
> > >
> > > Sub CopyPAS()
> > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > Dim rng1 As Range
> > > Dim LastRow As Long
> > >
> > > Set wbk1 = Workbooks("PAS Data.xls")
> > > Set wbk2 = Workbooks("CSR_PAS_Report1")
> > > Set wsht1 = wbk1.Worksheets("Sheet1") ' <-Change to sheet name for
> > > ("PASData.xls ")
> > > Set wsht2 = wbk2.Worksheets("Sheet1") ' <-Change to sheet name for
> > > ("CSR_PAS_REPORT1")
> > >
> > > wsht1.Activate ' <"PAS Data.xls">
> > > LastRow = wsht1.Range("A2").End(xlDown).Row
> > > Set rng1 = wsht1.Range("A2:S" & LastRow) ' select col's A thru S
thru
> > > lastrow
> > > rng1.Copy Destination:=wsht2.Range("A2")
> > >
> > > wsht2.Activate ' <"CSR_PAS_Report1">
> > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
True
> > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > Criteria1:=">=0", Operator:=xlAnd, _
> > > Criteria2:="<=30"
> > >
> > > End Sub
> > >
> > >
> > > "Jan" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Rick,
> > > >
> > > > I have enter your code exactly as provided, except I changed the
workbook
> > > > and worksheet names to what they are. I have also insured that the
> > > template
> > > > that is opened has been saved first before running the code.
> > > > Problem: The PAS Data has information in every cell in column A; but
not
> > > all
> > > > cells in the rows have data. So row/column A2 has data in the first
4
> > > cells
> > > > to the right. Consequently with the selection routine, it is only
picking
> > > up
> > > > the first 4 columns and copying them to workbook 2 (CSR_Report). The
range
> > > > needs to select columns A thru S.
> > > >
> > > >
> > > > The autofilter code is being set on the PAS Data workbook, when it
should
> > > be
> > > > set on the CSR_report. I don't know why that would be since the
> > > CSR_Report
> > > > is the activte window.
> > > >
> > > > Thanks for your help.
> > > > Jan
> > > >
> > > >
> > > >
> > > > "Rick Hansen" wrote:
> > > >
> > > > > Hello Again Jan, Its Rick, I took your code and rewrote it to
what I
> > > > > think your trying to accomplish. That is copy a range from one
workbook
> > > to
> > > > > another,then us a autofilter. I used some the same code I sent
you last
> > > > > night to select the complete range. So you don't worry finding the
> > > lastrow
> > > > > of the range, it done for you in the code. Make sure both works
books
> > > are
> > > > > open before you run this code. If not you'll get error with the
first
> > > couple
> > > > > lines of code. Also make sure to change the worksheet names in
the code
> > > to
> > > > > match the worksheets that your coping from an to. If you have any
> > > question
> > > > > drop me a line here or email me at [email protected]
> > > > >
> > > > > Enjoy, Rick (FBKS,AK)
> > > > >
> > > > >
> > > > > Option Explicit
> > > > >
> > > > > Sub CopyPAS()
> > > > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > > > Dim rng1 As Range
> > > > >
> > > > > Set wbk1 = Workbooks("PAS Data.xls")
> > > > > Set wbk2 = Workbooks("CSR_PAS_Report1.xls")
> > > > > Set wsht1 = wbk1.Worksheets("Sheet1") '' <-Change to sheet name
for
> > > ("PAS
> > > > > Data.xls")
> > > > > Set wsht2 = wbk2.Worksheets("Sheet1") '' <-Change to sheet name
for
> > > > > ("CSR_PAS_REPORT1.xls")
> > > > >
> > > > > wsht1.Activate '' <"PAS Data.xls">
> > > > > Set rng1 = wsht1.Range(Range("A2").End(xlDown),
> > > > > Range("A2").End(xlToRight))
> > > > > rng1.Copy Destination:=wsht2.Range("A2")
> > > > >
> > > > > wsht2.Activate '' <"CSR_PAS_Report1.xls">
> > > > >
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
> > > True
> > > > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > > > Criteria1:=">=0", Operator:=xlAnd, _
> > > > > Criteria2:="<=30"
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > > "Jan" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Simon,
> > > > > > I haven't actually tried your suggestion because I wonder if I
will
> > > have a
> > > > > > problem. Both workbooks have the same worksheet name; per
company
> > > > > request.
> > > > > > Is there some code that would "select" the specific workbook by
name?
> > > > > >
> > > > > > TIA
> > > > > > Jan
> > > > > >
> > > > > > "Simon Lloyd" wrote:
> > > > > >
> > > > > > >
> > > > > > > It seems to me that it "falls down" here because although you
are
> > > > > > > activating the window you are not selecting which sheet it
should
> > > > > > > select the range on..........of course i may be wrong on this
as i
> > > am
> > > > > > > just a fledgling at programming myself!
> > > > > > >
> > > > > > > Regards,
> > > > > > > Simon
> > > > > > >
> > > > > > > Windows("PAS Data.xls").Activate
> > > > > > > Set rng1 = Range("a2:U2")
> > > > > > > Set rng1 = Range(rng1, rng1.End(xlDown))
> > > > > > > Selection.Copy
> > > > > > > Windows("CSR_PAS_Report1").Activate
> > > > > > > Sheets("YOUR SHEET NAME").select'Try adding this line!
> > > > > > > Range("A2").Select 'this is where the code fails
> > > > > > > ActiveSheet.Paste
> > > > > > > Selection.AutoFilter Field:=19, Criteria1:=">=0",
Operator:=xlAnd, _
> > > > > > > Criteria2:="<=30"
> > > > > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> > > > > > > Selection.EntireColumn.Hidden = True
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Simon Lloyd
> > > > > >
> > >
> ------------------------------------------------------------------------
> > > > > > > Simon Lloyd's Profile:
> > > > > http://www.excelforum.com/member.php...fo&userid=6708
> > > > > > > View this thread:
> > > > > http://www.excelforum.com/showthread...hreadid=537064
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

Hi Jan, There is on other thing I caught in your code that is not required.
It the line of code under
wsht2.Activate (see below)

later , Rick


wsht2.Activate
' wbk2.Worksheets("Projects").Select < Delete this line of Code>


"Jan" <[email protected]> wrote in message
news:[email protected]...
> Hi Rick,
>
> Below is the exact copy of the code. When I run the code I am initially
on
> the PAS Data workbook. Thanks much.
>
> ****************
> Sub CSRNewFile()
> '
> ' CSRNewFile Macro
> ' Macro recorded 4/25/2006
> '
> Workbooks.Add Template:= _
> "C:\Documents and Settings\Owner\My
> Documents\Jan\Chase\PAS\CSR_Pas_Report.xlt"
> End Sub
> ***************
> Sub CopyPAS()
> Dim wbk1 As Workbook, wbk2 As Workbook
> Dim wsht1 As Worksheet, wsht2 As Worksheet
> Dim rng1 As Range
> Dim LastRow As Long
>
> Set wbk1 = Workbooks("PAS Data.xls")
> Set wbk2 = Workbooks("CSR_PAS_Report1") '
> Set wsht1 = wbk1.Worksheets("Label Number")
> Set wsht2 = wbk2.Worksheets("Projects")
> wsht1.Activate
> LastRow = wsht1.Range("A2").End(xlDown).Row
> Set rng1 = wsht1.Range("A2:S" & LastRow)
> rng1.Copy Destination:=wsht2.Range("A2")
> wsht2.Activate
> wbk2.Worksheets("Projects").Select
> Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
True
> Range("A2").End(xlToRight).AutoFilter Field:=19, _
> Criteria1:=">=0", Operator:=xlAnd, _
> Criteria2:="<=30"
>
> End Sub
>
>
>
> "Jan" wrote:
>
> > Hi Rick,
> >
> > I'm almost there. The data from PAS Data sheet is now copied to the
CSR_PAS
> > Report. However, the autofilter and the columns hidden are happening on
the
> > PAS data sheet. They should happen on the CSR_PAS report. The code
reads to
> > activate the CSR_PAS report, but how is it being selected to apply the
> > autofilter and hide the specific columns?
> >
> > I can't thank you enough for your help with this.
> >
> > Jan
> >
> > "Rick Hansen" wrote:
> >
> > > Good Morning Jan,
> > > I've made changes to the code, to select columns A thru S, and
down to
> > > the lastrow. (See Code). I have tested the code a seem to run ok. I
also
> > > changed the workbook name of "CSR_PAS_Report1.xlt " to
"CSR_PAS_Report1".
> > > Now the CSR_PAS_Report well activate with the autofilter. I believe
this
> > > help you meet your goal you were seeking for this of many execl
projects.
> > >
> > > Enjoy, Rick (Fbks,AK)
> > >
> > >
> > > Option Explicit
> > >
> > > Sub CopyPAS()
> > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > Dim rng1 As Range
> > > Dim LastRow As Long
> > >
> > > Set wbk1 = Workbooks("PAS Data.xls")
> > > Set wbk2 = Workbooks("CSR_PAS_Report1")
> > > Set wsht1 = wbk1.Worksheets("Sheet1") ' <-Change to sheet name for
> > > ("PASData.xls ")
> > > Set wsht2 = wbk2.Worksheets("Sheet1") ' <-Change to sheet name for
> > > ("CSR_PAS_REPORT1")
> > >
> > > wsht1.Activate ' <"PAS Data.xls">
> > > LastRow = wsht1.Range("A2").End(xlDown).Row
> > > Set rng1 = wsht1.Range("A2:S" & LastRow) ' select col's A thru S
thru
> > > lastrow
> > > rng1.Copy Destination:=wsht2.Range("A2")
> > >
> > > wsht2.Activate ' <"CSR_PAS_Report1">
> > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
True
> > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > Criteria1:=">=0", Operator:=xlAnd, _
> > > Criteria2:="<=30"
> > >
> > > End Sub
> > >
> > >
> > > "Jan" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Rick,
> > > >
> > > > I have enter your code exactly as provided, except I changed the
workbook
> > > > and worksheet names to what they are. I have also insured that the
> > > template
> > > > that is opened has been saved first before running the code.
> > > > Problem: The PAS Data has information in every cell in column A; but
not
> > > all
> > > > cells in the rows have data. So row/column A2 has data in the first
4
> > > cells
> > > > to the right. Consequently with the selection routine, it is only
picking
> > > up
> > > > the first 4 columns and copying them to workbook 2 (CSR_Report). The
range
> > > > needs to select columns A thru S.
> > > >
> > > >
> > > > The autofilter code is being set on the PAS Data workbook, when it
should
> > > be
> > > > set on the CSR_report. I don't know why that would be since the
> > > CSR_Report
> > > > is the activte window.
> > > >
> > > > Thanks for your help.
> > > > Jan
> > > >
> > > >
> > > >
> > > > "Rick Hansen" wrote:
> > > >
> > > > > Hello Again Jan, Its Rick, I took your code and rewrote it to
what I
> > > > > think your trying to accomplish. That is copy a range from one
workbook
> > > to
> > > > > another,then us a autofilter. I used some the same code I sent
you last
> > > > > night to select the complete range. So you don't worry finding the
> > > lastrow
> > > > > of the range, it done for you in the code. Make sure both works
books
> > > are
> > > > > open before you run this code. If not you'll get error with the
first
> > > couple
> > > > > lines of code. Also make sure to change the worksheet names in
the code
> > > to
> > > > > match the worksheets that your coping from an to. If you have any
> > > question
> > > > > drop me a line here or email me at [email protected]
> > > > >
> > > > > Enjoy, Rick (FBKS,AK)
> > > > >
> > > > >
> > > > > Option Explicit
> > > > >
> > > > > Sub CopyPAS()
> > > > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > > > Dim rng1 As Range
> > > > >
> > > > > Set wbk1 = Workbooks("PAS Data.xls")
> > > > > Set wbk2 = Workbooks("CSR_PAS_Report1.xls")
> > > > > Set wsht1 = wbk1.Worksheets("Sheet1") '' <-Change to sheet name
for
> > > ("PAS
> > > > > Data.xls")
> > > > > Set wsht2 = wbk2.Worksheets("Sheet1") '' <-Change to sheet name
for
> > > > > ("CSR_PAS_REPORT1.xls")
> > > > >
> > > > > wsht1.Activate '' <"PAS Data.xls">
> > > > > Set rng1 = wsht1.Range(Range("A2").End(xlDown),
> > > > > Range("A2").End(xlToRight))
> > > > > rng1.Copy Destination:=wsht2.Range("A2")
> > > > >
> > > > > wsht2.Activate '' <"CSR_PAS_Report1.xls">
> > > > >
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
> > > True
> > > > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > > > Criteria1:=">=0", Operator:=xlAnd, _
> > > > > Criteria2:="<=30"
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > > "Jan" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Simon,
> > > > > > I haven't actually tried your suggestion because I wonder if I
will
> > > have a
> > > > > > problem. Both workbooks have the same worksheet name; per
company
> > > > > request.
> > > > > > Is there some code that would "select" the specific workbook by
name?
> > > > > >
> > > > > > TIA
> > > > > > Jan
> > > > > >
> > > > > > "Simon Lloyd" wrote:
> > > > > >
> > > > > > >
> > > > > > > It seems to me that it "falls down" here because although you
are
> > > > > > > activating the window you are not selecting which sheet it
should
> > > > > > > select the range on..........of course i may be wrong on this
as i
> > > am
> > > > > > > just a fledgling at programming myself!
> > > > > > >
> > > > > > > Regards,
> > > > > > > Simon
> > > > > > >
> > > > > > > Windows("PAS Data.xls").Activate
> > > > > > > Set rng1 = Range("a2:U2")
> > > > > > > Set rng1 = Range(rng1, rng1.End(xlDown))
> > > > > > > Selection.Copy
> > > > > > > Windows("CSR_PAS_Report1").Activate
> > > > > > > Sheets("YOUR SHEET NAME").select'Try adding this line!
> > > > > > > Range("A2").Select 'this is where the code fails
> > > > > > > ActiveSheet.Paste
> > > > > > > Selection.AutoFilter Field:=19, Criteria1:=">=0",
Operator:=xlAnd, _
> > > > > > > Criteria2:="<=30"
> > > > > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> > > > > > > Selection.EntireColumn.Hidden = True
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Simon Lloyd
> > > > > >
> > >
> ------------------------------------------------------------------------
> > > > > > > Simon Lloyd's Profile:
> > > > > http://www.excelforum.com/member.php...fo&userid=6708
> > > > > > > View this thread:
> > > > > http://www.excelforum.com/showthread...hreadid=537064
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

Good Morning Jan, I just got home from work, I was look over all your
posting for this project to see if I missed anythink before I go to bed.
There is another minor change to your code, if not done will effect how your
autofilter runs. Remember you had change to range select for copying from
Col A thru Col S. So the same range has used for the Autofilter. So change
the last line of code in CopPas() subroutine marco to the following:

(New Code)
Range("A2:S2").AutoFilter Field:=19, _
Criteria1:=">=0", Operator:=xlAnd, _
Criteria2:="<=30"

(Old Code)
Range("A2").End(xlToRight).AutoFilter Field:=19, _
Criteria1:=">=0", Operator:=xlAnd, _
Criteria2:="<=30"

I'm sorry I over looked this change. (I guess that what you get when have do
many things going around in your head). Have great day, Let me know if it
all works. If Not we'll keep trying until we get in done.

Rick, (Fairbanks, Alaska)


"Jan" <[email protected]> wrote in message
news:[email protected]...
> Hi Rick,
>
> Below is the exact copy of the code. When I run the code I am initially
on
> the PAS Data workbook. Thanks much.
>
> ****************
> Sub CSRNewFile()
> '
> ' CSRNewFile Macro
> ' Macro recorded 4/25/2006
> '
> Workbooks.Add Template:= _
> "C:\Documents and Settings\Owner\My
> Documents\Jan\Chase\PAS\CSR_Pas_Report.xlt"
> End Sub
> ***************
> Sub CopyPAS()
> Dim wbk1 As Workbook, wbk2 As Workbook
> Dim wsht1 As Worksheet, wsht2 As Worksheet
> Dim rng1 As Range
> Dim LastRow As Long
>
> Set wbk1 = Workbooks("PAS Data.xls")
> Set wbk2 = Workbooks("CSR_PAS_Report1") '
> Set wsht1 = wbk1.Worksheets("Label Number")
> Set wsht2 = wbk2.Worksheets("Projects")
> wsht1.Activate
> LastRow = wsht1.Range("A2").End(xlDown).Row
> Set rng1 = wsht1.Range("A2:S" & LastRow)
> rng1.Copy Destination:=wsht2.Range("A2")
> wsht2.Activate
> wbk2.Worksheets("Projects").Select
> Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
True
> Range("A2").End(xlToRight).AutoFilter Field:=19, _
> Criteria1:=">=0", Operator:=xlAnd, _
> Criteria2:="<=30"
>
> End Sub
>
>
>
> "Jan" wrote:
>
> > Hi Rick,
> >
> > I'm almost there. The data from PAS Data sheet is now copied to the
CSR_PAS
> > Report. However, the autofilter and the columns hidden are happening on
the
> > PAS data sheet. They should happen on the CSR_PAS report. The code
reads to
> > activate the CSR_PAS report, but how is it being selected to apply the
> > autofilter and hide the specific columns?
> >
> > I can't thank you enough for your help with this.
> >
> > Jan
> >
> > "Rick Hansen" wrote:
> >
> > > Good Morning Jan,
> > > I've made changes to the code, to select columns A thru S, and
down to
> > > the lastrow. (See Code). I have tested the code a seem to run ok. I
also
> > > changed the workbook name of "CSR_PAS_Report1.xlt " to
"CSR_PAS_Report1".
> > > Now the CSR_PAS_Report well activate with the autofilter. I believe
this
> > > help you meet your goal you were seeking for this of many execl
projects.
> > >
> > > Enjoy, Rick (Fbks,AK)
> > >
> > >
> > > Option Explicit
> > >
> > > Sub CopyPAS()
> > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > Dim rng1 As Range
> > > Dim LastRow As Long
> > >
> > > Set wbk1 = Workbooks("PAS Data.xls")
> > > Set wbk2 = Workbooks("CSR_PAS_Report1")
> > > Set wsht1 = wbk1.Worksheets("Sheet1") ' <-Change to sheet name for
> > > ("PASData.xls ")
> > > Set wsht2 = wbk2.Worksheets("Sheet1") ' <-Change to sheet name for
> > > ("CSR_PAS_REPORT1")
> > >
> > > wsht1.Activate ' <"PAS Data.xls">
> > > LastRow = wsht1.Range("A2").End(xlDown).Row
> > > Set rng1 = wsht1.Range("A2:S" & LastRow) ' select col's A thru S
thru
> > > lastrow
> > > rng1.Copy Destination:=wsht2.Range("A2")
> > >
> > > wsht2.Activate ' <"CSR_PAS_Report1">
> > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
True
> > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > Criteria1:=">=0", Operator:=xlAnd, _
> > > Criteria2:="<=30"
> > >
> > > End Sub
> > >
> > >
> > > "Jan" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Rick,
> > > >
> > > > I have enter your code exactly as provided, except I changed the
workbook
> > > > and worksheet names to what they are. I have also insured that the
> > > template
> > > > that is opened has been saved first before running the code.
> > > > Problem: The PAS Data has information in every cell in column A; but
not
> > > all
> > > > cells in the rows have data. So row/column A2 has data in the first
4
> > > cells
> > > > to the right. Consequently with the selection routine, it is only
picking
> > > up
> > > > the first 4 columns and copying them to workbook 2 (CSR_Report). The
range
> > > > needs to select columns A thru S.
> > > >
> > > >
> > > > The autofilter code is being set on the PAS Data workbook, when it
should
> > > be
> > > > set on the CSR_report. I don't know why that would be since the
> > > CSR_Report
> > > > is the activte window.
> > > >
> > > > Thanks for your help.
> > > > Jan
> > > >
> > > >
> > > >
> > > > "Rick Hansen" wrote:
> > > >
> > > > > Hello Again Jan, Its Rick, I took your code and rewrote it to
what I
> > > > > think your trying to accomplish. That is copy a range from one
workbook
> > > to
> > > > > another,then us a autofilter. I used some the same code I sent
you last
> > > > > night to select the complete range. So you don't worry finding the
> > > lastrow
> > > > > of the range, it done for you in the code. Make sure both works
books
> > > are
> > > > > open before you run this code. If not you'll get error with the
first
> > > couple
> > > > > lines of code. Also make sure to change the worksheet names in
the code
> > > to
> > > > > match the worksheets that your coping from an to. If you have any
> > > question
> > > > > drop me a line here or email me at [email protected]
> > > > >
> > > > > Enjoy, Rick (FBKS,AK)
> > > > >
> > > > >
> > > > > Option Explicit
> > > > >
> > > > > Sub CopyPAS()
> > > > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > > > Dim rng1 As Range
> > > > >
> > > > > Set wbk1 = Workbooks("PAS Data.xls")
> > > > > Set wbk2 = Workbooks("CSR_PAS_Report1.xls")
> > > > > Set wsht1 = wbk1.Worksheets("Sheet1") '' <-Change to sheet name
for
> > > ("PAS
> > > > > Data.xls")
> > > > > Set wsht2 = wbk2.Worksheets("Sheet1") '' <-Change to sheet name
for
> > > > > ("CSR_PAS_REPORT1.xls")
> > > > >
> > > > > wsht1.Activate '' <"PAS Data.xls">
> > > > > Set rng1 = wsht1.Range(Range("A2").End(xlDown),
> > > > > Range("A2").End(xlToRight))
> > > > > rng1.Copy Destination:=wsht2.Range("A2")
> > > > >
> > > > > wsht2.Activate '' <"CSR_PAS_Report1.xls">
> > > > >
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
> > > True
> > > > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > > > Criteria1:=">=0", Operator:=xlAnd, _
> > > > > Criteria2:="<=30"
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > > "Jan" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Simon,
> > > > > > I haven't actually tried your suggestion because I wonder if I
will
> > > have a
> > > > > > problem. Both workbooks have the same worksheet name; per
company
> > > > > request.
> > > > > > Is there some code that would "select" the specific workbook by
name?
> > > > > >
> > > > > > TIA
> > > > > > Jan
> > > > > >
> > > > > > "Simon Lloyd" wrote:
> > > > > >
> > > > > > >
> > > > > > > It seems to me that it "falls down" here because although you
are
> > > > > > > activating the window you are not selecting which sheet it
should
> > > > > > > select the range on..........of course i may be wrong on this
as i
> > > am
> > > > > > > just a fledgling at programming myself!
> > > > > > >
> > > > > > > Regards,
> > > > > > > Simon
> > > > > > >
> > > > > > > Windows("PAS Data.xls").Activate
> > > > > > > Set rng1 = Range("a2:U2")
> > > > > > > Set rng1 = Range(rng1, rng1.End(xlDown))
> > > > > > > Selection.Copy
> > > > > > > Windows("CSR_PAS_Report1").Activate
> > > > > > > Sheets("YOUR SHEET NAME").select'Try adding this line!
> > > > > > > Range("A2").Select 'this is where the code fails
> > > > > > > ActiveSheet.Paste
> > > > > > > Selection.AutoFilter Field:=19, Criteria1:=">=0",
Operator:=xlAnd, _
> > > > > > > Criteria2:="<=30"
> > > > > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> > > > > > > Selection.EntireColumn.Hidden = True
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Simon Lloyd
> > > > > >
> > >
> ------------------------------------------------------------------------
> > > > > > > Simon Lloyd's Profile:
> > > > > http://www.excelforum.com/member.php...fo&userid=6708
> > > > > > > View this thread:
> > > > > http://www.excelforum.com/showthread...hreadid=537064
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

Rick,

I wasn't able to work on this project until today. Thank you so much for
your help. Things seem to be working as needed.

Jan




"Rick Hansen" wrote:

> Good Morning Jan, I just got home from work, I was look over all your
> posting for this project to see if I missed anythink before I go to bed.
> There is another minor change to your code, if not done will effect how your
> autofilter runs. Remember you had change to range select for copying from
> Col A thru Col S. So the same range has used for the Autofilter. So change
> the last line of code in CopPas() subroutine marco to the following:
>
> (New Code)
> Range("A2:S2").AutoFilter Field:=19, _
> Criteria1:=">=0", Operator:=xlAnd, _
> Criteria2:="<=30"
>
> (Old Code)
> Range("A2").End(xlToRight).AutoFilter Field:=19, _
> Criteria1:=">=0", Operator:=xlAnd, _
> Criteria2:="<=30"
>
> I'm sorry I over looked this change. (I guess that what you get when have do
> many things going around in your head). Have great day, Let me know if it
> all works. If Not we'll keep trying until we get in done.
>
> Rick, (Fairbanks, Alaska)
>
>
> "Jan" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Rick,
> >
> > Below is the exact copy of the code. When I run the code I am initially
> on
> > the PAS Data workbook. Thanks much.
> >
> > ****************
> > Sub CSRNewFile()
> > '
> > ' CSRNewFile Macro
> > ' Macro recorded 4/25/2006
> > '
> > Workbooks.Add Template:= _
> > "C:\Documents and Settings\Owner\My
> > Documents\Jan\Chase\PAS\CSR_Pas_Report.xlt"
> > End Sub
> > ***************
> > Sub CopyPAS()
> > Dim wbk1 As Workbook, wbk2 As Workbook
> > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > Dim rng1 As Range
> > Dim LastRow As Long
> >
> > Set wbk1 = Workbooks("PAS Data.xls")
> > Set wbk2 = Workbooks("CSR_PAS_Report1") '
> > Set wsht1 = wbk1.Worksheets("Label Number")
> > Set wsht2 = wbk2.Worksheets("Projects")
> > wsht1.Activate
> > LastRow = wsht1.Range("A2").End(xlDown).Row
> > Set rng1 = wsht1.Range("A2:S" & LastRow)
> > rng1.Copy Destination:=wsht2.Range("A2")
> > wsht2.Activate
> > wbk2.Worksheets("Projects").Select
> > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
> True
> > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > Criteria1:=">=0", Operator:=xlAnd, _
> > Criteria2:="<=30"
> >
> > End Sub
> >
> >
> >
> > "Jan" wrote:
> >
> > > Hi Rick,
> > >
> > > I'm almost there. The data from PAS Data sheet is now copied to the
> CSR_PAS
> > > Report. However, the autofilter and the columns hidden are happening on
> the
> > > PAS data sheet. They should happen on the CSR_PAS report. The code
> reads to
> > > activate the CSR_PAS report, but how is it being selected to apply the
> > > autofilter and hide the specific columns?
> > >
> > > I can't thank you enough for your help with this.
> > >
> > > Jan
> > >
> > > "Rick Hansen" wrote:
> > >
> > > > Good Morning Jan,
> > > > I've made changes to the code, to select columns A thru S, and
> down to
> > > > the lastrow. (See Code). I have tested the code a seem to run ok. I
> also
> > > > changed the workbook name of "CSR_PAS_Report1.xlt " to
> "CSR_PAS_Report1".
> > > > Now the CSR_PAS_Report well activate with the autofilter. I believe
> this
> > > > help you meet your goal you were seeking for this of many execl
> projects.
> > > >
> > > > Enjoy, Rick (Fbks,AK)
> > > >
> > > >
> > > > Option Explicit
> > > >
> > > > Sub CopyPAS()
> > > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > > Dim rng1 As Range
> > > > Dim LastRow As Long
> > > >
> > > > Set wbk1 = Workbooks("PAS Data.xls")
> > > > Set wbk2 = Workbooks("CSR_PAS_Report1")
> > > > Set wsht1 = wbk1.Worksheets("Sheet1") ' <-Change to sheet name for
> > > > ("PASData.xls ")
> > > > Set wsht2 = wbk2.Worksheets("Sheet1") ' <-Change to sheet name for
> > > > ("CSR_PAS_REPORT1")
> > > >
> > > > wsht1.Activate ' <"PAS Data.xls">
> > > > LastRow = wsht1.Range("A2").End(xlDown).Row
> > > > Set rng1 = wsht1.Range("A2:S" & LastRow) ' select col's A thru S
> thru
> > > > lastrow
> > > > rng1.Copy Destination:=wsht2.Range("A2")
> > > >
> > > > wsht2.Activate ' <"CSR_PAS_Report1">
> > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
> True
> > > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > > Criteria1:=">=0", Operator:=xlAnd, _
> > > > Criteria2:="<=30"
> > > >
> > > > End Sub
> > > >
> > > >
> > > > "Jan" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Rick,
> > > > >
> > > > > I have enter your code exactly as provided, except I changed the
> workbook
> > > > > and worksheet names to what they are. I have also insured that the
> > > > template
> > > > > that is opened has been saved first before running the code.
> > > > > Problem: The PAS Data has information in every cell in column A; but
> not
> > > > all
> > > > > cells in the rows have data. So row/column A2 has data in the first
> 4
> > > > cells
> > > > > to the right. Consequently with the selection routine, it is only
> picking
> > > > up
> > > > > the first 4 columns and copying them to workbook 2 (CSR_Report). The
> range
> > > > > needs to select columns A thru S.
> > > > >
> > > > >
> > > > > The autofilter code is being set on the PAS Data workbook, when it
> should
> > > > be
> > > > > set on the CSR_report. I don't know why that would be since the
> > > > CSR_Report
> > > > > is the activte window.
> > > > >
> > > > > Thanks for your help.
> > > > > Jan
> > > > >
> > > > >
> > > > >
> > > > > "Rick Hansen" wrote:
> > > > >
> > > > > > Hello Again Jan, Its Rick, I took your code and rewrote it to
> what I
> > > > > > think your trying to accomplish. That is copy a range from one
> workbook
> > > > to
> > > > > > another,then us a autofilter. I used some the same code I sent
> you last
> > > > > > night to select the complete range. So you don't worry finding the
> > > > lastrow
> > > > > > of the range, it done for you in the code. Make sure both works
> books
> > > > are
> > > > > > open before you run this code. If not you'll get error with the
> first
> > > > couple
> > > > > > lines of code. Also make sure to change the worksheet names in
> the code
> > > > to
> > > > > > match the worksheets that your coping from an to. If you have any
> > > > question
> > > > > > drop me a line here or email me at [email protected]
> > > > > >
> > > > > > Enjoy, Rick (FBKS,AK)
> > > > > >
> > > > > >
> > > > > > Option Explicit
> > > > > >
> > > > > > Sub CopyPAS()
> > > > > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > > > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > > > > Dim rng1 As Range
> > > > > >
> > > > > > Set wbk1 = Workbooks("PAS Data.xls")
> > > > > > Set wbk2 = Workbooks("CSR_PAS_Report1.xls")
> > > > > > Set wsht1 = wbk1.Worksheets("Sheet1") '' <-Change to sheet name
> for
> > > > ("PAS
> > > > > > Data.xls")
> > > > > > Set wsht2 = wbk2.Worksheets("Sheet1") '' <-Change to sheet name
> for
> > > > > > ("CSR_PAS_REPORT1.xls")
> > > > > >
> > > > > > wsht1.Activate '' <"PAS Data.xls">
> > > > > > Set rng1 = wsht1.Range(Range("A2").End(xlDown),
> > > > > > Range("A2").End(xlToRight))
> > > > > > rng1.Copy Destination:=wsht2.Range("A2")
> > > > > >
> > > > > > wsht2.Activate '' <"CSR_PAS_Report1.xls">
> > > > > >
> Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
> > > > True
> > > > > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > > > > Criteria1:=">=0", Operator:=xlAnd, _
> > > > > > Criteria2:="<=30"
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Jan" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Simon,
> > > > > > > I haven't actually tried your suggestion because I wonder if I
> will
> > > > have a
> > > > > > > problem. Both workbooks have the same worksheet name; per
> company
> > > > > > request.
> > > > > > > Is there some code that would "select" the specific workbook by
> name?
> > > > > > >
> > > > > > > TIA
> > > > > > > Jan
> > > > > > >
> > > > > > > "Simon Lloyd" wrote:
> > > > > > >
> > > > > > > >
> > > > > > > > It seems to me that it "falls down" here because although you
> are
> > > > > > > > activating the window you are not selecting which sheet it
> should
> > > > > > > > select the range on..........of course i may be wrong on this
> as i
> > > > am
> > > > > > > > just a fledgling at programming myself!
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > > Simon
> > > > > > > >
> > > > > > > > Windows("PAS Data.xls").Activate
> > > > > > > > Set rng1 = Range("a2:U2")
> > > > > > > > Set rng1 = Range(rng1, rng1.End(xlDown))
> > > > > > > > Selection.Copy
> > > > > > > > Windows("CSR_PAS_Report1").Activate
> > > > > > > > Sheets("YOUR SHEET NAME").select'Try adding this line!
> > > > > > > > Range("A2").Select 'this is where the code fails
> > > > > > > > ActiveSheet.Paste
> > > > > > > > Selection.AutoFilter Field:=19, Criteria1:=">=0",
> Operator:=xlAnd, _
> > > > > > > > Criteria2:="<=30"
> > > > > > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> > > > > > > > Selection.EntireColumn.Hidden = True
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > Simon Lloyd
> > > > > > >
> > > >
> > ------------------------------------------------------------------------
> > > > > > > > Simon Lloyd's Profile:
> > > > > > http://www.excelforum.com/member.php...fo&userid=6708
> > > > > > > > View this thread:
> > > > > > http://www.excelforum.com/showthread...hreadid=537064
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>

Jan, Your very welcome. I'm glad I could give a helping hand... Take care

Rick, (Fairbanks, Alaska)

"Jan" <[email protected]> wrote in message
news:[email protected]...
> Rick,
>
> I wasn't able to work on this project until today. Thank you so much for
> your help. Things seem to be working as needed.
>
> Jan
>
>
>
>
> "Rick Hansen" wrote:
>
> > Good Morning Jan, I just got home from work, I was look over all your
> > posting for this project to see if I missed anythink before I go to
bed.
> > There is another minor change to your code, if not done will effect how
your
> > autofilter runs. Remember you had change to range select for copying
from
> > Col A thru Col S. So the same range has used for the Autofilter. So
change
> > the last line of code in CopPas() subroutine marco to the following:
> >
> > (New Code)
> > Range("A2:S2").AutoFilter Field:=19, _
> > Criteria1:=">=0", Operator:=xlAnd, _
> > Criteria2:="<=30"
> >
> > (Old Code)
> > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > Criteria1:=">=0", Operator:=xlAnd, _
> > Criteria2:="<=30"
> >
> > I'm sorry I over looked this change. (I guess that what you get when
have do
> > many things going around in your head). Have great day, Let me know if
it
> > all works. If Not we'll keep trying until we get in done.
> >
> > Rick, (Fairbanks, Alaska)
> >
> >
> > "Jan" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Rick,
> > >
> > > Below is the exact copy of the code. When I run the code I am
initially
> > on
> > > the PAS Data workbook. Thanks much.
> > >
> > > ****************
> > > Sub CSRNewFile()
> > > '
> > > ' CSRNewFile Macro
> > > ' Macro recorded 4/25/2006
> > > '
> > > Workbooks.Add Template:= _
> > > "C:\Documents and Settings\Owner\My
> > > Documents\Jan\Chase\PAS\CSR_Pas_Report.xlt"
> > > End Sub
> > > ***************
> > > Sub CopyPAS()
> > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > Dim rng1 As Range
> > > Dim LastRow As Long
> > >
> > > Set wbk1 = Workbooks("PAS Data.xls")
> > > Set wbk2 = Workbooks("CSR_PAS_Report1") '
> > > Set wsht1 = wbk1.Worksheets("Label Number")
> > > Set wsht2 = wbk2.Worksheets("Projects")
> > > wsht1.Activate
> > > LastRow = wsht1.Range("A2").End(xlDown).Row
> > > Set rng1 = wsht1.Range("A2:S" & LastRow)
> > > rng1.Copy Destination:=wsht2.Range("A2")
> > > wsht2.Activate
> > > wbk2.Worksheets("Projects").Select
> > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
> > True
> > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > Criteria1:=">=0", Operator:=xlAnd, _
> > > Criteria2:="<=30"
> > >
> > > End Sub
> > >
> > >
> > >
> > > "Jan" wrote:
> > >
> > > > Hi Rick,
> > > >
> > > > I'm almost there. The data from PAS Data sheet is now copied to the
> > CSR_PAS
> > > > Report. However, the autofilter and the columns hidden are
happening on
> > the
> > > > PAS data sheet. They should happen on the CSR_PAS report. The code
> > reads to
> > > > activate the CSR_PAS report, but how is it being selected to apply
the
> > > > autofilter and hide the specific columns?
> > > >
> > > > I can't thank you enough for your help with this.
> > > >
> > > > Jan
> > > >
> > > > "Rick Hansen" wrote:
> > > >
> > > > > Good Morning Jan,
> > > > > I've made changes to the code, to select columns A thru S, and
> > down to
> > > > > the lastrow. (See Code). I have tested the code a seem to run ok.
I
> > also
> > > > > changed the workbook name of "CSR_PAS_Report1.xlt " to
> > "CSR_PAS_Report1".
> > > > > Now the CSR_PAS_Report well activate with the autofilter. I
believe
> > this
> > > > > help you meet your goal you were seeking for this of many execl
> > projects.
> > > > >
> > > > > Enjoy, Rick (Fbks,AK)
> > > > >
> > > > >
> > > > > Option Explicit
> > > > >
> > > > > Sub CopyPAS()
> > > > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > > > Dim rng1 As Range
> > > > > Dim LastRow As Long
> > > > >
> > > > > Set wbk1 = Workbooks("PAS Data.xls")
> > > > > Set wbk2 = Workbooks("CSR_PAS_Report1")
> > > > > Set wsht1 = wbk1.Worksheets("Sheet1") ' <-Change to sheet name
for
> > > > > ("PASData.xls ")
> > > > > Set wsht2 = wbk2.Worksheets("Sheet1") ' <-Change to sheet name
for
> > > > > ("CSR_PAS_REPORT1")
> > > > >
> > > > > wsht1.Activate ' <"PAS Data.xls">
> > > > > LastRow = wsht1.Range("A2").End(xlDown).Row
> > > > > Set rng1 = wsht1.Range("A2:S" & LastRow) ' select col's A thru
S
> > thru
> > > > > lastrow
> > > > > rng1.Copy Destination:=wsht2.Range("A2")
> > > > >
> > > > > wsht2.Activate ' <"CSR_PAS_Report1">
> > > > >
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
> > True
> > > > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > > > Criteria1:=">=0", Operator:=xlAnd, _
> > > > > Criteria2:="<=30"
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > > "Jan" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Rick,
> > > > > >
> > > > > > I have enter your code exactly as provided, except I changed the
> > workbook
> > > > > > and worksheet names to what they are. I have also insured that
the
> > > > > template
> > > > > > that is opened has been saved first before running the code.
> > > > > > Problem: The PAS Data has information in every cell in column A;
but
> > not
> > > > > all
> > > > > > cells in the rows have data. So row/column A2 has data in the
first
> > 4
> > > > > cells
> > > > > > to the right. Consequently with the selection routine, it is
only
> > picking
> > > > > up
> > > > > > the first 4 columns and copying them to workbook 2 (CSR_Report).
The
> > range
> > > > > > needs to select columns A thru S.
> > > > > >
> > > > > >
> > > > > > The autofilter code is being set on the PAS Data workbook, when
it
> > should
> > > > > be
> > > > > > set on the CSR_report. I don't know why that would be since the
> > > > > CSR_Report
> > > > > > is the activte window.
> > > > > >
> > > > > > Thanks for your help.
> > > > > > Jan
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Rick Hansen" wrote:
> > > > > >
> > > > > > > Hello Again Jan, Its Rick, I took your code and rewrote it
to
> > what I
> > > > > > > think your trying to accomplish. That is copy a range from one
> > workbook
> > > > > to
> > > > > > > another,then us a autofilter. I used some the same code I
sent
> > you last
> > > > > > > night to select the complete range. So you don't worry finding
the
> > > > > lastrow
> > > > > > > of the range, it done for you in the code. Make sure both
works
> > books
> > > > > are
> > > > > > > open before you run this code. If not you'll get error with
the
> > first
> > > > > couple
> > > > > > > lines of code. Also make sure to change the worksheet names
in
> > the code
> > > > > to
> > > > > > > match the worksheets that your coping from an to. If you have
any
> > > > > question
> > > > > > > drop me a line here or email me at [email protected]
> > > > > > >
> > > > > > > Enjoy, Rick (FBKS,AK)
> > > > > > >
> > > > > > >
> > > > > > > Option Explicit
> > > > > > >
> > > > > > > Sub CopyPAS()
> > > > > > > Dim wbk1 As Workbook, wbk2 As Workbook
> > > > > > > Dim wsht1 As Worksheet, wsht2 As Worksheet
> > > > > > > Dim rng1 As Range
> > > > > > >
> > > > > > > Set wbk1 = Workbooks("PAS Data.xls")
> > > > > > > Set wbk2 = Workbooks("CSR_PAS_Report1.xls")
> > > > > > > Set wsht1 = wbk1.Worksheets("Sheet1") '' <-Change to sheet
name
> > for
> > > > > ("PAS
> > > > > > > Data.xls")
> > > > > > > Set wsht2 = wbk2.Worksheets("Sheet1") '' <-Change to sheet
name
> > for
> > > > > > > ("CSR_PAS_REPORT1.xls")
> > > > > > >
> > > > > > > wsht1.Activate '' <"PAS Data.xls">
> > > > > > > Set rng1 = wsht1.Range(Range("A2").End(xlDown),
> > > > > > > Range("A2").End(xlToRight))
> > > > > > > rng1.Copy Destination:=wsht2.Range("A2")
> > > > > > >
> > > > > > > wsht2.Activate '' <"CSR_PAS_Report1.xls">
> > > > > > >
> > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").EntireColumn.Hidden =
> > > > > True
> > > > > > > Range("A2").End(xlToRight).AutoFilter Field:=19, _
> > > > > > > Criteria1:=">=0", Operator:=xlAnd, _
> > > > > > > Criteria2:="<=30"
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Jan" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > Simon,
> > > > > > > > I haven't actually tried your suggestion because I wonder if
I
> > will
> > > > > have a
> > > > > > > > problem. Both workbooks have the same worksheet name; per
> > company
> > > > > > > request.
> > > > > > > > Is there some code that would "select" the specific workbook
by
> > name?
> > > > > > > >
> > > > > > > > TIA
> > > > > > > > Jan
> > > > > > > >
> > > > > > > > "Simon Lloyd" wrote:
> > > > > > > >
> > > > > > > > >
> > > > > > > > > It seems to me that it "falls down" here because although
you
> > are
> > > > > > > > > activating the window you are not selecting which sheet it
> > should
> > > > > > > > > select the range on..........of course i may be wrong on
this
> > as i
> > > > > am
> > > > > > > > > just a fledgling at programming myself!
> > > > > > > > >
> > > > > > > > > Regards,
> > > > > > > > > Simon
> > > > > > > > >
> > > > > > > > > Windows("PAS Data.xls").Activate
> > > > > > > > > Set rng1 = Range("a2:U2")
> > > > > > > > > Set rng1 = Range(rng1, rng1.End(xlDown))
> > > > > > > > > Selection.Copy
> > > > > > > > > Windows("CSR_PAS_Report1").Activate
> > > > > > > > > Sheets("YOUR SHEET NAME").select'Try adding this line!
> > > > > > > > > Range("A2").Select 'this is where the code fails
> > > > > > > > > ActiveSheet.Paste
> > > > > > > > > Selection.AutoFilter Field:=19, Criteria1:=">=0",
> > Operator:=xlAnd, _
> > > > > > > > > Criteria2:="<=30"
> > > > > > > > > Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Selec t
> > > > > > > > > Selection.EntireColumn.Hidden = True
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Simon Lloyd
> > > > > > > >
> > > > >
> >
> ------------------------------------------------------------------------
> > > > > > > > > Simon Lloyd's Profile:
> > > > > > >
http://www.excelforum.com/member.php...fo&userid=6708
> > > > > > > > > View this thread:
> > > > > > > http://www.excelforum.com/showthread...hreadid=537064
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> >
> >
> >