Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    creating column size template for extract (2003)

    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

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: creating column size template for extract (200

    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: creating column size template for extract (2003)

    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

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: creating column size template for extract (200

    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: creating column size template for extract (200

    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

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: creating column size template for extract (200

    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: creating column size template for extract (200

    Then you only need the line of code:
    <pre>Columns.AutoFit</pre>


    This will autofit all the columns...

    Steve

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: creating column size template for extract (200

    The results you want can be achieved by:

    1. <LI>Selecting all cells; then
      <LI>double clicking on the line separating any column header
    H.T.H.
    Attached Images Attached Images
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •