Copy range from one workbook to another

  • Thread starter Thread starter Dale Fye
  • Start date Start date
D

Dale Fye

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
 
Hi,

It looks like you are using a Private Function

FindNonZero(sWbk, intWorksheet, intRowPointer)

Could you post this function for further assistance ...

HTH
 
You can only select a range on the activesheet.

So you could do:

Set rng = sWbk.Worksheets(intWorksheet).Range(strRange)
swbk.activate
worksheets(inworksheet).select
rng.Select

But you're going to have to do the same thing at the other end (before you
paste), too.

But better would be to drop the .select's and .activate's:

...
Set rng = sWbk.Worksheets(intWorksheet).Range(strRange)

rng.copy
aWbk.Sheets(intWorksheet).Range("A" & (intRowPointer - 9)) _
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
 
Thanks, Dave.

I thought about that over dinner. I currently activate that sheet outside
my loop, and needed to move that line inside the loop.
 
Dave,

I could swear I tried that, and that it wouldn't allow me to do the paste
without the sheet being activated. I'll give it another try, though
 
If you have trouble, you'll want to post the current version of your code.

Dale said:
Dave,

I could swear I tried that, and that it wouldn't allow me to do the paste
without the sheet being activated. I'll give it another try, though
Click to expand...
 
Back
Top