creating column size template for extract (2003)

Topic

New Reply

Hi,

I use monthly extracts from a web-based program. Starting next month there is going to be a change in the online software and the extracts coming out will look different. All of the columns will have a standard width of 14.29. Some columns only contain one or 2 digits of data. Is it possible to setup a template with the correct column widths and then apply it to each monthly extract? Some of the extracts have over 100 rows of columns that I would manually have to re-size each month to make viewing the data easier. Generally I use pivot tables or Crystal Reports to get my data out, however there are usually several occasions each month that I need to go into the extract and filter or search for specific details.

Thanks for any suggestions

capri

Reply | Quote

Replies

There are lots of ways and it depends how much VBA you know. Recording the format painter as a Macro may well be enough.
Extensions could involve opening the template and target sheets specifically, but again, a recorded Macro would do 90% of that directly

Sub Doit
Columns(“Sheet1!A:G”).Select
Selection.Copy
Columns(“Sheet2!A:G”).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Reply | Quote

Thanks Andrew and Steve,

I guess I will try opening one and recording a macro as I re-size each column, then applying it to each new extract. I’m not very good with VBA, just haven’t had the time to learn much, but hopefully the macro recorder should work. I can alter other’s macros to suit my data, but don’t have enough understanding of what the various commands do to create my own. I don’t tend to use the macro recorder much as it tends to pick up cell addresses, which in most cases I don’t want, but for this purpose, should not impact on the result.

In the new program all the columns are 14 and I want to make most much smaller. Sometimes I just draw a blank at figuring out what to do, (a senior moment). Thanks for pointing me in the right direction.

capri

Reply | Quote

Do you want to autofit all the columns or do you have particular column widths in mind?

If the latter, waht columns widths do you want for each column?

Steve

Reply | Quote

For the most part I want to adjust the column to the approximate width of the contents, so when scrolling through or filtering I can see what I am looking for. Eg Surname column needs to be at least 8.5 wide, while gender column only needs to be 3.5 wide as it contains either M or F. The previous program exported everything at 8.43 and I just dragged the edges of a column to widen or shrink it’s size. The majority could be left at 8.43. With the new program exporting all columns at 14 wide, it just makes viewing to awkward as you are continually scrolling and you can’t see as much on the screen at one time. I could block all the columns and resize to 8.43 and continue as I did in the past.

I just thought if I could find a way to get them all right once, and just copy that format each time I take an extract, it would make life so much easier. Generally I find that if you think of something you are doing as boring and repititious, then there is an easier way to do it. It’s just a question of figuring out what the easier way is. This forum is great, because I’ve learned so many time saving techniques just from reading other people’s posts. I was hoping someone else had already had and solved a similar problem, and could let me know how they did it.

capri

Reply | Quote

Then you only need the line of code:

Columns.AutoFit

This will autofit all the columns…

Steve

Reply | Quote

The results you want can be achieved by:

1. Selecting all cells; then
2. double clicking on the line separating any column header

H.T.H.

Reply | Quote

I am not exactly sure what you after. Manually you can set all the columns to 14.29 by just selecting all the columns (clicking on the box above the “row number 1” and to the left of the “col A” will select all. Then Format – Column – width and type in 14.29 in the box.

You can “eyeball” a width if desired by after selecting all the columns you manually narrow/widen any column and they will all be adjusted.

If you want to autofit all the columns (narrow columns with few items and widen those with more), after selecting you can use Format – column – Autofit selection.

You can also loop through all the columns in a macro changing the width of each one to some designated size.

If you need more details, could you elaborate on what you need…
Steve

Reply | Quote