Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto merge files
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge two excel files like in word mail merge | Excel Discussion (Misc queries) | |||
merge 2 files | Excel Discussion (Misc queries) | |||
Merge 2 Files | Excel Discussion (Misc queries) | |||
how do I merge two files? | Excel Discussion (Misc queries) | |||
MERGE 2 FILES | Excel Discussion (Misc queries) |