Need some more eyes to look at this ...

[Ray]

I'm not sure how to 'bump' my previous thread to the first page again
(so more people will see it), so please take a look at this post:
http://groups.google.com/group/micr...709dd/d9a6ea267ceb09a4?hl=en#d9a6ea267ceb09a4

thanks, ray

 

[Ron de Bruin]

Hi Ray

Maybe there is a problem with the file ?

Try one of the code samples on this page
http://www.rondebruin.nl/copy3.htm

Or try the add-in (link on the page above)

 

[Ray]

Hi Ray

Maybe there is a problem with the file ?

Try one of the code samples on this pagehttp://www.rondebruin.nl/copy3.htm

Or try the add-in (link on the page above)

Hi Ron, thanks for your response ...

I just ran a 'test' of sorts and I think I've narrowed the problem
down, but still don't know how to fix it ...

The test was this: I took out all of the source files that have
worked properly before, leaving approx 20 files (incl the file that
the code had continually stopped on). This time, the 'problem' file
worked ok, but the code STILL errored out on the same line
(destrange.Value = sourceRange.Value) on the 16th file.

So, it would seem that I'm defininely over-running some type of
buffer, perhaps the clipboard?

I've tried this code:
Application.CutCopyMode = False
at the end of each loop, to clear the clipboard .... as well as this
code:

Public Declare Function OpenClipboard Lib "user32" ( _
ByVal hwnd AsLong) As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long

Sub ClearClipboard()
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
End Sub
(via Frank Kabel)

No luck on either one ...

any more thoughts?

 

[Ron de Bruin]

I not understand the code that you use now

You loop through the files and copy the columns(27) to the same place each time ?
Is that correct ?

Tell me what you want then I can make another example for you that you can try

 

[Ron de Bruin]

You can also try it with PasteSpecial Values and see if you have the same problem

 

[Ray]

You can also try it with PasteSpecial Values and see if you have the same problem

hi ron -

I have 30+ data files, one for each store ... these are identically
structured. These files are opened one at a time and the desired
range is 'copied' into the destination sheet within the Master
Workbook. The 'getstore' code is used to identify the store file (ie
the source wb) and the destination sheet (in the Master file) is
Sheets(getstore).

So, in the Master file, there are 30+ worksheets, one for each
store ..... is that more clear?

Thanks,
ray

 

[Ron de Bruin]

I see

If all the files are almost the same can you send me one private.
I am curious what the problem is

 

[Ray]

I see

If all the files are almost the same can you send me one private.
I am curious what the problem is

Hi Ron -

I just email the files to you .... I appreciate your time!

//ray

 

[Ron de Bruin]

This is working for Ray

I See if I can't find out why the other code is not working

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

' Isolates the store number from the workbook name
getstore = Left(mybook.Name, 3)
Set sourceRange = mybook.Sheets("Store SRA").Range("F:AF").EntireColumn
Set destrange = basebook.Sheets(getstore).Range("A1")

sourceRange.Copy
With destrange
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With

mybook.Close savechanges:=False

Next Fnum
End If