Help with copying range btw workbooks (advanced example)

[Ray]

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

 

[Tom Ogilvy]

It might be easier to adapt code here:

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

 

[Bernie Deitrick]

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]

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?

 

[Ray]

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

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]

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

 

[Bernie Deitrick]

Ray,

Use the "Debug" button, and you should be taken to the line that is throwing the error...

HTH,
Bernie
MS Excel MVP

 

[Ray]

Hi Bernie -

It errors on your modified code from above:
Trange = Cells(5, Tcol).Resize(496, 1).Address
Run-Time Error 1004: Application-defined or Object-Defined Error

ideas?

//ray

 

[Ray]

Hi Bernie -

Yes, of course, the De-Bug button .... never used that one before
(seriously) ...

It errors on your modified code from above:
Trange = Cells(5, Tcol).Resize(496, 1).Address
Run-Time Error 1004: Application-defined or Object-Defined Error

I also noticed that in the Watch Window, all variables (getstore,
Tcol) are OK, but then when Trange errors out, they all say 'Out of
Context'. Not sure if that helps point in some direction, but figured
it might help...

ideas?

//ray

 

[Bernie Deitrick]

Ray,

This works fine for me:

Sub Test()
Tcol = 20
Trange = Cells(5, Tcol).Resize(496, 1).Address
MsgBox Trange
End Sub

So the basic code is OK. BUT: It will NOT work if Tcol doesn't have a valid value between 1 and
256, which means that where you put it may be wrong, or you haven't added error checking properly.

Post your entire procedure again, with the new line, and we'll take a look.

HTH,
Bernie
MS Excel MVP

 

[Ray]

Hi Bernie -

When I stepped through the procedure, I noted that Tcol had a value of
"3" (incl the "") -- would that matter?

Here's the entire code I have right now:
Sub FetchStoreData_Click()
Dim MyPath, getstore, FilesInPath As String
Dim MyFiles(), Trange, Tcol As String
Dim SourceRcount, x, Fnum, total As Long
Dim mybook, basebook, ws, sh As Workbook
Dim sourceRange, destrange, myC As Range

MyPath = "\\......\"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

' On Error GoTo CleanUp

Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set basebook = ThisWorkbook
basebook.Sheets("From FC
pkgs").Range("C5:E500").ClearContents 'clear all cells on all
sheets

'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")

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

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

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

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

destrange.Value = sourceRange.Value

mybook.Close savechanges:=False

Next Fnum
End If

Application.StatusBar = False

MsgBox "Store SRA created Successfully!"

CleanUp:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.StatusBar = False

End Sub

 

[Bernie Deitrick]

Ray,

Yes, it would. Cells is expecting a number, not a string. So dimension TCol as an integer, or use

CInt(Tcol)

instead of Tcol within the Cells line that errors out.

Note that your dimensioning statement:

Dim MyFiles(), Trange, Tcol As String

actually dimensions MyFiles() as a variant, Trange as a variant, and Tcol as a string.

Use

Dim MyFiles() As String, Trange As String, Tcol As Integer

That's just one of the rules of VB, which is different from, say, C++ in that respect.

HTH,
Bernie
MS Excel MVP

 

[Ray]

First, thanks VERY MUCH for your help Bernie .... it's really
appreciated!

I understand how my variable dimensioning is wrong -- I saw it
somewhere else and never had a problem until now. I never know how to
define my variables (as in, Long vs Integer vs Range)...

I made the change you suggest and the procedure now goes further, but
again errors out at
Trange = ....
Error "13": type mismatch .... Trange is defined as String.

I did notice that in the coding before (ie Set myC ... Tcol=...) that
Tcol comes up in the Watch window with a + next to it. When I click
on the +, a bunch of 'options' (parameters of Tcol, I suppose)
appear. Column is noted as '5' (which is right) and listed as Long.
I have it as Integer, as you specified...

I tried it as Long, but still got same error...

//ray

 

[Bernie Deitrick]

Ray,

You have

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

It should be (as I originally used)

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


HTH,
Bernie
MS Excel MVP

 

[Ray]

Ha! Perfect! Thanks again for all of your help Bernie .... I've
learned quite a bit in our exchange!