Width Formatting is lost when I Copy to new Columns

  • When I copy data from one range, (E1:I99) for instance, to the ActiveCell, (P1), the data is copied but the formatting, in particular the Width, is lost ... I therefore end up with "#####" all over the place.


    Basically, I suppose I am asking how to update/replace the following code, which copies data but not width information, to copy the data AND keep the width information :


    Code
    For EmployLoop = 1 To LastEmp 
        Range("E1", "I" & LastEnh).Copy 
        ActiveCell.PasteSpecial 
        ActiveCell.Offset(0, 5).Activate 
        Range("K1", "O" & LastEnh).Copy 
        ActiveCell.PasteSpecial 
        ActiveCell.Offset(0, 5).Activate 
    Next EmployLoop
  • Re: Width Formatting is lost when I Copy to new Columns


    Try this at the end of your run.

    Code
    Cells.Select
        Cells.EntireColumn.AutoFit
        Range("A1").Select

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: Width Formatting is lost when I Copy to new Columns


    Autofitting the columns after the fact is certainly one method...


    The other would be to just copy/paste the entire column if possible. For instance instead of specifying E1:I?, use E:I.


    Not sure why you're even doing that loop activity in the first place. Certainly seems inefficient to process multiple copy/paste actions like that; probably slow too. I'd try replacing that whole loop action with just...


    Code
    Range("E:I").Copy Range("K1")


    Now, if you're using that pastespecial for anything particular (values?) it would be only a slight modification. For instance, maybe you want just the values and formats...

    Code
    Range("E:I").Copy
        With Range("K1")
            .PasteSpecial (xlPasteValues)
            .PasteSpecial (xlPasteFormats)
        End With


    Alternatively... you could use that autofit method on the copied data after the fact, in which case you may not want to apply it to all cells (which implies all columns) as was suggested. I'd probably just apply it to the K:O output range; but that's your call.


    Code
    Range("K:O").EntireColumn.AutoFit

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Width Formatting is lost when I Copy to new Columns


    Thanks for your input, I'll play around and see which works best for me ...


    Aaron, basically I have a skeleton Monthly layout where Columns E thru I are totals and Column D is a total of them (row by row), the same for Columns K thru O, with J as the totals Column.


    These are all Total Cells for individual users and various projects, and there could be any number of users, which is why I am copying E thru I and K thru O for each user (with Formulae to be added later), that is what the loop is doing ... as you point out, it probably is very inefficient, but I am very inexperienced at VB & my 25 year background as a COBOL programmer tends to have me approaching ideas and problems from the wrong angle !!!!


    If you can sugggest a better way than the loop then I'm all ears, I want to learn as much of this stuff as I possibly can, just in case COBOL really does die a final death :yikes: !!!!!

  • Re: Width Formatting is lost when I Copy to new Columns


    Quote from vodkasoda

    If you can sugggest a better way than the loop then I'm all ears, I want to learn as much of this stuff as I possibly can, just in case COBOL really does die a final death :yikes: !!!!!


    I already suggested that you replace the entire loop with a single line of code.

    Code
    Range("E:I").Copy Range("K1")

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Width Formatting is lost when I Copy to new Columns


    Quote from Aaron Blood

    I already suggested that you replace the entire loop with a single line of code.

    Code
    Range("E:I").Copy Range("K1")


    I'm sorry, but I don't see how that replaces the loop, which occurs once for every Employee ?!?


    I used your suggestion of copying the entire Column and using the 2 PasteSpecial commands and it works fine, thank you, but I still had to code that within the Loop of Employees, which can be from 1 to 24 (then I run out of Columns, something I will consider when the rest of it works !!!) ...

  • Re: Width Formatting is lost when I Copy to new Columns


    Wait, the variable controls the number of rows to copy, does it not?


    I'm just saying copy every row in the column... what am I missing?


    Oh wait a minute... I'm looking at it again...



    I see, you're doing something slightly different than I thought.
    What's the activecell when the loop starts? ...or is it just whatever the user has selected?

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Width Formatting is lost when I Copy to new Columns



    Sorry, I realised a little while ago that I should have included the ActiveCell !!! It's P1, the first Cell after the TOTAL Columns ...


    Ignoring the first 3 Columns, Column D is a total for Columns E thru I, then Column J is a total for K thru O ... I then copy the non-total Columns E thru I (5 Columns) to the ActiveCell, which is P1, & reset the ActiveCell 5 to the right, where I then copy the second set of non-total columns, K thru O & again reset the ActiveCell 5 to the right ...


    I loop through these actions for each Employee ... having done a little more work now, I don't see how this could be done differently anyhow, as I use the EmployLoop variable to pick up the Employee name and rate from another Range ...

  • Re: Width Formatting is lost when I Copy to new Columns


    Alright, I'm with ya now...


    Yeah a loop is required, although we can probably clean it up a bit and consolidate the two copy actions per loop to one and avoid the activation of cells inside the loop.


    This would be a little quicker:

    Code
    Set OutCell = Range("P1")
        Set SrcCell = Range("E:I, K:O")
        LastEmp = 5
        
       'EmployLoop
        n = Intersect(SrcCell, SrcCell.Rows(1).EntireRow).Cells.Count
        For i = 1 To LastEmp
            r = 0: c = (i - 1) * n
            SrcCell.Copy OutCell.Offset(r, c)
        Next i


    Now if you want to talk to me about that LastEmp variable, we can probably have the macro detect that value instead of hard coding it.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Width Formatting is lost when I Copy to new Columns


    Wow, please allow me to disappear with a pencil & paper for a while to understand that logic !!!


    The more I see expert written VBA, the more I am blown away by how much can be done by so little code !!!

  • Re: Width Formatting is lost when I Copy to new Columns


    Sorry, I can't get my head around that INTERSECT statement at all !!!


    I understand the loop and how it is copying the 2 separate ranges and is using the incrementing OFFSET, and also that the increment is the value returned from the INTERSECT statement ...


    How does the INTERSECT statement arrive at 10, the number of Columns being copied each time, when it referes to Rows and Cells ?!?!?


    BTW, I'm very impressed :thanx: !!!!!!!!

  • Re: Width Formatting is lost when I Copy to new Columns


    You're referring to this line...

    Code
    n = Intersect(SrcCell, SrcCell.Rows(1).EntireRow).Cells.Count


    The INTERSECT method returns the intersecting range of two or more ranges.


    You are correct, the goal was to return a count of the columns (n) to use as an offset. Normally, I'd do that by simply using something like:

    Code
    Range("E:I").Columns.Count


    However, in this case I consolidated the copy action to a single non-contiguous range reference:

    Code
    Range("E:I, K:O")


    The downside of doing this is that when you have a non-contiguous range reference, the following code...

    Code
    Range("E:I, K:O").Columns.Count


    ...would only return the columns in the first area of the range (E:I columns = 5).


    What's kinda interesting is that the Cells method doesn't have the problem with non-cont ranges. If you feed a non-cont range and ask for the cell count it gives you the total cell count for all areas, not just the count of the first area. So, I used the Intersect method to just slice off the first row and then with that I was able to get a count of the cells. Since it's just one row, the count of cells is the same as the count of columns.


    ...now all that said. It was just the idea I happened to come up with at the time. After thinking about it a bit, I could've accomplished the same task with the following shorter (probably slightly less confusing) code.


    Code
    n = SrcCell.Cells.Count / 65536


    The only problem with this bit of code is that the row limit of Excel is scheduled to increase soon. So the macro would fail as Excel is upgraded. Another option would be to make the divisor a variable, perhaps something like this...

    Code
    n = SrcCell.Cells.Count / SrcCell.Columns(1).Cells.Count


    ...so yes, maybe this is a slightly less complicated way of doing it. Granted, it's making an assumption about the ranges being the same size row-wise. I guess if I had to do it with non-cont ranges with different row dimensions, I'd probably use the intersect approach and maybe adjust it to use EntireColumn. Like this:

    Code
    x = Intersect(SrcCell.EntireColumn, SrcCell.Rows(1).EntireRow).Cells.Count


    ...this last one is probably the safest method.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!