How to select range of Active Window

G

Guest

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
 
S

Simon Lloyd

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
 
M

mudraker

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
 
G

Guest

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
 
G

Guest

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
 
I

Ivan Raiminius

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
 
R

Rick Hansen

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 (e-mail address removed)

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
 
G

Guest

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
 
G

Guest

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
 
R

Rick Hansen

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
 
G

Guest

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
 
R

Rick Hansen

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
 
G

Guest

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
 
R

Rick Hansen

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
==========================================
 
R

Rick Hansen

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>
 
R

Rick Hansen

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)
 
G

Guest

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
 
R

Rick Hansen

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

Rick, (Fairbanks, Alaska)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access an open workbook without making it active 2
End process at last row of data 3
Macro in one workbook that names a range in a different workbook 2
Return to original workbook 1
Prevent a workbook from displaying 1
Import multiple workbooks to current workbook in specified row number 0
Error Message - Macro to Unlink MS Queries from Workbook 0
loop thru cells to find matching record 2

Top