Copy range from one workbook to another
 

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

Copy range from one workbook to another
 

Hi,

It looks like you are using a Private Function

FindNonZero(sWbk, intWorksheet, intRowPointer)

Could you post this function for further assistance ...

HTH

Copy range from one workbook to another
 

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 wrote:

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

--

Dave Peterson

Copy range from one workbook to another
 

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

Copy range from one workbook to another
 

I still would consider dropping the activate's completely.

Dale Fye wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

Copy range from one workbook to another
 

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" wrote in message
...
I still would consider dropping the activate's completely.

Dale Fye wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

Copy range from one workbook to another
 

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

Dale Fye wrote:

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" wrote in message
...
I still would consider dropping the activate's completely.

Dale Fye wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson