Hi All,

In xl2003 - Using RDB Merge all Workbooks (found here
http://msdn.microsoft.com/en-us/library/cc837974.aspx )

I have 13 folders on a shared drive for individual user groups to update
bi-weekly. Each folder contains one main update sheet which I will pull
together for one report (also contain subfolders that are saved copies by
date of previous updates, but thats another post � )

1. I have to leave blank rows in the source sheets for users to have room to
input but they dont have to fill. The pasted cells in the destination sheet
are stopping at 26 (where the first blank row is) I need to get to row 94
(past 3 more sets of empties). The sourcerange will always be €œa1:j94€�, for
every sheet. I have adjusted the sourcerange in VBA to this value €“ but
still running into the problem.
2. Ill have to modify the source code for where to find the files. I dont
want to find all files in destination folder, rather, I would like it to just
pick up 1 specific file in each folder €“ 13 in all. (Im creating subfolders
in each folder that I dont want to collate each time.) I have seen RDB
merge data (below) but dont know how I would combine the two.
3. Can I transfer formatting (row/column, height/width) so I dont have to
readjust everytime?
4. Can I transfer VBA sheet code (Calendar Control 11.0)

Thanks so much in advance.


Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long

myCountOfFiles = Get_File_Names = "Macro.xls"
MyPath = "h:\Macro"
Subfolders = False
ExtStr = "*.xl*"
myReturnedFiles = myFiles

If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If

Get_Data:
FileNameInA = True
PasteAsValues = True
SourceShName = ""
SourceShIndex = 1
SourceRng = "A1:G1"
StartCell = ""
myReturnedFiles = myFiles

End Sub

--
I''ve encountered an error and I need to close for the weekend :)

Hi Anders

Try this first

In this add-in I have a filter option for sheet names or workbook names
http://www.rondebruin.nl/merge.htm


--

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




"Anders" wrote in message ...
Hi All,

In xl2003 - Using RDB Merge all Workbooks (found here
http://msdn.microsoft.com/en-us/library/cc837974.aspx )

I have 13 folders on a shared drive for individual user groups to update
bi-weekly. Each folder contains one main update sheet which I will pull
together for one report (also contain subfolders that are saved copies by
date of previous updates, but thats another post � )

1. I have to leave blank rows in the source sheets for users to have room to
input but they dont have to fill. The pasted cells in the destination sheet
are stopping at 26 (where the first blank row is) I need to get to row 94
(past 3 more sets of empties). The sourcerange will always be €œa1:j94€�, for
every sheet. I have adjusted the sourcerange in VBA to this value €“ but
still running into the problem.
2. Ill have to modify the source code for where to find the files. I dont
want to find all files in destination folder, rather, I would like it to just
pick up 1 specific file in each folder €“ 13 in all. (Im creating subfolders
in each folder that I dont want to collate each time.) I have seen RDB
merge data (below) but dont know how I would combine the two.
3. Can I transfer formatting (row/column, height/width) so I dont have to
readjust everytime?
4. Can I transfer VBA sheet code (Calendar Control 11.0)

Thanks so much in advance.


Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long

myCountOfFiles = Get_File_Names = "Macro.xls"
MyPath = "h:\Macro"
Subfolders = False
ExtStr = "*.xl*"
myReturnedFiles = myFiles

If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If

Get_Data:
FileNameInA = True
PasteAsValues = True
SourceShName = ""
SourceShIndex = 1
SourceRng = "A1:G1"
StartCell = ""
myReturnedFiles = myFiles

End Sub

--
I''ve encountered an error and I need to close for the weekend :)

Hi Ron,
Nice add in ! I tried it, but it isn't keeping my formatting or code (like
calendar controls etc, coded conditional formatting and more).

Is there a modification I can do? Also, is there a way to select just one
level of sub folders? If not, I'm going to have to write the code.

I'm thinking about how to layout the share drive with the 13 different teams
and different update sheets and backup folders. I guess I could name them
all the same and select that name as the only document to merge.

***By the way, your 'save a copy in a newly created subfolder' code is
great. I added a date modifier to the file save name. Thanks so much!

Anders
--
I''ve encountered an error and I need to close for the weekend :)


"Ron de Bruin" wrote:

Hi Anders

Try this first

In this add-in I have a filter option for sheet names or workbook names
http://www.rondebruin.nl/merge.htm


--

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




"Anders" wrote in message ...
Hi All,

In xl2003 - Using RDB Merge all Workbooks (found here
http://msdn.microsoft.com/en-us/library/cc837974.aspx )

I have 13 folders on a shared drive for individual user groups to update
bi-weekly. Each folder contains one main update sheet which I will pull
together for one report (also contain subfolders that are saved copies by
date of previous updates, but thats another post � )

1. I have to leave blank rows in the source sheets for users to have room to
input but they dont have to fill. The pasted cells in the destination sheet
are stopping at 26 (where the first blank row is) I need to get to row 94
(past 3 more sets of empties). The sourcerange will always be €œa1:j94€�, for
every sheet. I have adjusted the sourcerange in VBA to this value €“ but
still running into the problem.
2. Ill have to modify the source code for where to find the files. I dont
want to find all files in destination folder, rather, I would like it to just
pick up 1 specific file in each folder €“ 13 in all. (Im creating subfolders
in each folder that I dont want to collate each time.) I have seen RDB
merge data (below) but dont know how I would combine the two.
3. Can I transfer formatting (row/column, height/width) so I dont have to
readjust everytime?
4. Can I transfer VBA sheet code (Calendar Control 11.0)

Thanks so much in advance.

When you merge data it is not good to copy also CF and controls (always problems)
You can add code after the merge is ready to add the formatting /CFif you want

I'm thinking about how to layout the share drive with the 13 different teams
Yes change you layout to make it easy for yourself

--

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




"Anders" wrote in message ...
Hi Ron,
Nice add in ! I tried it, but it isn't keeping my formatting or code (like
calendar controls etc, coded conditional formatting and more).

Is there a modification I can do? Also, is there a way to select just one
level of sub folders? If not, I'm going to have to write the code.

I'm thinking about how to layout the share drive with the 13 different teams
and different update sheets and backup folders. I guess I could name them
all the same and select that name as the only document to merge.

***By the way, your 'save a copy in a newly created subfolder' code is
great. I added a date modifier to the file save name. Thanks so much!

Anders
--
I''ve encountered an error and I need to close for the weekend :)


"Ron de Bruin" wrote:

Hi Anders

Try this first

In this add-in I have a filter option for sheet names or workbook names
http://www.rondebruin.nl/merge.htm


--

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




"Anders" wrote in message ...
Hi All,

In xl2003 - Using RDB Merge all Workbooks (found here
http://msdn.microsoft.com/en-us/library/cc837974.aspx )

I have 13 folders on a shared drive for individual user groups to update
bi-weekly. Each folder contains one main update sheet which I will pull
together for one report (also contain subfolders that are saved copies by
date of previous updates, but thats another post � )

1. I have to leave blank rows in the source sheets for users to have room to
input but they dont have to fill. The pasted cells in the destination sheet
are stopping at 26 (where the first blank row is) I need to get to row 94
(past 3 more sets of empties). The sourcerange will always be €œa1:j94€�, for
every sheet. I have adjusted the sourcerange in VBA to this value €“ but
still running into the problem.
2. Ill have to modify the source code for where to find the files. I dont
want to find all files in destination folder, rather, I would like it to just
pick up 1 specific file in each folder €“ 13 in all. (Im creating subfolders
in each folder that I dont want to collate each time.) I have seen RDB
merge data (below) but dont know how I would combine the two.
3. Can I transfer formatting (row/column, height/width) so I dont have to
readjust everytime?
4. Can I transfer VBA sheet code (Calendar Control 11.0)

Thanks so much in advance.