hello -

I need to pull a specified range from approx 30 workbooks and paste
this info into the corresponding column in my summary workbook. I've
gotten my code to work to a point (open source file, copy range, paste
into a pre-specified column in destination workbook), but when I try
to modify the code to LOOK for the correct column, it all goes to
H***.

Here are the specs, with my current code below:
* range to copy: J5:J500 from source WB (called 'mybook' in code)
* identifying value (of source WB) in Sheets("Dashboard").Range("E13")
(called 'getstore' in code)

* find 'getstore' value in Row3 of destination WB and identify column
('Tcol' in code)
* paste copied range (J5:J500) in correct column, cells 5:500

Here's the current code (the 'meat' of it, anyway):

Set basebook = ThisWorkbook
basebook.Sheets("From FC
pkgs").Range("C5:AB500").ClearContents '

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
total = Fnum

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum), 0,
True)

Application.StatusBar = "Now processing File " & Fnum & "
of " & total

' Isolates the store number from the workbook name
getstore = mybook.Sheets("Dashboard").Range("E13").Value
getstore = Format(getstore, "000") 'necessary b/c
some stores have leading zeros

mybook.Sheets("P&L Acct Detail").Unprotect ("busnav")
Set sourceRange = mybook.Sheets("P&L Acct
Detail").Range("J5:J500")

Tcol = basebook.Worksheets("From FC
pkgs").Range("3:3").Find(getstore, LookIn:=xlValues,
LookAt:=xlWhole).Column
Trange = Tcol & "5:" & Tcol & "500"

Set destrange = basebook.Sheets("From FC
pkgs").Range(Trange)

destrange.Value = sourceRange.Value

mybook.Close savechanges:=False

Next Fnum
End If

The last time I ran this (with just one WB in a test folder), the
correct range was copied, but instead of into just one column, it was
copied into EVERY column in the destination workbook. The really
strange part is that the paste was started in row35 ..... and I don't
mention row35 anywhere in my code. Or do I???

TIA,
Ray

It might be easier to adapt code he

http://www.rondebruin.nl/copy3.htm

--
Regards,
Tom Ogilvy


"Ray" wrote:

hello -

I need to pull a specified range from approx 30 workbooks and paste
this info into the corresponding column in my summary workbook. I've
gotten my code to work to a point (open source file, copy range, paste
into a pre-specified column in destination workbook), but when I try
to modify the code to LOOK for the correct column, it all goes to
H***.

Here are the specs, with my current code below:
* range to copy: J5:J500 from source WB (called 'mybook' in code)
* identifying value (of source WB) in Sheets("Dashboard").Range("E13")
(called 'getstore' in code)

* find 'getstore' value in Row3 of destination WB and identify column
('Tcol' in code)
* paste copied range (J5:J500) in correct column, cells 5:500

Here's the current code (the 'meat' of it, anyway):

Set basebook = ThisWorkbook
basebook.Sheets("From FC
pkgs").Range("C5:AB500").ClearContents '

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
total = Fnum

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum), 0,
True)

Application.StatusBar = "Now processing File " & Fnum & "
of " & total

' Isolates the store number from the workbook name
getstore = mybook.Sheets("Dashboard").Range("E13").Value
getstore = Format(getstore, "000") 'necessary b/c
some stores have leading zeros

mybook.Sheets("P&L Acct Detail").Unprotect ("busnav")
Set sourceRange = mybook.Sheets("P&L Acct
Detail").Range("J5:J500")

Tcol = basebook.Worksheets("From FC
pkgs").Range("3:3").Find(getstore, LookIn:=xlValues,
LookAt:=xlWhole).Column
Trange = Tcol & "5:" & Tcol & "500"

Set destrange = basebook.Sheets("From FC
pkgs").Range(Trange)

destrange.Value = sourceRange.Value

mybook.Close savechanges:=False

Next Fnum
End If

The last time I ran this (with just one WB in a test folder), the
correct range was copied, but instead of into just one column, it was
copied into EVERY column in the destination workbook. The really
strange part is that the paste was started in row35 ..... and I don't
mention row35 anywhere in my code. Or do I???

TIA,
Ray

Ray,

Tcol is an integer, with the column number.
Tcol = basebook.Worksheets("From FC
pkgs").Range("3:3").Find(getstore, LookIn:=xlValues,
LookAt:=xlWhole).Column

So, instead of

Trange = Tcol & "5:" & Tcol & "500"

use

Trange = Cells(5,Tcol).Resize(496,1).Address

Though there are other ways to improve your code, we'll stop there, as that should make it work.

HTH,
Bernie
MS Excel MVP


"Ray" wrote in message
oups.com...
hello -

I need to pull a specified range from approx 30 workbooks and paste
this info into the corresponding column in my summary workbook. I've
gotten my code to work to a point (open source file, copy range, paste
into a pre-specified column in destination workbook), but when I try
to modify the code to LOOK for the correct column, it all goes to
H***.

Here are the specs, with my current code below:
* range to copy: J5:J500 from source WB (called 'mybook' in code)
* identifying value (of source WB) in Sheets("Dashboard").Range("E13")
(called 'getstore' in code)

* find 'getstore' value in Row3 of destination WB and identify column
('Tcol' in code)
* paste copied range (J5:J500) in correct column, cells 5:500

Here's the current code (the 'meat' of it, anyway):

Set basebook = ThisWorkbook
basebook.Sheets("From FC
pkgs").Range("C5:AB500").ClearContents '

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
total = Fnum

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum), 0,
True)

Application.StatusBar = "Now processing File " & Fnum & "
of " & total

' Isolates the store number from the workbook name
getstore = mybook.Sheets("Dashboard").Range("E13").Value
getstore = Format(getstore, "000") 'necessary b/c
some stores have leading zeros

mybook.Sheets("P&L Acct Detail").Unprotect ("busnav")
Set sourceRange = mybook.Sheets("P&L Acct
Detail").Range("J5:J500")

Tcol = basebook.Worksheets("From FC
pkgs").Range("3:3").Find(getstore, LookIn:=xlValues,
LookAt:=xlWhole).Column
Trange = Tcol & "5:" & Tcol & "500"

Set destrange = basebook.Sheets("From FC
pkgs").Range(Trange)

destrange.Value = sourceRange.Value

mybook.Close savechanges:=False

Next Fnum
End If

The last time I ran this (with just one WB in a test folder), the
correct range was copied, but instead of into just one column, it was
copied into EVERY column in the destination workbook. The really
strange part is that the paste was started in row35 ..... and I don't
mention row35 anywhere in my code. Or do I???

TIA,
Ray

Thanks to both for your input ... I'll check them out ...

Bernie, I'm always looking to learn (hence the failed code!) -- how
else would you improve my code?

Tom, checked out the link you sent .... this is where my (failed) code
originated! I actually used Ron's code in another project (with great
success) and the re-modified for my current project.

I 'could' probably use Ron's "Merge a range from all workbooks in a
folder (next to each other)" code, but I need the data in the SAME
ORDER every time b/c other formulas will use this info and aren't
built to 'look' for the right data.

I tried Bernie's code modification and got a 1004 error (object-
defined error) .... this error popped up after it had opened the
source WB, so I assume there's something wrong with the code related
to changing sheets?

I think I'm making this more difficult than it needs to be -- I just
need to be able to match the Store#
(mybook.sheets("Dashboard").Range("E13").value) with a value in Row3
of the Destination WB and then paste-values into Cells 5:500 of that
column. Sounds like it should be easy .... but then again, I AM
asking for help, aren't I? ;)

Ray,

Which line produced the error? You have no error checking for lack of a match on getting Tcol:

Tcol = basebook.Worksheets("From FC
pkgs").Range("3:3").Find(getstore, LookIn:=xlValues,
LookAt:=xlWhole).Column

If getstore doesn't exist, this will error.

Perhaps:

Dim myC As Range

Set myC = basebook.Worksheets("From FC pkgs"). _
Range("3:3").Find(getstore, LookIn:=xlValues, LookAt:=xlWhole)

If Not myC Is Nothing Then
Tcol = myC.Column
Else
Msgbox getstore & " wasn't found"
'Other action to take when getstore is not found
End If

Bernie
MS Excel MVP


"Ray" wrote in message
ps.com...
Tom, checked out the link you sent .... this is where my (failed) code
originated! I actually used Ron's code in another project (with great
success) and the re-modified for my current project.

I 'could' probably use Ron's "Merge a range from all workbooks in a
folder (next to each other)" code, but I need the data in the SAME
ORDER every time b/c other formulas will use this info and aren't
built to 'look' for the right data.

I tried Bernie's code modification and got a 1004 error (object-
defined error) .... this error popped up after it had opened the
source WB, so I assume there's something wrong with the code related
to changing sheets?

I think I'm making this more difficult than it needs to be -- I just
need to be able to match the Store#
(mybook.sheets("Dashboard").Range("E13").value) with a value in Row3
of the Destination WB and then paste-values into Cells 5:500 of that
column. Sounds like it should be easy .... but then again, I AM
asking for help, aren't I? ;)

Bernie -

I'm not sure of the specific line ... I don't get the normal "End" &
"De-Bug" buttons, just a way to close the error window, so no way to
tell what code is causing the error. It shouldn't be the 'getstore'
code -- this worked before and I haven't changed it at all ....

I'll try your other code and see what happens .....

Thanks,
ray