Copy range from one workbook to another

[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

 

[Carim]

Hi,

It looks like you are using a Private Function

FindNonZero(sWbk, intWorksheet, intRowPointer)

Could you post this function for further assistance ...

HTH

 

[Dave Peterson]

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

 

[Dale Fye]

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 Peterson]

I still would consider dropping the activate's completely.

 

[Dale Fye]

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

 

[Dave Peterson]

If you have trouble, you'll want to post the current version of your code.

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