UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Combining Workbooks    
 
   
John_Ross
post Mar 30 2012, 02:15 PM
Post #1

UtterAccess Veteran
Posts: 370



Hi,

How do I combine two workbooks to become 1?

For example I want workbook1.xls & workbook2.xls to be one.

Thanks
Go to the top of the page
 
+
ipisors
post Mar 30 2012, 02:16 PM
Post #2

Banned
Posts: 9,239



from where do you want to run the VBA? From one of those 2 workbooks, or from a 3rd workbook?
Go to the top of the page
 
+
John_Ross
post Mar 30 2012, 02:18 PM
Post #3

UtterAccess Veteran
Posts: 370



I don't have a VBA. I was thinking something like import/export. Here is what I want:

Workbook1.xls+Workbook2.xls = Workbook3.xls

Thanks.
Go to the top of the page
 
+
ipisors
post Mar 30 2012, 02:23 PM
Post #4

Banned
Posts: 9,239



;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?
Go to the top of the page
 
+
John_Ross
post Mar 30 2012, 02:27 PM
Post #5

UtterAccess Veteran
Posts: 370



I don't mind using VBA, what I meant was I don't currently have a VBA code to do the task.

What I want is I have workbook1 & workbook2, but I want to combine them as workbook3.
Go to the top of the page
 
+
ipisors
post Mar 30 2012, 02:28 PM
Post #6

Banned
Posts: 9,239



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
Go to the top of the page
 
+
John_Ross
post Mar 30 2012, 02:32 PM
Post #7

UtterAccess Veteran
Posts: 370



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.
Go to the top of the page
 
+
ipisors
post Mar 30 2012, 02:33 PM
Post #8

Banned
Posts: 9,239



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?

Except 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.
Go to the top of the page
 
+
John_Ross
post Mar 30 2012, 02:35 PM
Post #9

UtterAccess Veteran
Posts: 370



Exactly!
Go to the top of the page
 
+
ipisors
post Mar 30 2012, 02:38 PM
Post #10

Banned
Posts: 9,239



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.
Go to the top of the page
 
+
John_Ross
post Mar 30 2012, 02:39 PM
Post #11

UtterAccess Veteran
Posts: 370



A totally separate workbook.
Go to the top of the page
 
+
ipisors
post Mar 30 2012, 02:52 PM
Post #12

Banned
Posts: 9,239



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

Set 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
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 25th July 2014 - 01:43 AM