Copy One Sheet To Another File and SaveAs

MikeSea

New Member
Joined
Oct 13, 2006
Messages
6
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?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
Hi -
Welcome to the board. when you save as the file, is it all 10 worksheets or just the RawData sheets?
 

MikeSea

New Member
Joined
Oct 13, 2006
Messages
6
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
So is the MasterFile actually a sort of template?

Where would you be copying the data to?

Which sheet is C3 on?
 

MikeSea

New Member
Joined
Oct 13, 2006
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Oct 13, 2006
Messages
6

ADVERTISEMENT

Richard,

Its the entire sheet that needs to be copied.

Cheers

Mike
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Mike

Try the following code:

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

New Member
Joined
Oct 13, 2006
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,383
Messages
5,528,377
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top