Need some more eyes to look at this ...
 

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/micro...a6ea267ceb09a4

thanks, ray

Need some more eyes to look at this ...
 

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)



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ray" wrote in message ups.com...
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/micro...a6ea267ceb09a4

thanks, ray

Need some more eyes to look at this ...
 

On May 7, 11:10 am, "Ron de Bruin" wrote:
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)

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ray" wrote in oglegroups.com...
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/micro...rogramming/bro...

thanks, ray

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?

Need some more eyes to look at this ...
 

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ray" wrote in message oups.com...
On May 7, 11:10 am, "Ron de Bruin" wrote:
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)

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ray" wrote in oglegroups.com...
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/micro...rogramming/bro...

thanks, ray

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?

Need some more eyes to look at this ...
 

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ray" wrote in message oups.com...
On May 7, 11:10 am, "Ron de Bruin" wrote:
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)

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ray" wrote in oglegroups.com...
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/micro...rogramming/bro...

thanks, ray

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?

Need some more eyes to look at this ...
 

On May 7, 1:12 pm, "Ron de Bruin" wrote:
You can also try it with PasteSpecial Values and see if you have the same problem

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ron de Bruin" wrote in .. .

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Ray" wrote in ooglegroups.com...
On May 7, 11:10 am, "Ron de Bruin" wrote:
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)

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ray" wrote in oglegroups.com...
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/micro...rogramming/bro...

thanks, ray

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?

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

Need some more eyes to look at this ...
 

I see

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ray" wrote in message oups.com...
On May 7, 1:12 pm, "Ron de Bruin" wrote:
You can also try it with PasteSpecial Values and see if you have the same problem

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ron de Bruin" wrote in .. .

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Ray" wrote in ooglegroups.com...
On May 7, 11:10 am, "Ron de Bruin" wrote:
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)

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ray" wrote in oglegroups.com...
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/micro...rogramming/bro...

thanks, ray

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?

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

Need some more eyes to look at this ...
 

On May 7, 2:01 pm, "Ron de Bruin" wrote:
I see

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ray" wrote in ooglegroups.com...
On May 7, 1:12 pm, "Ron de Bruin" wrote:
You can also try it with PasteSpecial Values and see if you have the same problem

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ron de Bruin" wrote in .. .

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Ray" wrote in ooglegroups.com...
On May 7, 11:10 am, "Ron de Bruin" wrote:
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)

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ray" wrote in oglegroups.com...
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/micro...rogramming/bro...

thanks, ray

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?

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

Hi Ron -

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

//ray

Need some more eyes to look at this ...
 

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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ray" wrote in message ups.com...
On May 7, 2:01 pm, "Ron de Bruin" wrote:
I see

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ray" wrote in ooglegroups.com...
On May 7, 1:12 pm, "Ron de Bruin" wrote:
You can also try it with PasteSpecial Values and see if you have the same problem

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ron de Bruin" wrote in .. .

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Ray" wrote in ooglegroups.com...
On May 7, 11:10 am, "Ron de Bruin" wrote:
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)

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ray" wrote in oglegroups.com...
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/micro...rogramming/bro...

thanks, ray

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?

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

Hi Ron -

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

//ray