Formatting into new worksheet

[StylinEric]

Hey!!

My first time posting here, but I've been here a few times. I reall
appreciate everyone's help.

Anyways, my question....

I have a large worksheet I'm working on with a dynamic number of rows.
I want to be able to copy this worksheet into a new one at the sam
time fomat the new worksheet based upon some criteria.

For example if there is no data in the column with column headin
"salutation" (otherwise known as column A) I want to delete that ro
when it is copied over. If there is no data in that column I don'
want to cluttering up the new one

At the same time I want to delete some columns.
For example there is a column heading "phone" (column E) that I don'
want in the new sheet

I also want to rearange some columns and change their size
For example I want to move the state column from its current poistio
as "column D" and move it to "column G" while at the same time makin
its size smaller.

I know there is alot to do out there, but I know some guru knows th
answer...or atleast I hope so. Thanks in advance for all your help!

Eri

 

[David McRitchie]

Hi Eric,
Most of these things you can do by recording a macro,
some of the coding has been included as parts of the steps
that you can compare to what is generated when you record a
macro.
..
1) create a copy, which becomes the active sheet

'Copy another sheet similar to using a template
Worksheets("MyBeginning").Copy after:=Worksheets(Worksheets.Count)

2) rename the active sheet to whatever you want

ActiveSheet.Name = newName

3) move the columns around, technique is hold shift and drag
the column border to where you want it.

'--http://www.mvps.org/dmcritchie/excel/fillhand.htm#mouse

4) enlarge the columns you want to enlarge
'--http://www.mvps.org/dmcritchie/excel/fillhand.htm#mouse

5) to delete the rows that have nothing (no data, no formulas, no spaces)
in column A you can add the following to your macro

'--http://www.mvps.org/dmcritchie/excel/delempty.htm
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Is that sufficient for you to create and complete your macro.

 

[StylinEric]

David,

Thanks for the help this worked great

 

[David McRitchie]

Hi Eric,
Good, it's frequently a question whether someone needs
a line of code or an entire macro. Glad the little pieces
of code were sufficient. I guess currently the most snipets
are in my http://www.mvps.org/dmcritchie/excel/sheets.htm#sheetcoding