modifying merge all workbooks..

[Anders]

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 that’s another post ïŠ )

1. I have to leave blank rows in the source sheets for users to have room to
input but they don’t 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. I’ll have to modify the source code for where to find the files. I don’t
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. (I’m creating subfolders
in each folder that I don’t want to collate each time.) I have seen RDB
merge data (below) but don’t know how I would combine the two.
3. Can I transfer formatting (row/column, height/width) so I don’t 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

 

[Ron de Bruin]

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

 

[Anders]

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

 

[Ron de Bruin]

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
Yes change you layout to make it easy for yourself

--

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