Range, Columns, and Copy

[jwise]

I'm trying to copy a block of 4 columns (plus another) from one worksheet to another. I'm trying to do this the most efficient way, i.e. not use the clipboard. Although I can get it to work multiple ways, I'm unsure of the correct syntax.

wsRank.Columns(lastCol - 3).Copy wsNewP.Columns("F")
         wsRank.Columns(lastCol - 2).Copy wsNewP.Columns("G")
         wsRank.Columns(lastCol - 1).Copy wsNewP.Columns("H")
         wsRank.Columns(lastCol).Copy wsNewP.Columns("I")
         wsRank.Columns(lastCol - 4).Copy wsNewP.Columns("J")

I can do this by:

wsRank.Columns("AO:AR").Copy wsNewP.Columns("F:I")
         wsRank.Columns("AN").Copy wsNewP.Columns("J")

The problem is the "AO:AR" and "AN" will need to change. The "lastCol" variable allows me to calculate the beginning/ending column numbers. How do I do this? I understand it will take two statements, but any statement which compiles gets the 1004 error if it has multiple columns. I am using Excel 2010.

I also tried:

wsRank.Columns(lastCol - 3, lastCol).Copy wsNewP.Columns("F:I")

which does not work (1004 error).

Thanks in advance.

[mancubus]

With wsRank
    LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
    LastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    wsNewP.Range("F1").Resize(LastRow, 4).Value = .Range(.Cells(1, LastCol - 3), .Cells(LastRow, LastCol)).Value
        'resize F1 to extend it by "LastRow" rows and 4 columns (LastCol - 3,  LastCol - 2, LastCol - 1, LastCol)
    wsNewP.Range("J1").Resize(LastRow, 1).Value = .Range(.Cells(1, LastCol - 4), .Cells(LastRow, LastCol - 4)).Value
        'resize J1 to extend it by "LastRow" rows and 1 column (LastCol - 4)
End With

[jwise]

Thank you for your reply.

I'm sorry for the confusing terminology I used. "lastCol" is the integer number of columns in the "rank" worksheet. This worksheet has a variable number of columns, and the question is how do I pick up the last five columns which contain statistics for all the "data" columns. These columns are moved to a new worksheet ("wsNewP"). To make this case work, I used the range object ("AO:AR"), but this only works for one particular number of data columns. I'm trying to make it work for any number of columns. And I'd like to avoid using the clipboard. The macro already runs a long time due to extensive print formatting of the new worksheets constructed (code I purposefully left out because it obscures the problem).

Perhaps I've misunderstood your code, but I interpret it as finding the last column. There are three worksheets in use by this macro, and I already know the last column in rank worksheet.

[mancubus]

post your workbook with sample data and include before and after macro scenarios in it please.

[jwise]

I can't post the workbook because it is huge and full of confidential financial information. There are probably fifty or more macros, and the macro in question uses functions and subroutines that really make it unreasonable to trace their execution together. I hope you do not think I'm being uncooperative. The workbook is also huge with 80+ worksheets, including a couple of 4,000+ row worksheets. It would take a while to wade through this!

Here is all of the code in the loop in question:

For i = 3 To lastCol - 6
         propCode = wsRank.Cells(1, i).Value
         Set wsOldP = Sheets(propCode)
         wsOldP.Copy After:=Sheets(Sheets.count)
         Set wsNewP = ActiveSheet
         wsNewP.Name = newPre & "_" & propCode
         With wsNewP.Columns(16)                            'Change to value
              .Value = .Value
         End With        
         wsNewP.Range("C:O").Delete xlShiftToLeft           'Delete months
         wsRank.Columns(i).Copy wsNewP.Columns(5)        'Copy rank
         wsNewP.Range("E1").Value = "Rank"                    'Title
         wsNewP.Range("E1").ClearComments                   'Clear comments
         wsRank.Columns("AO:AR").Copy wsNewP.Columns("F:I")
         wsRank.Columns("AN").Copy wsNewP.Columns("J")
         Call FmtTitle2(wsNewP, propCode)                      'Format print
    Next i

This code is processing the data columns of wsRank which begin in 3 and end at lastCol - 6. The last five of these columns (the other is for spacing) are statistics derived from the data. The top row of each of these data columns has the "property code" which names the "property sheet". The loop takes the property code, gets the property sheet, creates a new worksheet and names it, then puts data from the property sheet (wsOldP) along with the column from the rank sheet (wsRank) in the new sheet (wsNewP), and finally adds the statistics from the last five columns of the rank sheet, but not in the same order. This nasty is to make it look pretty! The reason for deleting those twelve columns is to eliminate the detail records (monthly values) which leaves only the yearly data. This "rank" worksheet usually does not have every property in it which is why there is a variable number of columns.

To copy these last five columns between sheets, I tried to use two copy statements: the first for the contiguous last four and one for the remaining column. I had no difficulty with the single column copy using the "wsRank.Columns(6).Copy" construction, but if I tried to copy multiple columns, regardless of which construct I used (Range or Columns), it either got a compile error or 1004 execution error.

So, if you look at third and second-last loop statements, I want to change the "Range" to use variable "Columns" without resorting to one column at a time

Sorry for the verbosity. Let me assure you I've done many searches and tried several combinations using Range and Columns.

[mancubus]

hi there. i believe nobody is after your confidential data. this thread contains a sample data file: http://www.vbaexpress.com/forum/show...tiple-criteria make a xlsx copy of your workbook. keep a number of rows of data which will help us understand your table structure. replace your real data with fake data. for example, if a column contains customer names change them to CustomerA, CustomerB, etc. you can leave blank as well, if not necessary for coding.

[mancubus]

my understanding of your requirement with sample columns. please correct where necessary:

(1) wsRank: lastCol is 16 (Column P), so lastCol - 6 is 10 (Column J). (thus Column K is Blank, Columns L thru P are the rightmost 5 columns that you want to copy to all new worksheets.)

(2) take worksheet names from C1 thru J1 cells in wsRank.

(3)create a copy of these worksheets.

(4) rename the copies by adding a prefix which is stored in a variable named "newPre" and "_" to old worksheet names.

(5) convert the formulas in Column P to values in these newly created worksheets.

(6) delete columns C-O of these newly created worksheets. (Column P becomes Column C now.)

(7) copy related column (column index from loop's counter) from wsRank to column E of these newly created worksheets. Change E1 value to "Rank" and clear its comment.

(8) copy rightmost 4 columns of wsRank to columns F-I in these newly created worksheets.

(9)copy 5th column from last of wsRank to column J in these newly created worksheets.

[jwise]

Thank you again for your patience. I apologize for doing an inadequate job of explaining what this loop does.

There are three worksheets involved. "wsOldP" is the "old" property sheet. This worksheet lists every income/expense code, how much income/expense in each of the last 12 months, and the sum of those months for a yearly total. Next it has a "per user" calculation which divides this annual cost by the number of users. Thus if I have two entities with different numbers of users, I can calculate their values on a per user basis. These statistics are values, and not formulas. On the "wsRank" sheet, I have where this entity is rated (ranked) for each of these income/expense codes. Thus the #1 ranked entity for a given expense code would be the lowest expense per user After this. I have five statistics which are things like average, median, standard deviation, and count (not every entity has an income/expense in every code)..

The goal is to create a new "summary" worksheet. This summary sheet ("wsNewP") is created by copying the old property sheet (wsOldP) and deleting the monthly data. Before I can delete the monthly data, I have to change the "annual" number from a formula to a "value", otherwise the number disappears. After this is done, I copy the ranking for the expense into a column (from wsRank), then the stats, also from wsRank.

The result of all of this has what the entity spent, a per user spend amount, a rank versus its fellow entities, and the statistics for this particular expense.

The code works, but I didn't want 4 copy statements (to move those stats from contiguous columns on wsRank to wsNewP when I knew it could be done with a single statement. The fifth column is not contiguous and I didn't want to play Union. It works fine if I use

wsRank.Range("AO:AR").Copy

, but the code depends on the number of entities in the comparison. The whole point is to evaluate expenses multiple ways to gain insight into why a particular entity is spending. I did extensive searches trying to figure out the syntax of "multi-column copy".

I expected to use "wsNewP.Columns(var1, var2) = wsRank.Columns(var3, var4) where "var" is an Integer column number variable. But I could never get anything to compile which wouldn't get the 1004 error. Thus I switched to various Range constructs.

All I'm really asking is how to copy data from one worksheet to another given only the column numbers. I can use "Columns" if it is a single column, and Range if I used "A1" nomenclature, but I can't figure out how to do a multi-column copy with only the column numbers involved. I do not want to use "Select" or "Paste".

[mancubus]

you're welcome.

does not Next i statement repeat wsOldP.Copy After:=Sheets(Sheets.count)?

i understand columns AO-AR and AN are static and you want to use their number equivalents.

i think you can play with the following. LastCol is AR (or col num 44), LastCol -3 is AO (or col num 41), LastCol -4 is AN (or col num 40). you can replace LastCol with 44, etc.

With wsRank
    LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
    'LastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
End With

For i = 3 To LastCol - 6
    propCode = wsRank.Cells(1, i).Value
    Set wsOldP = Sheets(propCode)
    wsOldP.Copy After:=Worksheets(Worksheets.Count)
    Set wsNewP = ActiveSheet
    With wsNewP
        .Name = newPre & "_" & propCode
        .Columns(16).Value = .Columns(16).Value 'Change to value
        .Range("C:O").Delete xlShiftToLeft 'Delete months
        With .Range("E1")
            .Resize(LastRow).Value = wsRank.Columns(i).Resize(LastRow).Value 'Copy rank
            .Value = "Rank" 'Title
            .ClearComments 'Clear comments
        End With
        wsRank.Activate 'this handles the error from referring to range with column indexes as in Range(Columns(LastCol - 3), Columns(LastCol))
        .Range("F1").Resize(LastRow, 4) = wsRank.Range(Columns(LastCol - 3), Columns(LastCol)).Resize(LastRow).Value
        .Range("J1").Resize(LastRow).Value = wsRank.Columns(LastCol - 4).Resize(LastRow).Value
    End With
    Call FmtTitle2(wsNewP, propCode) 'Format print
Next i

[jwise]

It's been a contorted ride, but I think the "wsRank.Activate" is the real cure for my problem. It'll take a while to fully test this. Thank you so much.

I have confidence your comment on the "activate" is accurate, but I've never seen anything in print that would have educated me on this issue. This is a genuine case of lack of education.

Thanks again.

[mancubus]

i really dont know. i noticed it when testing with a sample file. when wsRank was active, it didnt throw error.

[jwise]

Isn't it comforting o know that neither of us knew to do this in the first place? There ought to be some way to prevent this kind of thing!

Thanks again for your patience. I wasn't worried about anybody on VBAX using the data-- just the corporate types here would have an objection... as if you might recognize the entities. It's like a service bureau: we have to protect the customers "sensitive financial information".

Thanks again.

[mancubus]

just kidding.

you are welcome.