Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy Range from one wbk to another
When I originally posted this, it somehow got tacked onto a thread that
started on 11/24/07. I read that thread, and tried the technique mentioned, but it did not work either. Figured I better start my own thread if I wanted it to get any visibility. On a monthly basis, I recieve about 30 workbooks and have to consolidate them into a single workbook to forward to my prime contractor. This involves copying subsections of each of these workbooks into the same location (sheet/rows) in my consolidated workbook. I've got code working that loops through all of the workbooks in the same folder as my consolidated workbook, and then search those workbooks (3 sheets) for non-zero values in a particular row/column combination. This function FindNonZero( ) works great, returns a True/False value to indicate whether it found the non-zero value, and also gives me the worksheet and row pointer for the non-zero values. The portion of my code below works great if there is only a single non-zero value in the source workbook, but if the code finds a second non-zero value I get a Runtime Error '1004' Application-defined or object-defined error when the "Rng.Select" statement is executed, and my code stops running. I don't know whether it is because the source workbook (sWbk) already has a range selected (I have not figured out how to un-select a range programmatically) and Excel won't allow that, or whether there is something else wrong with my methodology. Would appreciate any assistance or other recommendations. Thanks, Dale While FindNonZero(sWbk, intWorksheet, intRowPointer) = True Debug.Print sWbk.Name, intWorksheet, intRowPointer, sWbk.Sheets(intWorksheet).Cells(intRowPointer, 12) strRange = "A" & (intRowPointer - 9) & ":K" & (intRowPointer - 1) Set rng = sWbk.Worksheets(intWorksheet).Range(strRange) rng.Select Selection.Copy aWbk.Sheets(intWorksheet).Activate aWbk.Sheets(intWorksheet).Range("A" & (intRowPointer - 9)).Select Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False Wend |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy Range from one wbk to another
You can't move to a range in a sheet if you aren't already in that sheet.
Just put: sWbk.Worksheets(intWorksheet).Activate before your rng.Select line. "Dale Fye" wrote: When I originally posted this, it somehow got tacked onto a thread that started on 11/24/07. I read that thread, and tried the technique mentioned, but it did not work either. Figured I better start my own thread if I wanted it to get any visibility. On a monthly basis, I recieve about 30 workbooks and have to consolidate them into a single workbook to forward to my prime contractor. This involves copying subsections of each of these workbooks into the same location (sheet/rows) in my consolidated workbook. I've got code working that loops through all of the workbooks in the same folder as my consolidated workbook, and then search those workbooks (3 sheets) for non-zero values in a particular row/column combination. This function FindNonZero( ) works great, returns a True/False value to indicate whether it found the non-zero value, and also gives me the worksheet and row pointer for the non-zero values. The portion of my code below works great if there is only a single non-zero value in the source workbook, but if the code finds a second non-zero value I get a Runtime Error '1004' Application-defined or object-defined error when the "Rng.Select" statement is executed, and my code stops running. I don't know whether it is because the source workbook (sWbk) already has a range selected (I have not figured out how to un-select a range programmatically) and Excel won't allow that, or whether there is something else wrong with my methodology. Would appreciate any assistance or other recommendations. Thanks, Dale While FindNonZero(sWbk, intWorksheet, intRowPointer) = True Debug.Print sWbk.Name, intWorksheet, intRowPointer, sWbk.Sheets(intWorksheet).Cells(intRowPointer, 12) strRange = "A" & (intRowPointer - 9) & ":K" & (intRowPointer - 1) Set rng = sWbk.Worksheets(intWorksheet).Range(strRange) rng.Select Selection.Copy aWbk.Sheets(intWorksheet).Activate aWbk.Sheets(intWorksheet).Range("A" & (intRowPointer - 9)).Select Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False Wend |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy Range from one wbk to another
Thanks,
Appreciate the response, and the confirmation. I thought about that later this afternoon. I currently have that line outside of my loop, and need to move it inside the loop, but had not had a chance to test it. Dale "Smallweed" wrote in message ... You can't move to a range in a sheet if you aren't already in that sheet. Just put: sWbk.Worksheets(intWorksheet).Activate before your rng.Select line. "Dale Fye" wrote: When I originally posted this, it somehow got tacked onto a thread that started on 11/24/07. I read that thread, and tried the technique mentioned, but it did not work either. Figured I better start my own thread if I wanted it to get any visibility. On a monthly basis, I recieve about 30 workbooks and have to consolidate them into a single workbook to forward to my prime contractor. This involves copying subsections of each of these workbooks into the same location (sheet/rows) in my consolidated workbook. I've got code working that loops through all of the workbooks in the same folder as my consolidated workbook, and then search those workbooks (3 sheets) for non-zero values in a particular row/column combination. This function FindNonZero( ) works great, returns a True/False value to indicate whether it found the non-zero value, and also gives me the worksheet and row pointer for the non-zero values. The portion of my code below works great if there is only a single non-zero value in the source workbook, but if the code finds a second non-zero value I get a Runtime Error '1004' Application-defined or object-defined error when the "Rng.Select" statement is executed, and my code stops running. I don't know whether it is because the source workbook (sWbk) already has a range selected (I have not figured out how to un-select a range programmatically) and Excel won't allow that, or whether there is something else wrong with my methodology. Would appreciate any assistance or other recommendations. Thanks, Dale While FindNonZero(sWbk, intWorksheet, intRowPointer) = True Debug.Print sWbk.Name, intWorksheet, intRowPointer, sWbk.Sheets(intWorksheet).Cells(intRowPointer, 12) strRange = "A" & (intRowPointer - 9) & ":K" & (intRowPointer - 1) Set rng = sWbk.Worksheets(intWorksheet).Range(strRange) rng.Select Selection.Copy aWbk.Sheets(intWorksheet).Activate aWbk.Sheets(intWorksheet).Range("A" & (intRowPointer - 9)).Select Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False Wend |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repost: Copy and Offset cell reference | Excel Worksheet Functions | |||
Named Range REPOST | Excel Worksheet Functions | |||
Repost - Is there a quicker way to copy paste | Excel Programming | |||
Copy & paste image from UserForm (repost) | Excel Programming | |||
Repost: Any way to do this with a range? | Excel Programming |