I have a worksheet with 243 Columns. I have a Macro that analyzes data from
a fare number of columns.

I just want to create a new worksheet with just the 21 columns I need to
print a mail merge document from.

All my code is working except I cannot find out how to create the worksheet
in the macro and then display the merge form letters. I have seen other
people do this several years ago, so I know it can be done.

A little green, only the third time I have worked with Excel, I am a real
time Macro Assembler Programmer.

Thanks in Advance

Len

Normally Word is used for mailmerges with Excel as the source database but see
John Walkenbach's site for mailmerging with Excel alone.

http://www.j-walk.com/ss/excel/tips/tip92.htm


Gord Dibben MS Excel MVP


On Wed, 4 Jul 2007 16:06:00 -0700, Len <(E-Mail Removed)> wrote:

>I have a worksheet with 243 Columns. I have a Macro that analyzes data from
>a fare number of columns.
>
>I just want to create a new worksheet with just the 21 columns I need to
>print a mail merge document from.
>
>All my code is working except I cannot find out how to create the worksheet
>in the macro and then display the merge form letters. I have seen other
>people do this several years ago, so I know it can be done.
>
>A little green, only the third time I have worked with Excel, I am a real
>time Macro Assembler Programmer.
>
>Thanks in Advance
>
>Len

Dim sh as Worksheet
set sh = Worksheets.Add(after:=Worksheets(worksheets.count))

is how you add a worksheet
--
Regards,
Tom Ogilvy


"Len" wrote:

> I have a worksheet with 243 Columns. I have a Macro that analyzes data from
> a fare number of columns.
>
> I just want to create a new worksheet with just the 21 columns I need to
> print a mail merge document from.
>
> All my code is working except I cannot find out how to create the worksheet
> in the macro and then display the merge form letters. I have seen other
> people do this several years ago, so I know it can be done.
>
> A little green, only the third time I have worked with Excel, I am a real
> time Macro Assembler Programmer.
>
> Thanks in Advance
>
> Len

Gord;

Thanks for getting back and the link. I will play with it a little
later. Most high school guidance departments are slowly but surely removing
Web Access. Along with the fact that they are cutting back on other software
tools we are having to learn new things.

I want to get a few thing resolved before I actually do the Mail
Merge.

What I have for Code so far is:

Sub ClassAAA()

Dim lastRow As Long
Dim SelCol as String

SelCol = InputBox("Enter Desired Column to Analyze!")
Selection.AutoFilter Field:=12, Criteria1:="AAA"

Workbooks.Add
ActiveCell.FormulaR1C1 = ""
ChDir "C:\ExcelExp"

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

MsgBox "Class AAA Completed"

End Sub

What I need to know is Move The Column I select SelCol and others:
Column C to B
E to C
I to F
II to G
etc.

3000 Lines of code go here!

Workbooks.Add
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

Once I get this new workbook created, I will worry about the Mail Merge.

Thanks for your assistance

Len

"Gord Dibben" wrote:

> Normally Word is used for mailmerges with Excel as the source database but see
> John Walkenbach's site for mailmerging with Excel alone.
>
> http://www.j-walk.com/ss/excel/tips/tip92.htm
>
>
> Gord Dibben MS Excel MVP
>
>
> On Wed, 4 Jul 2007 16:06:00 -0700, Len <(E-Mail Removed)> wrote:
>
> >I have a worksheet with 243 Columns. I have a Macro that analyzes data from
> >a fare number of columns.
> >
> >I just want to create a new worksheet with just the 21 columns I need to
> >print a mail merge document from.
> >
> >All my code is working except I cannot find out how to create the worksheet
> >in the macro and then display the merge form letters. I have seen other
> >people do this several years ago, so I know it can be done.
> >
> >A little green, only the third time I have worked with Excel, I am a real
> >time Macro Assembler Programmer.
> >
> >Thanks in Advance
> >
> >Len
>
>

Tom;

Dim SelCol as String
SelCol = InputBox("Enter the column you need to analyze")

Thanks for the reply. Did not notice a difference yet, however I
need to figure out how to move the Column that is entered by the operator
SELCOL to Column A of my new worksheet.

With so many columns all I need to work with is 21, this is the
reason I am copying the sheet.

I am just having trouble finding the code to do this?

Thanks Again

Len




"Tom Ogilvy" wrote:

> Dim sh as Worksheet
> set sh = Worksheets.Add(after:=Worksheets(worksheets.count))
>
> is how you add a worksheet
> --
> Regards,
> Tom Ogilvy
>
>
> "Len" wrote:
>
> > I have a worksheet with 243 Columns. I have a Macro that analyzes data from
> > a fare number of columns.
> >
> > I just want to create a new worksheet with just the 21 columns I need to
> > print a mail merge document from.
> >
> > All my code is working except I cannot find out how to create the worksheet
> > in the macro and then display the merge form letters. I have seen other
> > people do this several years ago, so I know it can be done.
> >
> > A little green, only the third time I have worked with Excel, I am a real
> > time Macro Assembler Programmer.
> >
> > Thanks in Advance
> >
> > Len

You said:
>I just want to create a new worksheet with just the 21 columns
then you said you were having a problem creating a worksheet. The code you
posted later creates new workbooks, so maybe you meant workbook and said
worksheet.

In any event, for a worksheet

Dim SelCol as String
Dim sh as Worksheet
Dim sh1 as Worksheet

SelCol = InputBox("Enter the column you need to analyze")
set sh1 = ActiveSheet
set sh = Worksheets.Add(after:=Worksheets(worksheets.count))
sh1.columns(SelCol).copy sh.Columns(1)

and so forth.

--
Regards,
Tom Ogilvy


"Len" wrote:

> Tom;
>
> Dim SelCol as String
> SelCol = InputBox("Enter the column you need to analyze")
>
> Thanks for the reply. Did not notice a difference yet, however I
> need to figure out how to move the Column that is entered by the operator
> SELCOL to Column A of my new worksheet.
>
> With so many columns all I need to work with is 21, this is the
> reason I am copying the sheet.
>
> I am just having trouble finding the code to do this?
>
> Thanks Again
>
> Len
>
>
>
>
> "Tom Ogilvy" wrote:
>
> > Dim sh as Worksheet
> > set sh = Worksheets.Add(after:=Worksheets(worksheets.count))
> >
> > is how you add a worksheet
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Len" wrote:
> >
> > > I have a worksheet with 243 Columns. I have a Macro that analyzes data from
> > > a fare number of columns.
> > >
> > > I just want to create a new worksheet with just the 21 columns I need to
> > > print a mail merge document from.
> > >
> > > All my code is working except I cannot find out how to create the worksheet
> > > in the macro and then display the merge form letters. I have seen other
> > > people do this several years ago, so I know it can be done.
> > >
> > > A little green, only the third time I have worked with Excel, I am a real
> > > time Macro Assembler Programmer.
> > >
> > > Thanks in Advance
> > >
> > > Len

I'm not Gord or Tom, but I'm confused about what you're doing.

Personally, I'd try to keep all the data in the same worksheet--separating the
data into different workbooks is a problem just waiting to happen (in my
experience).

Could you just hide the columns you don't need? Maybe with just 21 visible
columns, moving to column A won't be necessary????

Or insert a new column A and keep it as a place holder--copy the column (don't
move it) you need into column A. Then hide the original column.

But if you want to get a range from the user you can use something like:

Dim myRng as range
set myrng = nothing
on error resume next
set myrng = application.inputbox(Prompt:="select a cell",type:=8).cells(1)
on error goto 0

if myrng is nothing then
'user hit cancel, what should happen here?
else
msgbox myrng.entirecolumn.address
end if

======
ps. I've found it's much simpler to unhide a column than to try to get that
column back after it's been deleted.

Len wrote:
>
> Gord;
>
> Thanks for getting back and the link. I will play with it a little
> later. Most high school guidance departments are slowly but surely removing
> Web Access. Along with the fact that they are cutting back on other software
> tools we are having to learn new things.
>
> I want to get a few thing resolved before I actually do the Mail
> Merge.
>
> What I have for Code so far is:
>
> Sub ClassAAA()
>
> Dim lastRow As Long
> Dim SelCol as String
>
> SelCol = InputBox("Enter Desired Column to Analyze!")
> Selection.AutoFilter Field:=12, Criteria1:="AAA"
>
> Workbooks.Add
> ActiveCell.FormulaR1C1 = ""
> ChDir "C:\ExcelExp"
>
> ActiveWorkbook.SaveAs Filename:="C:\ExcelExp\NELabels.xls",
> FileFormat:= _
> xlNormal, Password:="", WriteResPassword:="",
> ReadOnlyRecommended:=False _
> , CreateBackup:=False
>
> MsgBox "Class AAA Completed"
>
> End Sub
>
> What I need to know is Move The Column I select SelCol and others:
> Column C to B
> E to C
> I to F
> II to G
> etc.
>
> 3000 Lines of code go here!
>
> Workbooks.Add
> 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
>
> Once I get this new workbook created, I will worry about the Mail Merge.
>
> Thanks for your assistance
>
> Len
>
> "Gord Dibben" wrote:
>
> > Normally Word is used for mailmerges with Excel as the source database but see
> > John Walkenbach's site for mailmerging with Excel alone.
> >
> > http://www.j-walk.com/ss/excel/tips/tip92.htm
> >
> >
> > Gord Dibben MS Excel MVP
> >
> >
> > On Wed, 4 Jul 2007 16:06:00 -0700, Len <(E-Mail Removed)> wrote:
> >
> > >I have a worksheet with 243 Columns. I have a Macro that analyzes data from
> > >a fare number of columns.
> > >
> > >I just want to create a new worksheet with just the 21 columns I need to
> > >print a mail merge document from.
> > >
> > >All my code is working except I cannot find out how to create the worksheet
> > >in the macro and then display the merge form letters. I have seen other
> > >people do this several years ago, so I know it can be done.
> > >
> > >A little green, only the third time I have worked with Excel, I am a real
> > >time Macro Assembler Programmer.
> > >
> > >Thanks in Advance
> > >
> > >Len
> >
> >

--

Dave Peterson

Tom;
You were right. I am just destroying data using the Worksheet
method. Assembler programmers have a hard time getting accustomed to object
programming. I purchased five books and feel like it was a waste of money.
I guess it is going to be a learning curve.

Here is what I have now. The line that end with the ? is where I am hanging
up.

Sub ClassAAA()

Dim lastRow As Long
Dim ws As Workbook
Dim wk 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 wk = ActiveSheet
Set ws = Workbooks.Add(after:=Workbook(Workbook.Count)) ?
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)

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

MsgBox "Class AAA Completed"

End Sub

Thanks Again

Len

"Tom Ogilvy" wrote:

> You said:
> >I just want to create a new worksheet with just the 21 columns
> then you said you were having a problem creating a worksheet. The code you
> posted later creates new workbooks, so maybe you meant workbook and said
> worksheet.
>
> In any event, for a worksheet
>
> Dim SelCol as String
> Dim sh as Worksheet
> Dim sh1 as Worksheet
>
> SelCol = InputBox("Enter the column you need to analyze")
> set sh1 = ActiveSheet
> set sh = Worksheets.Add(after:=Worksheets(worksheets.count))
> sh1.columns(SelCol).copy sh.Columns(1)
>
> and so forth.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Len" wrote:
>
> > Tom;
> >
> > Dim SelCol as String
> > SelCol = InputBox("Enter the column you need to analyze")
> >
> > Thanks for the reply. Did not notice a difference yet, however I
> > need to figure out how to move the Column that is entered by the operator
> > SELCOL to Column A of my new worksheet.
> >
> > With so many columns all I need to work with is 21, this is the
> > reason I am copying the sheet.
> >
> > I am just having trouble finding the code to do this?
> >
> > Thanks Again
> >
> > Len
> >
> >
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > Dim sh as Worksheet
> > > set sh = Worksheets.Add(after:=Worksheets(worksheets.count))
> > >
> > > is how you add a worksheet
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Len" wrote:
> > >
> > > > I have a worksheet with 243 Columns. I have a Macro that analyzes data from
> > > > a fare number of columns.
> > > >
> > > > I just want to create a new worksheet with just the 21 columns I need to
> > > > print a mail merge document from.
> > > >
> > > > All my code is working except I cannot find out how to create the worksheet
> > > > in the macro and then display the merge form letters. I have seen other
> > > > people do this several years ago, so I know it can be done.
> > > >
> > > > A little green, only the third time I have worked with Excel, I am a real
> > > > time Macro Assembler Programmer.
> > > >
> > > > Thanks in Advance
> > > >
> > > > Len

There is no concept of after:=Workbooks(workbooks.count)

that is for worksheets that have a order (tab order) within a single
workbook.

You already had code that added a new workbook.

I gave you sample code to add a sheet within the activeworkbook.

Some how you have mixed the two.

--
Regards,
Tom Ogilvy


"Len" wrote:

> Tom;
> You were right. I am just destroying data using the Worksheet
> method. Assembler programmers have a hard time getting accustomed to object
> programming. I purchased five books and feel like it was a waste of money.
> I guess it is going to be a learning curve.
>
> Here is what I have now. The line that end with the ? is where I am hanging
> up.
>
> Sub ClassAAA()
>
> Dim lastRow As Long
> Dim ws As Workbook
> Dim wk 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 wk = ActiveSheet
> Set ws = Workbooks.Add(after:=Workbook(Workbook.Count)) ?
> 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)
>
> ActiveWorkbook.SaveAs Filename:="C:\ExcelExp\TxLabels.xls",
> FileFormat:= _
> xlNormal, Password:="", WriteResPassword:="",
> ReadOnlyRecommended:=False _
> , CreateBackup:=False
>
> MsgBox "Class AAA Completed"
>
> End Sub
>
> Thanks Again
>
> Len
>
> "Tom Ogilvy" wrote:
>
> > You said:
> > >I just want to create a new worksheet with just the 21 columns
> > then you said you were having a problem creating a worksheet. The code you
> > posted later creates new workbooks, so maybe you meant workbook and said
> > worksheet.
> >
> > In any event, for a worksheet
> >
> > Dim SelCol as String
> > Dim sh as Worksheet
> > Dim sh1 as Worksheet
> >
> > SelCol = InputBox("Enter the column you need to analyze")
> > set sh1 = ActiveSheet
> > set sh = Worksheets.Add(after:=Worksheets(worksheets.count))
> > sh1.columns(SelCol).copy sh.Columns(1)
> >
> > and so forth.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Len" wrote:
> >
> > > Tom;
> > >
> > > Dim SelCol as String
> > > SelCol = InputBox("Enter the column you need to analyze")
> > >
> > > Thanks for the reply. Did not notice a difference yet, however I
> > > need to figure out how to move the Column that is entered by the operator
> > > SELCOL to Column A of my new worksheet.
> > >
> > > With so many columns all I need to work with is 21, this is the
> > > reason I am copying the sheet.
> > >
> > > I am just having trouble finding the code to do this?
> > >
> > > Thanks Again
> > >
> > > Len
> > >
> > >
> > >
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Dim sh as Worksheet
> > > > set sh = Worksheets.Add(after:=Worksheets(worksheets.count))
> > > >
> > > > is how you add a worksheet
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Len" wrote:
> > > >
> > > > > I have a worksheet with 243 Columns. I have a Macro that analyzes data from
> > > > > a fare number of columns.
> > > > >
> > > > > I just want to create a new worksheet with just the 21 columns I need to
> > > > > print a mail merge document from.
> > > > >
> > > > > All my code is working except I cannot find out how to create the worksheet
> > > > > in the macro and then display the merge form letters. I have seen other
> > > > > people do this several years ago, so I know it can be done.
> > > > >
> > > > > A little green, only the third time I have worked with Excel, I am a real
> > > > > time Macro Assembler Programmer.
> > > > >
> > > > > Thanks in Advance
> > > > >
> > > > > Len