Move Data From Active Sheet to New WorkBook
 

This is only the third time in my life I have used Excel.

I am sure it is going to be a learning curve.

I have a huge SpreadSheet I need to reduce to a workable document. I had it
working between Active Sheeets but kept losing data.

I now realize that I must copy the selected information to a new Workbook,
but I am having trouble learning the proper code.

Here is my code so far, I have been working on it for two days so it may not
look very good!

Sub ClassAAA()

Dim lastRow As Long
Dim WB As Workbook
Dim NB As Workbook
Dim ws As Worksheet
Dim SelCol As String

' Selection.AutoFilter Field:=12, Criteria1:="AAA"
SelCol = InputBox("Enter The Column for the Coach You Want to Send
Letters To:!")
Set WB = Workbooks.Add
With WB
.Title = "Letters to Parents"
.Subject = "Grades"
.SaveAs Filename:="TxLabels.xls"
End With
' Workbooks.Open ("TxLabels.xls")
Set WB = ActiveWorkbook
Set NB = ActiveWorkbook
Set wk = ActiveSheet
' Set ws = Workbooks.Add(after:=Worksheets(Worksheets.Count))
WB.Columns(SelCol).Copy NB.Columns(1) ' ? Here is my problem at
this point
wk.Columns(3).Copy ws.Columns(2)
wk.Columns(5).Copy ws.Columns(3)
wk.Columns(7).Copy ws.Columns(4)
wk.Columns(8).Copy ws.Columns(5)
wk.Columns(9).Copy ws.Columns(6)

' ActiveCell.FormulaR1C1 = ""
' ChDir "C:\ExcelExp"

ActiveWorkbook.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

MsgBox "Class AAA Completed"

End Sub

I just cannot find examples of the objects I need to learn.

Thanks for any help

Len

Move Data From Active Sheet to New WorkBook
 

Sub ClassAAA()

Dim lastRow As Long
Dim WB As Workbook ' TxLabels.xls
Dim ws As Worksheet ' First sh in TxLabels.xls
Dim wk as Worksheet ' sheet with data
Dim SelCol As String

' Selection.AutoFilter Field:=12, Criteria1:="AAA"

' assume sheet with data is the activesheet when you run the macro

Set wk = ActiveSheet
SelCol = InputBox("Enter The Column for the Coach You Want to Send
Letters To:!")
Set WB = Workbooks.Add
With WB
.Title = "Letters to Parents"
.Subject = "Grades"
End With
set ws = Activesheet
wk.Columns(SelCol).Copy ws.Columns(1)
wk.Columns(3).Copy ws.Columns(2)
wk.Columns(5).Copy ws.Columns(3)
wk.Columns(7).Copy ws.Columns(4)
wk.Columns(8).Copy ws.Columns(5)
wk.Columns(9).Copy ws.Columns(6)

' ActiveCell.FormulaR1C1 = ""
' ChDir "C:\ExcelExp"

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

MsgBox "Class AAA Completed"

End Sub

--
Regards,
Tom Ogilvy


"Len" wrote:

This is only the third time in my life I have used Excel.

I am sure it is going to be a learning curve.

I have a huge SpreadSheet I need to reduce to a workable document. I had it
working between Active Sheeets but kept losing data.

I now realize that I must copy the selected information to a new Workbook,
but I am having trouble learning the proper code.

Here is my code so far, I have been working on it for two days so it may not
look very good!

Sub ClassAAA()

Dim lastRow As Long
Dim WB As Workbook
Dim NB As Workbook
Dim ws As Worksheet
Dim SelCol As String

' Selection.AutoFilter Field:=12, Criteria1:="AAA"
SelCol = InputBox("Enter The Column for the Coach You Want to Send
Letters To:!")
Set WB = Workbooks.Add
With WB
.Title = "Letters to Parents"
.Subject = "Grades"
.SaveAs Filename:="TxLabels.xls"
End With
' Workbooks.Open ("TxLabels.xls")
Set WB = ActiveWorkbook
Set NB = ActiveWorkbook
Set wk = ActiveSheet
' Set ws = Workbooks.Add(after:=Worksheets(Worksheets.Count))
WB.Columns(SelCol).Copy NB.Columns(1) ' ? Here is my problem at
this point
wk.Columns(3).Copy ws.Columns(2)
wk.Columns(5).Copy ws.Columns(3)
wk.Columns(7).Copy ws.Columns(4)
wk.Columns(8).Copy ws.Columns(5)
wk.Columns(9).Copy ws.Columns(6)

' ActiveCell.FormulaR1C1 = ""
' ChDir "C:\ExcelExp"

ActiveWorkbook.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

MsgBox "Class AAA Completed"

End Sub

I just cannot find examples of the objects I need to learn.

Thanks for any help

Len

Move Data From Active Sheet to New WorkBook
 

Tom;
Thanks for your help and recommendations. I hope the learning curve
is not too long. It seems like people are leaning to Excel more and more.

You were a great help. If you have any more suggestions as to how I
can continue to improve, I will follow them.

Thanks Again

Len

"Tom Ogilvy" wrote:

Sub ClassAAA()

Dim lastRow As Long
Dim WB As Workbook ' TxLabels.xls
Dim ws As Worksheet ' First sh in TxLabels.xls
Dim wk as Worksheet ' sheet with data
Dim SelCol As String

' Selection.AutoFilter Field:=12, Criteria1:="AAA"

' assume sheet with data is the activesheet when you run the macro

Set wk = ActiveSheet
SelCol = InputBox("Enter The Column for the Coach You Want to Send
Letters To:!")
Set WB = Workbooks.Add
With WB
.Title = "Letters to Parents"
.Subject = "Grades"
End With
set ws = Activesheet
wk.Columns(SelCol).Copy ws.Columns(1)
wk.Columns(3).Copy ws.Columns(2)
wk.Columns(5).Copy ws.Columns(3)
wk.Columns(7).Copy ws.Columns(4)
wk.Columns(8).Copy ws.Columns(5)
wk.Columns(9).Copy ws.Columns(6)

' ActiveCell.FormulaR1C1 = ""
' ChDir "C:\ExcelExp"

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

MsgBox "Class AAA Completed"

End Sub

--
Regards,
Tom Ogilvy


"Len" wrote:

This is only the third time in my life I have used Excel.

I am sure it is going to be a learning curve.

I have a huge SpreadSheet I need to reduce to a workable document. I had it
working between Active Sheeets but kept losing data.

I now realize that I must copy the selected information to a new Workbook,
but I am having trouble learning the proper code.

Here is my code so far, I have been working on it for two days so it may not
look very good!

Sub ClassAAA()

Dim lastRow As Long
Dim WB As Workbook
Dim NB As Workbook
Dim ws As Worksheet
Dim SelCol As String

' Selection.AutoFilter Field:=12, Criteria1:="AAA"
SelCol = InputBox("Enter The Column for the Coach You Want to Send
Letters To:!")
Set WB = Workbooks.Add
With WB
.Title = "Letters to Parents"
.Subject = "Grades"
.SaveAs Filename:="TxLabels.xls"
End With
' Workbooks.Open ("TxLabels.xls")
Set WB = ActiveWorkbook
Set NB = ActiveWorkbook
Set wk = ActiveSheet
' Set ws = Workbooks.Add(after:=Worksheets(Worksheets.Count))
WB.Columns(SelCol).Copy NB.Columns(1) ' ? Here is my problem at
this point
wk.Columns(3).Copy ws.Columns(2)
wk.Columns(5).Copy ws.Columns(3)
wk.Columns(7).Copy ws.Columns(4)
wk.Columns(8).Copy ws.Columns(5)
wk.Columns(9).Copy ws.Columns(6)

' ActiveCell.FormulaR1C1 = ""
' ChDir "C:\ExcelExp"

ActiveWorkbook.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

MsgBox "Class AAA Completed"

End Sub

I just cannot find examples of the objects I need to learn.

Thanks for any help

Len