WorkSheet or AutoFilter Problem
 

I searched the users group and came up with some changes, but still cannot
get this portion of my macro to work!

On the first example Marked by '*, I get a hard halt.

My second example the WorkSheet never has any data in it?

I will be happy with either method, if someone knows what is wrong?

ClassAAAA

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim lastRow As Long

Set wkst = ActiveSheet
Set lbls = Workbooks.Add(1)
Set wslb = lbls.Worksheets(1)
lbls.Title = "Letters to Educators"
lbls.Subject = "Grades"

With wkst
.AutoFilterMode = False
.Columns(12).AutoFilter Field:=1, Criterial:="AAA" '*
Application-defined or Object-defined Error
.Columns(2).Copy wslb.Columns(2)
.Columns(5).Copy wslb.Columns(3)
.Columns(7).Copy wslb.Columns(4)
.Columns(8).Copy wslb.Columns(5)
.Columns(9).Copy wslb.Columns(6)
End With

wslb.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:=xlNormal

MsgBox "Class AAAA Completed"

End Sub

' Also Tried Thsi Method

Sub ClassAAA()

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim lastRow As Long
Dim chkClass As String

Set wkst = ActiveSheet
lastRow = Range("L" & Rows.Count).End(xlUp).Row
Range("L1").Select
chkClass = "AAA"

SelCol = InputBox("Enter the Column for the Educator You Want to
Send Letters to:!")
Set lbls = Workbooks.Add

With lbls
.Title = "Letters to Educators"
.Subject = "Grades"
End With

Set wslb = ActiveSheet
Do Until ActiveCell.Row lastRow
If ActiveCell.Value = chkClass Then '*
ActiveCell.Value is Always Empty
wkst.Columns(3).Copy wslb.Columns(2)
wkst.Columns(5).Copy wslb.Columns(3)
wkst.Columns(7).Copy wslb.Columns(4)
wkst.Columns(8).Copy wslb.Columns(5)
End If
ActiveCell.Offset(1, 0).Activate ' Move to Next Row
Loop

wslb.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:=xlNormal

MsgBox "Class AAA Completed"

End Sub

There is just something I am not understanding, one of my worksheets must
have something in memory?

Again, a lot of this code came from users in the group.

Thanks Again

J.Q.

WorkSheet or AutoFilter Problem
 

You have a follow up at the original post.

Do you have data in column 12?

John Quinn wrote:

I searched the users group and came up with some changes, but still cannot
get this portion of my macro to work!

On the first example Marked by '*, I get a hard halt.

My second example the WorkSheet never has any data in it?

I will be happy with either method, if someone knows what is wrong?

ClassAAAA

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim lastRow As Long

Set wkst = ActiveSheet
Set lbls = Workbooks.Add(1)
Set wslb = lbls.Worksheets(1)
lbls.Title = "Letters to Educators"
lbls.Subject = "Grades"

With wkst
.AutoFilterMode = False
.Columns(12).AutoFilter Field:=1, Criterial:="AAA" '*
Application-defined or Object-defined Error
.Columns(2).Copy wslb.Columns(2)
.Columns(5).Copy wslb.Columns(3)
.Columns(7).Copy wslb.Columns(4)
.Columns(8).Copy wslb.Columns(5)
.Columns(9).Copy wslb.Columns(6)
End With

wslb.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:=xlNormal

MsgBox "Class AAAA Completed"

End Sub

' Also Tried Thsi Method

Sub ClassAAA()

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim lastRow As Long
Dim chkClass As String

Set wkst = ActiveSheet
lastRow = Range("L" & Rows.Count).End(xlUp).Row
Range("L1").Select
chkClass = "AAA"

SelCol = InputBox("Enter the Column for the Educator You Want to
Send Letters to:!")
Set lbls = Workbooks.Add

With lbls
.Title = "Letters to Educators"
.Subject = "Grades"
End With

Set wslb = ActiveSheet
Do Until ActiveCell.Row lastRow
If ActiveCell.Value = chkClass Then '*
ActiveCell.Value is Always Empty
wkst.Columns(3).Copy wslb.Columns(2)
wkst.Columns(5).Copy wslb.Columns(3)
wkst.Columns(7).Copy wslb.Columns(4)
wkst.Columns(8).Copy wslb.Columns(5)
End If
ActiveCell.Offset(1, 0).Activate ' Move to Next Row
Loop

wslb.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:=xlNormal

MsgBox "Class AAA Completed"

End Sub

There is just something I am not understanding, one of my worksheets must
have something in memory?

Again, a lot of this code came from users in the group.

Thanks Again

J.Q.

--

Dave Peterson

WorkSheet or AutoFilter Problem
 

When I look at the WorkBook there is Data in Column 12 of every row. There
can never be a null or empty!

On the AutoFilter Line I originally had:

Selection.AutoFilter Field:=12, Criterial1="FGJKLPQR"

This worked until I realized that I had to create a simplier WorkBook to
work from. When I put the:

wslb WorkSheet in, it stopped working.

I am taking this a piece at a time, I am only about 10% to 20% of the
completed task. I felt with my limited knowledge this was the best way to
go. This WorkBook Application is no where near the best place to start when
you are a beginner.

Thanks

John
"Dave Peterson" wrote:

You have a follow up at the original post.

Do you have data in column 12?

John Quinn wrote:

I searched the users group and came up with some changes, but still cannot
get this portion of my macro to work!

On the first example Marked by '*, I get a hard halt.

My second example the WorkSheet never has any data in it?

I will be happy with either method, if someone knows what is wrong?

ClassAAAA

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim lastRow As Long

Set wkst = ActiveSheet
Set lbls = Workbooks.Add(1)
Set wslb = lbls.Worksheets(1)
lbls.Title = "Letters to Educators"
lbls.Subject = "Grades"

With wkst
.AutoFilterMode = False
.Columns(12).AutoFilter Field:=1, Criterial:="AAA" '*
Application-defined or Object-defined Error
.Columns(2).Copy wslb.Columns(2)
.Columns(5).Copy wslb.Columns(3)
.Columns(7).Copy wslb.Columns(4)
.Columns(8).Copy wslb.Columns(5)
.Columns(9).Copy wslb.Columns(6)
End With

wslb.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:=xlNormal

MsgBox "Class AAAA Completed"

End Sub

' Also Tried Thsi Method

Sub ClassAAA()

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim lastRow As Long
Dim chkClass As String

Set wkst = ActiveSheet
lastRow = Range("L" & Rows.Count).End(xlUp).Row
Range("L1").Select
chkClass = "AAA"

SelCol = InputBox("Enter the Column for the Educator You Want to
Send Letters to:!")
Set lbls = Workbooks.Add

With lbls
.Title = "Letters to Educators"
.Subject = "Grades"
End With

Set wslb = ActiveSheet
Do Until ActiveCell.Row lastRow
If ActiveCell.Value = chkClass Then '*
ActiveCell.Value is Always Empty
wkst.Columns(3).Copy wslb.Columns(2)
wkst.Columns(5).Copy wslb.Columns(3)
wkst.Columns(7).Copy wslb.Columns(4)
wkst.Columns(8).Copy wslb.Columns(5)
End If
ActiveCell.Offset(1, 0).Activate ' Move to Next Row
Loop

wslb.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:=xlNormal

MsgBox "Class AAA Completed"

End Sub

There is just something I am not understanding, one of my worksheets must
have something in memory?

Again, a lot of this code came from users in the group.

Thanks Again

J.Q.

--

Dave Peterson

WorkSheet or AutoFilter Problem
 

Criteria should have the numberal 1 on the end. Yours doesn't

..Columns(12).AutoFilter Field:=1, Criteria1:="AAA"

worked fine for me.

--
Regards,
Tom Ogilvy


"John Quinn" wrote:

I searched the users group and came up with some changes, but still cannot
get this portion of my macro to work!

On the first example Marked by '*, I get a hard halt.

My second example the WorkSheet never has any data in it?

I will be happy with either method, if someone knows what is wrong?

ClassAAAA

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim lastRow As Long

Set wkst = ActiveSheet
Set lbls = Workbooks.Add(1)
Set wslb = lbls.Worksheets(1)
lbls.Title = "Letters to Educators"
lbls.Subject = "Grades"

With wkst
.AutoFilterMode = False
.Columns(12).AutoFilter Field:=1, Criterial:="AAA" '*
Application-defined or Object-defined Error
.Columns(2).Copy wslb.Columns(2)
.Columns(5).Copy wslb.Columns(3)
.Columns(7).Copy wslb.Columns(4)
.Columns(8).Copy wslb.Columns(5)
.Columns(9).Copy wslb.Columns(6)
End With

wslb.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:=xlNormal

MsgBox "Class AAAA Completed"

End Sub

' Also Tried Thsi Method

Sub ClassAAA()

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim lastRow As Long
Dim chkClass As String

Set wkst = ActiveSheet
lastRow = Range("L" & Rows.Count).End(xlUp).Row
Range("L1").Select
chkClass = "AAA"

SelCol = InputBox("Enter the Column for the Educator You Want to
Send Letters to:!")
Set lbls = Workbooks.Add

With lbls
.Title = "Letters to Educators"
.Subject = "Grades"
End With

Set wslb = ActiveSheet
Do Until ActiveCell.Row lastRow
If ActiveCell.Value = chkClass Then '*
ActiveCell.Value is Always Empty
wkst.Columns(3).Copy wslb.Columns(2)
wkst.Columns(5).Copy wslb.Columns(3)
wkst.Columns(7).Copy wslb.Columns(4)
wkst.Columns(8).Copy wslb.Columns(5)
End If
ActiveCell.Offset(1, 0).Activate ' Move to Next Row
Loop

wslb.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:=xlNormal

MsgBox "Class AAA Completed"

End Sub

There is just something I am not understanding, one of my worksheets must
have something in memory?

Again, a lot of this code came from users in the group.

Thanks Again

J.Q.

WorkSheet or AutoFilter Problem
 

Tom;

Thanks for getting back! The error message has changed, but I did go
to the next line before I got it..

I get this message (I was getting before I made the typo, that is why
I tried the Do Loop) I keyed l instead of 1. However both sheets never have
any data in them, this is why I tried something I found from the users group.

The Information cannot be pasted because the Copy area and the paste area
are not the same size and shape.

Thanks

John Q.


"Tom Ogilvy" wrote:

Criteria should have the numberal 1 on the end. Yours doesn't

.Columns(12).AutoFilter Field:=1, Criteria1:="AAA"

worked fine for me.

--
Regards,
Tom Ogilvy


"John Quinn" wrote:

I searched the users group and came up with some changes, but still cannot
get this portion of my macro to work!

On the first example Marked by '*, I get a hard halt.

My second example the WorkSheet never has any data in it?

I will be happy with either method, if someone knows what is wrong?

ClassAAAA

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim lastRow As Long

Set wkst = ActiveSheet
Set lbls = Workbooks.Add(1)
Set wslb = lbls.Worksheets(1)
lbls.Title = "Letters to Educators"
lbls.Subject = "Grades"

With wkst
.AutoFilterMode = False
.Columns(12).AutoFilter Field:=1, Criterial:="AAA" '*
Application-defined or Object-defined Error
.Columns(2).Copy wslb.Columns(2)
.Columns(5).Copy wslb.Columns(3)
.Columns(7).Copy wslb.Columns(4)
.Columns(8).Copy wslb.Columns(5)
.Columns(9).Copy wslb.Columns(6)
End With

wslb.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:=xlNormal

MsgBox "Class AAAA Completed"

End Sub

' Also Tried Thsi Method

Sub ClassAAA()

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim lastRow As Long
Dim chkClass As String

Set wkst = ActiveSheet
lastRow = Range("L" & Rows.Count).End(xlUp).Row
Range("L1").Select
chkClass = "AAA"

SelCol = InputBox("Enter the Column for the Educator You Want to
Send Letters to:!")
Set lbls = Workbooks.Add

With lbls
.Title = "Letters to Educators"
.Subject = "Grades"
End With

Set wslb = ActiveSheet
Do Until ActiveCell.Row lastRow
If ActiveCell.Value = chkClass Then '*
ActiveCell.Value is Always Empty
wkst.Columns(3).Copy wslb.Columns(2)
wkst.Columns(5).Copy wslb.Columns(3)
wkst.Columns(7).Copy wslb.Columns(4)
wkst.Columns(8).Copy wslb.Columns(5)
End If
ActiveCell.Offset(1, 0).Activate ' Move to Next Row
Loop

wslb.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:=xlNormal

MsgBox "Class AAA Completed"

End Sub

There is just something I am not understanding, one of my worksheets must
have something in memory?

Again, a lot of this code came from users in the group.

Thanks Again

J.Q.