Repost: Copy Range from one wbk to another

[Dale Fye]

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

 

[Smallweed]

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]

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