| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Combine Mutiple Worksheets into one
I have 60 differant excel files that are formatted the same way but need to
be combined into one master list. This is a databse of 60 of our sales represtntives contact list. Each column is formatted identical between each list. I want to combine all the lists into one for a mail campaign. Any suiggestios to make this easy. |
|
#3
|
|||
|
|||
|
Ron,
Thanks for the help. I am still struggling. I am not a Visual Basic geek yet but am learning. I took your script below to do this. I copied into my C drive a file called Creating America which has all 60 work sheets. I am confused on some of the language listed below. Sorry to sound stupid but I do not understand the Dim basebook as Workbook. Maybe this is over my head but hoping you can help. This is a mail list. from each representive and I want to merge them so I can do one run on my mailing equipment. The only change I did to your VB script was to change the directory for the files. I obviously need to do more but am somewhat confused. Here is the error I am getting... Script: C\Documents and Settings\Deanf.JBC\My Documents\Test.vbs Line: 2 Char: 14 Error: Expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error Sub TestFile3() Dim basebook As Workbook Dim mybook As Workbook Dim i As Long Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Creating America" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set basebook = ThisWorkbook For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) ActiveSheet.Name = mybook.Name mybook.Close Next i End If End With Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Dean Try this one http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dean F" <Dean wrote in message ... I have 60 differant excel files that are formatted the same way but need to be combined into one master list. This is a databse of 60 of our sales represtntives contact list. Each column is formatted identical between each list. I want to combine all the lists into one for a mail campaign. Any suiggestios to make this easy. |
|
#4
|
|||
|
|||
|
Hi dean
You said I have 60 differant excel files that are formatted ...... It sounds like you have one file with 60 sheets Am I correct? Then you need this example http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... Ron, Thanks for the help. I am still struggling. I am not a Visual Basic geek yet but am learning. I took your script below to do this. I copied into my C drive a file called Creating America which has all 60 work sheets. I am confused on some of the language listed below. Sorry to sound stupid but I do not understand the Dim basebook as Workbook. Maybe this is over my head but hoping you can help. This is a mail list. from each representive and I want to merge them so I can do one run on my mailing equipment. The only change I did to your VB script was to change the directory for the files. I obviously need to do more but am somewhat confused. Here is the error I am getting... Script: C\Documents and Settings\Deanf.JBC\My Documents\Test.vbs Line: 2 Char: 14 Error: Expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error Sub TestFile3() Dim basebook As Workbook Dim mybook As Workbook Dim i As Long Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Creating America" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set basebook = ThisWorkbook For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) ActiveSheet.Name = mybook.Name mybook.Close Next i End If End With Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Dean Try this one http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dean F" <Dean wrote in message ... I have 60 differant excel files that are formatted the same way but need to be combined into one master list. This is a databse of 60 of our sales represtntives contact list. Each column is formatted identical between each list. I want to combine all the lists into one for a mail campaign. Any suiggestios to make this easy. |
|
#5
|
|||
|
|||
|
No. It is 60 seperate excel files. They are not in one file. I have 60
differant files. I could send you some examples if it helps. "Ron de Bruin" wrote: Hi dean You said I have 60 differant excel files that are formatted ...... It sounds like you have one file with 60 sheets Am I correct? Then you need this example http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... Ron, Thanks for the help. I am still struggling. I am not a Visual Basic geek yet but am learning. I took your script below to do this. I copied into my C drive a file called Creating America which has all 60 work sheets. I am confused on some of the language listed below. Sorry to sound stupid but I do not understand the Dim basebook as Workbook. Maybe this is over my head but hoping you can help. This is a mail list. from each representive and I want to merge them so I can do one run on my mailing equipment. The only change I did to your VB script was to change the directory for the files. I obviously need to do more but am somewhat confused. Here is the error I am getting... Script: C\Documents and Settings\Deanf.JBC\My Documents\Test.vbs Line: 2 Char: 14 Error: Expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error Sub TestFile3() Dim basebook As Workbook Dim mybook As Workbook Dim i As Long Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Creating America" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set basebook = ThisWorkbook For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) ActiveSheet.Name = mybook.Name mybook.Close Next i End If End With Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Dean Try this one http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dean F" <Dean wrote in message ... I have 60 differant excel files that are formatted the same way but need to be combined into one master list. This is a databse of 60 of our sales represtntives contact list. Each column is formatted identical between each list. I want to combine all the lists into one for a mail campaign. Any suiggestios to make this easy. |
|
#6
|
|||
|
|||
|
Hi Dean
Send me a few of the files and i look at it Do you want to copy a worksheet ? or a range from each workbook -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... No. It is 60 seperate excel files. They are not in one file. I have 60 differant files. I could send you some examples if it helps. "Ron de Bruin" wrote: Hi dean You said I have 60 differant excel files that are formatted ...... It sounds like you have one file with 60 sheets Am I correct? Then you need this example http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... Ron, Thanks for the help. I am still struggling. I am not a Visual Basic geek yet but am learning. I took your script below to do this. I copied into my C drive a file called Creating America which has all 60 work sheets. I am confused on some of the language listed below. Sorry to sound stupid but I do not understand the Dim basebook as Workbook. Maybe this is over my head but hoping you can help. This is a mail list. from each representive and I want to merge them so I can do one run on my mailing equipment. The only change I did to your VB script was to change the directory for the files. I obviously need to do more but am somewhat confused. Here is the error I am getting... Script: C\Documents and Settings\Deanf.JBC\My Documents\Test.vbs Line: 2 Char: 14 Error: Expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error Sub TestFile3() Dim basebook As Workbook Dim mybook As Workbook Dim i As Long Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Creating America" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set basebook = ThisWorkbook For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) ActiveSheet.Name = mybook.Name mybook.Close Next i End If End With Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Dean Try this one http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dean F" <Dean wrote in message ... I have 60 differant excel files that are formatted the same way but need to be combined into one master list. This is a databse of 60 of our sales represtntives contact list. Each column is formatted identical between each list. I want to combine all the lists into one for a mail campaign. Any suiggestios to make this easy. |
|
#7
|
|||
|
|||
|
Also I am some what ignorant on visual basic so if you could give me a more
live example it would help. You guys are awesome with this kind of support "Ron de Bruin" wrote: Hi dean You said I have 60 differant excel files that are formatted ...... It sounds like you have one file with 60 sheets Am I correct? Then you need this example http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... Ron, Thanks for the help. I am still struggling. I am not a Visual Basic geek yet but am learning. I took your script below to do this. I copied into my C drive a file called Creating America which has all 60 work sheets. I am confused on some of the language listed below. Sorry to sound stupid but I do not understand the Dim basebook as Workbook. Maybe this is over my head but hoping you can help. This is a mail list. from each representive and I want to merge them so I can do one run on my mailing equipment. The only change I did to your VB script was to change the directory for the files. I obviously need to do more but am somewhat confused. Here is the error I am getting... Script: C\Documents and Settings\Deanf.JBC\My Documents\Test.vbs Line: 2 Char: 14 Error: Expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error Sub TestFile3() Dim basebook As Workbook Dim mybook As Workbook Dim i As Long Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Creating America" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set basebook = ThisWorkbook For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) ActiveSheet.Name = mybook.Name mybook.Close Next i End If End With Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Dean Try this one http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dean F" <Dean wrote in message ... I have 60 differant excel files that are formatted the same way but need to be combined into one master list. This is a databse of 60 of our sales represtntives contact list. Each column is formatted identical between each list. I want to combine all the lists into one for a mail campaign. Any suiggestios to make this easy. |
|
#8
|
|||
|
|||
|
Send the files to me private Dean and not to this group
-- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Dean Send me a few of the files and i look at it Do you want to copy a worksheet ? or a range from each workbook -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... No. It is 60 seperate excel files. They are not in one file. I have 60 differant files. I could send you some examples if it helps. "Ron de Bruin" wrote: Hi dean You said I have 60 differant excel files that are formatted ...... It sounds like you have one file with 60 sheets Am I correct? Then you need this example http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... Ron, Thanks for the help. I am still struggling. I am not a Visual Basic geek yet but am learning. I took your script below to do this. I copied into my C drive a file called Creating America which has all 60 work sheets. I am confused on some of the language listed below. Sorry to sound stupid but I do not understand the Dim basebook as Workbook. Maybe this is over my head but hoping you can help. This is a mail list. from each representive and I want to merge them so I can do one run on my mailing equipment. The only change I did to your VB script was to change the directory for the files. I obviously need to do more but am somewhat confused. Here is the error I am getting... Script: C\Documents and Settings\Deanf.JBC\My Documents\Test.vbs Line: 2 Char: 14 Error: Expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error Sub TestFile3() Dim basebook As Workbook Dim mybook As Workbook Dim i As Long Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Creating America" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set basebook = ThisWorkbook For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) ActiveSheet.Name = mybook.Name mybook.Close Next i End If End With Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Dean Try this one http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dean F" <Dean wrote in message ... I have 60 differant excel files that are formatted the same way but need to be combined into one master list. This is a databse of 60 of our sales represtntives contact list. Each column is formatted identical between each list. I want to combine all the lists into one for a mail campaign. Any suiggestios to make this easy. |
|
#9
|
|||
|
|||
|
Ron,
How do I attach the files.. "Ron de Bruin" wrote: Hi Dean Send me a few of the files and i look at it Do you want to copy a worksheet ? or a range from each workbook -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... No. It is 60 seperate excel files. They are not in one file. I have 60 differant files. I could send you some examples if it helps. "Ron de Bruin" wrote: Hi dean You said I have 60 differant excel files that are formatted ...... It sounds like you have one file with 60 sheets Am I correct? Then you need this example http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... Ron, Thanks for the help. I am still struggling. I am not a Visual Basic geek yet but am learning. I took your script below to do this. I copied into my C drive a file called Creating America which has all 60 work sheets. I am confused on some of the language listed below. Sorry to sound stupid but I do not understand the Dim basebook as Workbook. Maybe this is over my head but hoping you can help. This is a mail list. from each representive and I want to merge them so I can do one run on my mailing equipment. The only change I did to your VB script was to change the directory for the files. I obviously need to do more but am somewhat confused. Here is the error I am getting... Script: C\Documents and Settings\Deanf.JBC\My Documents\Test.vbs Line: 2 Char: 14 Error: Expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error Sub TestFile3() Dim basebook As Workbook Dim mybook As Workbook Dim i As Long Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Creating America" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set basebook = ThisWorkbook For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) ActiveSheet.Name = mybook.Name mybook.Close Next i End If End With Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Dean Try this one http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dean F" <Dean wrote in message ... I have 60 differant excel files that are formatted the same way but need to be combined into one master list. This is a databse of 60 of our sales represtntives contact list. Each column is formatted identical between each list. I want to combine all the lists into one for a mail campaign. Any suiggestios to make this easy. |
|
#10
|
|||
|
|||
|
Ron,
"Ron de Bruin" wrote: Hi Dean Send me a few of the files and i look at it Do you want to copy a worksheet ? or a range from each workbook -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... No. It is 60 seperate excel files. They are not in one file. I have 60 differant files. I could send you some examples if it helps. "Ron de Bruin" wrote: Hi dean You said I have 60 differant excel files that are formatted ...... It sounds like you have one file with 60 sheets Am I correct? Then you need this example http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... Ron, Thanks for the help. I am still struggling. I am not a Visual Basic geek yet but am learning. I took your script below to do this. I copied into my C drive a file called Creating America which has all 60 work sheets. I am confused on some of the language listed below. Sorry to sound stupid but I do not understand the Dim basebook as Workbook. Maybe this is over my head but hoping you can help. This is a mail list. from each representive and I want to merge them so I can do one run on my mailing equipment. The only change I did to your VB script was to change the directory for the files. I obviously need to do more but am somewhat confused. Here is the error I am getting... Script: C\Documents and Settings\Deanf.JBC\My Documents\Test.vbs Line: 2 Char: 14 Error: Expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error Sub TestFile3() Dim basebook As Workbook Dim mybook As Workbook Dim i As Long Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Creating America" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set basebook = ThisWorkbook For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) ActiveSheet.Name = mybook.Name mybook.Close Next i End If End With Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Dean Try this one http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dean F" <Dean wrote in message ... I have 60 differant excel files that are formatted the same way but need to be combined into one master list. This is a databse of 60 of our sales represtntives contact list. Each column is formatted identical between each list. I want to combine all the lists into one for a mail campaign. Any suiggestios to make this easy. |
|
#11
|
|||
|
|||
|
Ron...Sent tot he e-mail address below correct?
"Ron de Bruin" wrote: Send the files to me private Dean and not to this group -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Dean Send me a few of the files and i look at it Do you want to copy a worksheet ? or a range from each workbook -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... No. It is 60 seperate excel files. They are not in one file. I have 60 differant files. I could send you some examples if it helps. "Ron de Bruin" wrote: Hi dean You said I have 60 differant excel files that are formatted ...... It sounds like you have one file with 60 sheets Am I correct? Then you need this example http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... Ron, Thanks for the help. I am still struggling. I am not a Visual Basic geek yet but am learning. I took your script below to do this. I copied into my C drive a file called Creating America which has all 60 work sheets. I am confused on some of the language listed below. Sorry to sound stupid but I do not understand the Dim basebook as Workbook. Maybe this is over my head but hoping you can help. This is a mail list. from each representive and I want to merge them so I can do one run on my mailing equipment. The only change I did to your VB script was to change the directory for the files. I obviously need to do more but am somewhat confused. Here is the error I am getting... Script: C\Documents and Settings\Deanf.JBC\My Documents\Test.vbs Line: 2 Char: 14 Error: Expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error Sub TestFile3() Dim basebook As Workbook Dim mybook As Workbook Dim i As Long Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Creating America" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set basebook = ThisWorkbook For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) ActiveSheet.Name = mybook.Name mybook.Close Next i End If End With Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Dean Try this one http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dean F" <Dean wrote in message ... I have 60 differant excel files that are formatted the same way but need to be combined into one master list. This is a databse of 60 of our sales represtntives contact list. Each column is formatted identical between each list. I want to combine all the lists into one for a mail campaign. Any suiggestios to make this easy. |
|
#12
|
|||
|
|||
|
Hi Dean
I have send you a example workbook -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... Ron...Sent tot he e-mail address below correct? "Ron de Bruin" wrote: Send the files to me private Dean and not to this group -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Dean Send me a few of the files and i look at it Do you want to copy a worksheet ? or a range from each workbook -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... No. It is 60 seperate excel files. They are not in one file. I have 60 differant files. I could send you some examples if it helps. "Ron de Bruin" wrote: Hi dean You said I have 60 differant excel files that are formatted ...... It sounds like you have one file with 60 sheets Am I correct? Then you need this example http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... Ron, Thanks for the help. I am still struggling. I am not a Visual Basic geek yet but am learning. I took your script below to do this. I copied into my C drive a file called Creating America which has all 60 work sheets. I am confused on some of the language listed below. Sorry to sound stupid but I do not understand the Dim basebook as Workbook. Maybe this is over my head but hoping you can help. This is a mail list. from each representive and I want to merge them so I can do one run on my mailing equipment. The only change I did to your VB script was to change the directory for the files. I obviously need to do more but am somewhat confused. Here is the error I am getting... Script: C\Documents and Settings\Deanf.JBC\My Documents\Test.vbs Line: 2 Char: 14 Error: Expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error Sub TestFile3() Dim basebook As Workbook Dim mybook As Workbook Dim i As Long Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Creating America" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set basebook = ThisWorkbook For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) ActiveSheet.Name = mybook.Name mybook.Close Next i End If End With Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Hi Dean Try this one http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dean F" <Dean wrote in message ... I have 60 differant excel files that are formatted the same way but need to be combined into one master list. This is a databse of 60 of our sales represtntives contact list. Each column is formatted identical between each list. I want to combine all the lists into one for a mail campaign. Any suiggestios to make this easy. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Print all charts in a workbook (multiple worksheets) | Charts and Charting in Excel | |||
| Charting information from a number of worksheets | Charts and Charting in Excel | |||
| sotring worksheets in a workbook | Charts and Charting in Excel | |||
| How do I combine two worksheets into one graph | Charts and Charting in Excel | |||
| Linking to Worksheets on Excel -Saved Web Page | Links and Linking in Excel | |||
Linear Mode
