My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() Post#1 | |
Posts: 536 Joined: 22-August 11 ![]() | Hi, How do I combine two workbooks to become 1? For example I want workbook1.xls & workbook2.xls to be one. Thanks |
![]() Post#2 | |
Banned Posts: 9,239 Joined: 21-June 07 ![]() | from where do you want to run the VBA? From one of those 2 workbooks, or from a 3rd workbook? |
![]() Post#3 | |
Posts: 536 Joined: 22-August 11 ![]() | I don't have a VBA. I was thinking something like import/export. Here is what I want: orkbook1.xls+Workbook2.xls = Workbook3.xls Thanks. |
![]() Post#4 | |
Banned Posts: 9,239 Joined: 21-June 07 ![]() | ;m sorry, I don't understand what you mean. Do you mean that you want a solution that does not use VBA? - not understanding this either, sorry can you rephrase or something? |
![]() Post#5 | |
Posts: 536 Joined: 22-August 11 ![]() | I don't mind using VBA, what I meant was I don't currently have a VBA code to do the task. That I want is I have workbook1 & workbook2, but I want to combine them as workbook3. |
![]() Post#6 | |
Banned Posts: 9,239 Joined: 21-June 07 ![]() | Ok thanks, so my second question then is, what do you mean by combine them? Basically you want to just take all of their sheets - (from both books 1 and 2), and put all of those worksheets into a new one, worksheet3...Do I understand that correct? If so, I'll reply w/code suggestion |
![]() Post#7 | |
Posts: 536 Joined: 22-August 11 ![]() | Yes! Exactly what I want. I want to retain their formatting - Bold text etc., but I want all sheets in the workbook to be paste as values in the New combined workbook. |
![]() Post#8 | |
Banned Posts: 9,239 Joined: 21-June 07 ![]() | So, if you have 3 sheets in book1, and 4 sheets in book2, then the NewBook3 will send up with 7 sheets right? That's OK? xcept each one of the 7 sheets will be only VALUES from the original source? Do I understand that right too? sorry! just want to make sure. |
![]() Post#9 | |
Posts: 536 Joined: 22-August 11 ![]() | Exactly! |
![]() Post#10 | |
Banned Posts: 9,239 Joined: 21-June 07 ![]() | Oh and the last question you haven't answered yet - FROM WHERE do you want to run this code? From within book1 or book2 (the source books) ? or from within an already-existing Book3? Or from within just wherever else (a totally separate book from all 3 of them), which we might call "CodeBook.xlsm" or something. |
![]() Post#11 | |
Posts: 536 Joined: 22-August 11 ![]() | A totally separate workbook. |
![]() Post#12 | |
Banned Posts: 9,239 Joined: 21-June 07 ![]() | Here is some code that should work for you - paste it in a new standard module; note the comments where you need to change something. CODE Sub Combine() Dim wbSource1 As Workbook Dim wbSource2 As Workbook Dim ws As Worksheet Dim wsAdd As Worksheet Dim wbFinal As Workbook et wbSource1 = Workbooks.Open("C:\Documents and Settings\pisorsia\Desktop\1.xls") 'change to suit! Set wbSource2 = Workbooks.Open("C:\Documents and Settings\pisorsia\Desktop\2.xls") 'change to suit! Set wbFinal = Workbooks.Add Application.DisplayAlerts = False 'turn alerts off, like "are you sure you want to delete this sheet?" stuff wbFinal.Sheets(1).Name = "blanksheet" For Each ws In wbFinal.Worksheets If ws.Name <> "blanksheet" Then ws.Delete Next ws 'put the first book's stuff in For Each ws In wbSource1.Worksheets Set wsAdd = wbFinal.Worksheets.Add wsAdd.Name = "from-" & ws.Name & "-" & Format(Now, "hhmmss") 'some random to avoid conflicts ws.Cells.Copy wsAdd.Cells.PasteSpecial xlPasteValues Next ws 'put the second book's stuff in For Each ws In wbSource2.Worksheets Set wsAdd = wbFinal.Worksheets.Add wsAdd.Name = ws.Name & "-" & Format(Now, "hhmmss") 'some random to avoid conflicts ws.Cells.Copy wsAdd.Cells.PasteSpecial xlPasteValues Next ws wbSource1.Close (False) wbSource2.Close (False) MsgBox "Complete", vbExclamation, " " Application.DisplayAlerts = True 'turn back on End Sub |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 21st April 2018 - 02:16 PM |