Auto merge files
 

I have a Master file containing 15 different tabs. The tabs are labele
"Georgia", "Ohio", "Texas", etc.

The data on each tab needs to be completed by a different person. T
make viewing easier for each person, I generated a single file (e.g
"Georgia" file, "Ohio" file, etc.) to send each person. After the
complete their particular file, it is sent to me and saved into
folder labeled "states".

Is there a VB code for the Master file that will automatically copy th
values from each file ("Georgia", "Ohio", etc.) and paste them to th
appropriate tab in the "states" file.

Cells C1:X50 need copied and pasted.


Thanks you

--
Message posted from http://www.ExcelForum.com

Auto merge files
 

Hi

Try this to copy the sheet from each workbook into the master workbook
http://www.rondebruin.nl/copy3.htm

Use
http://www.rondebruin.nl/copy3.htm#sheet



--
Regards Ron de Bruin
http://www.rondebruin.nl


"sowetoddid " wrote in message ...
I have a Master file containing 15 different tabs. The tabs are labeled
"Georgia", "Ohio", "Texas", etc.

The data on each tab needs to be completed by a different person. To
make viewing easier for each person, I generated a single file (e.g.
"Georgia" file, "Ohio" file, etc.) to send each person. After they
complete their particular file, it is sent to me and saved into a
folder labeled "states".

Is there a VB code for the Master file that will automatically copy the
values from each file ("Georgia", "Ohio", etc.) and paste them to the
appropriate tab in the "states" file.

Cells C1:X50 need copied and pasted.


Thanks you!


---
Message posted from http://www.ExcelForum.com/

Auto merge files
 

Thanks for your idea, and I would do that if it was only one time. But
this copy and paste process will be repeated on a regular basis wit
the same workbooks

--
Message posted from http://www.ExcelForum.com

Auto merge files
 

I was in a hurry on that last post. I see that I misread what you wer
saying. My apologies.

On the linked page you provided, I used the

"Copy a sheet from each workbook into your workbook" code and change
the file path to represent mine.


Can you help with a couple other things....

1. I would like to delete "Sheet1-3" automatically with the macro.
What is the command for auto-selecting "yes" when prompted for th
delete?

2. When the Sheets are created in my master files, they are labele
with "filename.xls". Is it possible to make the label onl
"filename"?


Many thanks

--
Message posted from http://www.ExcelForum.com

Auto merge files
 

This is the code without changes...

Sub TestFile6()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0

mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub


Thanks for the help

--
Message posted from http://www.ExcelForum.com

Auto merge files
 

Hi sowetoddid

Application.DisplayAlerts = False
'Sheet delete code
Application.DisplayAlerts = True


ActiveSheet.Name = Left(mybook.Name, Len(mybook.Name) - 4)


--
Regards Ron de Bruin
http://www.rondebruin.nl


"sowetoddid " wrote in message ...
This is the code without changes...

Sub TestFile6()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0

mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub


Thanks for the help.


---
Message posted from http://www.ExcelForum.com/

Auto merge files
 

Thanks, Ron de Bruin. I appreciate you help

--
Message posted from http://www.ExcelForum.com

Auto merge files
 

You are welcome

--
Regards Ron de Bruin
http://www.rondebruin.nl


"sowetoddid " wrote in message ...
Thanks, Ron de Bruin. I appreciate you help.


---
Message posted from http://www.ExcelForum.com/