Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I can't figure out why these lines don't work
Thanks for all your help. This group is awesome. I can't figure out why these macro lines work: Worksheets("DP").Range("b5:b54").Copy Worksheets("Complete List").Range("a1:a50").PasteSpecial and these, attempting the same exact job, don't: Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy Worksheets("Complete List").Range(Cells(ccc * 50 + 1, _ 1),Cells(ccc + 1 * 50, 1)).PasteSpecial "i" is a variable in a for/next loop and in the current line equals 2 "ccc" is a variable also that is currently equal to 0. I keep getting an error message with the top line, Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy i dimensioned i and ccc as integers and both act as counters. I'm trying to copy several columns of data from one sheet into one large column of data into another sheet. I don't want to cut and paste each individual column because eventually the sheets will change and there will be more columns. i must have some syntax incorrect. thank you again, JasonK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I can't figure out why these lines don't work
Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy
Worksheets("Complete List").Range(Cells(ccc * 50 + 1, _ 1),Cells(ccc + 1 * 50, 1)).PasteSpecial **ALL** of the Cells reference are pointing to the ActiveSheet, not to the Worksheet references the Range properties that contain them are pointing to. You would need to add the correct worksheet reference for each Cells function call in order to guarantee they referred to the correct cell. For example, the last Cells reference.... Cells(ccc+1*50,1)... would need to become Worksheets("Complete List").Cells(ccc + 1 * 50, 1) in order to point to the worksheet I think you mean it to point to (and you would have to do the same thing for the other three of them). -- Rick (MVP - Excel) "JasonK" wrote in message ... Thanks for all your help. This group is awesome. I can't figure out why these macro lines work: Worksheets("DP").Range("b5:b54").Copy Worksheets("Complete List").Range("a1:a50").PasteSpecial and these, attempting the same exact job, don't: Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy Worksheets("Complete List").Range(Cells(ccc * 50 + 1, _ 1),Cells(ccc + 1 * 50, 1)).PasteSpecial "i" is a variable in a for/next loop and in the current line equals 2 "ccc" is a variable also that is currently equal to 0. I keep getting an error message with the top line, Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy i dimensioned i and ccc as integers and both act as counters. I'm trying to copy several columns of data from one sheet into one large column of data into another sheet. I don't want to cut and paste each individual column because eventually the sheets will change and there will be more columns. i must have some syntax incorrect. thank you again, JasonK |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I can't figure out why these lines don't work
Rick, I'm trying to copy and paste a range of cells, not just one cell. worksheets("Complete List").cells(5,i), Cells(54,i).copy gagged. i'm trying to copy the range of cells B5:B54 to another sheet A1:A50 Please take another look. The worksheets("Complete List").Range(cells(a,a),cells(b,b)) works in other macros. I don't understand why it wont work here. JasonK On Thu, 23 Jul 2009 03:24:46 -0400, "Rick Rothstein" wrote: Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy Worksheets("Complete List").Range(Cells(ccc * 50 + 1, _ 1),Cells(ccc + 1 * 50, 1)).PasteSpecial **ALL** of the Cells reference are pointing to the ActiveSheet, not to the Worksheet references the Range properties that contain them are pointing to. You would need to add the correct worksheet reference for each Cells function call in order to guarantee they referred to the correct cell. For example, the last Cells reference.... Cells(ccc+1*50,1)... would need to become Worksheets("Complete List").Cells(ccc + 1 * 50, 1) in order to point to the worksheet I think you mean it to point to (and you would have to do the same thing for the other three of them). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I can't figure out why these lines don't work
Hi Jason,
It's a trap for the uninitiated. You need to specify the worksheet name for each of the cells like this. (Not required if activesheet) Worksheets("DP").Range(Worksheets("DP").Cells(5, i), _ Worksheets("DP").Cells(54, i)).Copy or better still use with (Note the . before each Cells tying them to the worksheet. With Worksheets("DP") .Range(.Cells(5, i), .Cells(54, i)).Copy End With Also when pasting it is only necessary to identify the first cell and as you are pasting all of the data in one column then you could use code something like this. Insert a column header of your choice before starting any copy/paste operation with code like this. With Worksheets("Complete List") .Cells(1, 1) = "My Col Head" End With Use the following for the paste operation. With Worksheets("Complete List") .Cells(.Rows.Count, 1) _ .End(xlUp).Offset(1, 0).PasteSpecial End With -- Regards, OssieMac "JasonK" wrote: Thanks for all your help. This group is awesome. I can't figure out why these macro lines work: Worksheets("DP").Range("b5:b54").Copy Worksheets("Complete List").Range("a1:a50").PasteSpecial and these, attempting the same exact job, don't: Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy Worksheets("Complete List").Range(Cells(ccc * 50 + 1, _ 1),Cells(ccc + 1 * 50, 1)).PasteSpecial "i" is a variable in a for/next loop and in the current line equals 2 "ccc" is a variable also that is currently equal to 0. I keep getting an error message with the top line, Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy i dimensioned i and ccc as integers and both act as counters. I'm trying to copy several columns of data from one sheet into one large column of data into another sheet. I don't want to cut and paste each individual column because eventually the sheets will change and there will be more columns. i must have some syntax incorrect. thank you again, JasonK |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I can't figure out why these lines don't work
It probably works in other macros because you are not referencing cells on *other* worksheets. If you run a macro with Cells, Range, Columns, Rows property calls in it, they **automatically** reference the ActiveSheet **IF** you don't provide an explicit sheet reference. So, for this code that you posted originally, namely this...
Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy Worksheets( _ "Complete List").Range(Cells(ccc * 50 + 1, 1), _ Cells(ccc + 1 * 50, 1)).PasteSpecial ....and assuming you ran it from the worksheet named "DP", Excel interprets it like this... Worksheets("DP").Range(Worksheets("DP").Cells(5, i), Worksheets("DP"). _ Cells(54, i)).Copy Worksheets("Complete List"). _ Range(Worksheets("DP").Cells(ccc * 50 + 1, 1), _ Worksheets("DP").Cells(ccc + 1 * 50, 1)).PasteSpecial In particular, notice this part of the code... Worksheets("Complete List").Range( _ Worksheets("DP").Cells(ccc * 50 + 1, 1), _ Worksheets("DP").Cells(ccc + 1 * 50, 1)) Do you see how the Range property is referencing the worksheet named "Complete List" whereas the included Cells references are not... that is why your code line doesn't work. None of this is a problem if the code only references a single worksheet and, when you run the macro, that worksheet is the ActiveSheet. -- Rick (MVP - Excel) "JasonK" wrote in message ... Rick, I'm trying to copy and paste a range of cells, not just one cell. worksheets("Complete List").cells(5,i), Cells(54,i).copy gagged. i'm trying to copy the range of cells B5:B54 to another sheet A1:A50 Please take another look. The worksheets("Complete List").Range(cells(a,a),cells(b,b)) works in other macros. I don't understand why it wont work here. JasonK On Thu, 23 Jul 2009 03:24:46 -0400, "Rick Rothstein" wrote: Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy Worksheets("Complete List").Range(Cells(ccc * 50 + 1, _ 1),Cells(ccc + 1 * 50, 1)).PasteSpecial **ALL** of the Cells reference are pointing to the ActiveSheet, not to the Worksheet references the Range properties that contain them are pointing to. You would need to add the correct worksheet reference for each Cells function call in order to guarantee they referred to the correct cell. For example, the last Cells reference.... Cells(ccc+1*50,1)... would need to become Worksheets("Complete List").Cells(ccc + 1 * 50, 1) in order to point to the worksheet I think you mean it to point to (and you would have to do the same thing for the other three of them). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A What-if scenario changing one figure to work out another | Excel Worksheet Functions | |||
Can't figure out why Macro wont work???? | Excel Worksheet Functions | |||
How do I add additional lines to the work sheet. | New Users to Excel | |||
I'm trying to figure out what would work best for data entry and retreval. | Excel Programming | |||
VBA Help, cant figure out why it dosn't work. | Excel Programming |