WorkSheet or AutoFilter Problem

[John Quinn]

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]

You have a follow up at the original post.

Do you have data in column 12?

 

[John Quinn]

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

 

[Tom Ogilvy]

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

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

worked fine for me.

 

[John Quinn]

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.