Copying data & creating new worksheets - error

[Jen]

I'm trying to create new worksheets based on a column in my active
worksheet. I've copied and altered some code from ron debruin's
webpage. This is part of a much longer module that defined the range
previously. I keep getting caught up in this one area and get an error
that says: "Object variable or With block variable not set" Yes, I'm
clueless and really don't know what I am doing but could use some
help/advice. Thanks.


Dim CalcMode As Long
Dim ws1 As Worksheet
Dim WSNew As Worksheet
Dim cell As Range
Dim Lrow As Long

'Tip : Use a Dynamic range name,
http://www.contextures.com/xlNames01.html#Dynamic
'or a fixed range like Range("A1:H1200")
Set rng = ws1.Range("A1", ("A" & NoAccounts)).Product '<<< Change

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'THIS IS WHERE THE ERROR IS OCCURING
With ws1
==========================>rng.Columns(1).AdvancedFilter _
==========================>Action:=xlFilterCopy, _
==========================>CopyToRange:=.Range("CR1"),
Unique:=True
'This example filter on the first column in the range (change
this if needed)
'You see that the last two columns of the worksheet are used to
make a Unique list
'and add the CriteriaRange.(you can't use this macro if you use
the columns)


Lrow = .Cells(Rows.Count, "CR").End(xlUp).row
.Range("CQ1").Value = .Range("CR1").Value

For Each cell In .Range("CR2:CR" & Lrow)
.Range("CQ2").Value = cell.Value
Set WSNew = Sheets.Add
On Error Resume Next
WSNew.Name = cell.Value
If Err.Number > 0 Then
MsgBox "Change the name of : " & WSNew.Name & "
manually"
Err.Clear
End If
On Error GoTo 0
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("J1:J2"), _
CopyToRange:=WSNew.Range("A1"), _
Unique:=False
WSNew.Columns.AutoFit
Next
.Columns("J:K").Clear
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

 

[Guest]

With ws1
rng.Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("CR1"),
Unique:=True

First you say
With ws1
but you don't ever show setting ws1 to anything. There should be a line of
code like

Set ws1 = Activesheet

or

Set ws1 = Worksheets("Data")

However, that should surface as an error on this line
Set rng = ws1.Range("A1", ("A" & NoAccounts)).Product '<<< Change

Remove the from the end of True if it is actually there

You set rng to range reference earlier in the code, so it should be OK
assuming you have data in column A.

so have you defined a named range CR1 (insert=>range=>Define) and is that
named range located on the sheet which will be referenced by ws1.

 

[Jen]

Tom,

I tried setting the worksheet both ways that you suggested. It's still
getting caught up on this, with the same variable not defined error
message:

rng.Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("CR1"), Unique:=True

I haven't defined the range CR1, how do I go about doing that?

Thanks,
Jennifer

 

[Guest]

As I said, Insert =>Name=>Define

Name: CR1
RefersTo: =Sheet1!$N$10

click the ADD button.

Change the
=Sheet1!$N$10

to the location where you want the advanced filter to copy the data to.

 

[Dave Peterson]

CR1 is an address. You're putting the unique list on that ws1 worksheet in cell
CR1.

But I didn't see where you set ws1 variable to any worksheet.

Set ws1 = activesheet
or
set ws1 = worksheets("sheet99") '<--change to what you want.

This set statement has to be before your
Set rng = ws1.Range("A1", ("A" & NoAccounts)).Product '<<< Change
line.

 

[Dave Peterson]

Never mind!

CR1 is an address. You're putting the unique list on that ws1 worksheet in cell
CR1.

But I didn't see where you set ws1 variable to any worksheet.

Set ws1 = activesheet
or
set ws1 = worksheets("sheet99") '<--change to what you want.

This set statement has to be before your
Set rng = ws1.Range("A1", ("A" & NoAccounts)).Product '<<< Change
line.

 

[Dave Peterson]

But don't use that name. It looks too much like an address (to me and excel!).

Maybe _CR1 (with the underscore)

 

[Tom Ogilvy]

Good catch. I got headed off in the wrong direction with this line thinking
the OP had a defined name/range:

This is part of a much longer module that defined the range previously.

I got target fixation - flew into the ground <g>
Of course that is just a cell reference.

 

[Dave Peterson]

Well, I read your response about it being a named range and thought I missed it
(and didn't go back to review).

Well, it sounds like the OP may have something that is close to working <vbg>.