Copy One Sheet To Another File and SaveAs

[MikeSea]

Sorry about asking such a complex question for my first post but here goes.

I have a folder with about 1000 files, each of these files are identical in format, with 5 worksheets with one being hidden called RawData.

In another folder I have a file called MasterFile which has 10 worksheets with one called RawData.

The problem, I have to open each file in the first folder and copy the contents of the worksheet called RawData, into the MasterFile spreadsheet, I then have to save the MasterFile based on the cell value in cell C3. The newly created file need to be saved in a seperate directory to the original files.

Could someone please help me to set up a macro to automate this process as I suspect it will take me along time manually?

 

[agihcam]

Hi -
Welcome to the board. when you save as the file, is it all 10 worksheets or just the RawData sheets?

 

[MikeSea]

It needs to save the file with all 10 sheets in it. Basically, I'm looking to copy the data from the original file into the MasterFile and then save the MasterFile based on the contents of cell C3. I then need to move onto the next file and repeat the same operation and so on, till all 1000 files have been processed.

I realise that eventually I'll have a further 1000 files, but that's what I'm after.

 

[Norie]

So is the MasterFile actually a sort of template?

Where would you be copying the data to?

Which sheet is C3 on?

 

[MikeSea]

Yes, the MasteFile could best be described as a template where all the formulas and various reports are run from.

The data is being copied to the sheet called RawData on the MasteFile and the newly updated file needs to be saved to a folder called C:\HoldingArea.

Cell C3 is on the worksheet called RawData.

 

[Richard Schollar]

Mike

When you say the data is being copied to RawData in the Masterfile, do you mean the entire contents of RawData in all the 1000 files? Or do you just mean part of the sheet ie that contains the data (eg A4:Z1000 if that is the range the relevant data occupies). It makes it easier if it's the entire sheet.

Richard

 

[MikeSea]

Richard,

Its the entire sheet that needs to be copied.

Cheers

Mike

 

[Richard Schollar]

Hi Mike

Try the following code:

Sub CreateFiles()
Dim wbTemplate As Workbook, wbSource As Workbook, fs As FileSearch
Dim i As Integer, strDestination As String, strSource As String
Dim strFileList()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

strDestination = "C:\HoldingArea\"

Set fs = Application.FileSearch

strSource = "C:\SourceDirectory" ' Need to amend this

With fs
        .LookIn = strSource
        .SearchSubFolders = False   'set to false
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute > 0 Then
            ReDim strFileList(.FoundFiles.Count - 1)
            For i = 0 To .FoundFiles.Count - 1
                strFileList(i) = .FoundFiles(i + 1)
            Next i
        Else: MsgBox "No Excel Workbooks found": Exit Sub
        End If
End With

For i = LBound(strFileList) To UBound(strFileList)
    Set wbTemplate = Workbooks.Open("C:\Masterfile.xls", UpdateLinks:=False)  ' will need to amend
    Set wbSource = Workbooks.Open(strFileList(i), UpdateLinks:=False)
    wbSource.Sheets("RawData").Cells.Copy Destination:=wbTemplate.Sheets("RawData").Range("A1")
    wbSource.Close
    Err.Clear
    On Error Resume Next
    With wbTemplate
        .SaveAs strDestination & .Sheets("RawData").Range("C3").Value & ".xls"
        If Err > 0 Then .SaveAs strDestination & .Sheets("RawData").Range("C3").Value & Format(Time, "hh_mm_ss") & ".xls"
        On Error GoTo 0
        .Close
    End With
Next i

Set wbTemplate = Nothing
Set wbSource = Nothing

End Sub

1. Needs to go into a standard module (create a new workbook in Excel and go Tools>Macro>Visual Basic Editor - navigate to your new workbook in the Projects Pane (top left) and right-click and select Insert>Module. Copy & paste the code in here.

2. You will need to change the line in the code:

strSource = "C:\SourceDirectory"

to whatever the source directory of your files is

3. Also change:

Set wbTemplate = Workbooks.Open("C:\Masterfile.xls", UpdateLinks:=False)

to the full pathname of wherever your masterfile is located.

Then you just need to go Tools>Macro>Macros and select "CreateFiles" from the list and sit back - hopefully you won't have any problems!

Please post back if you get stuck.

Richard

 

[MikeSea]

Richard,

Works perfectly.

Have been told by my boss this morning that this task will have to be repeated every month so it looks as though you will be saving many hours of work.

Thanks for all your time in preparing the macro.

Cheers

Mike