Combining workbooks with varying names

[radka.silva]

Hello,

I have two workbooks open that always 'start' with the same name, but the ends varry. The goal is to combine all the data into one sheet. Here is an example of the names:

Workbook 1: Portfolio_Detail (49)
Workbook 2: Masteraccountview (66)

The data in these workbooks are downloads from custodial websites and the number at the end changes with every download.

Workbook 1 (Porfolio_Detail (xx)) has two sheets (PortfolioDetail and Holdings) and I have already wrote a simple macro to combine the sheets and it works perfectly:


Sub MSHoldingsCopy()
Sheets("Holdings").Select
Range("A1:K81").Select
Selection.Copy
Sheets("PortfolioDetail").Select
Range("A18").Select
ActiveSheet.Paste
End Sub

What I need now is to paste data from workbook Masteraccountview (xx) to Porfolio_Detail (xx). The data in Masteracountview (xx) is always in range A1:K2 and I need it to paste in cell A14 of Porfolio_Detail (xx).

I know very basic information about VBA and generally record macros and then edit them to do what I need. I don't know how to reference the different workbooks since their names change all the time. This is the code so far:


Sub Macro1()
Windows("MASTERACCOUNTVIEW (65).XLS").Activate
Range("A1:K2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("PORTFOLIO_DETAIL (52).XLS").Activate
Range("A14").Select
ActiveSheet.Paste
End Sub

I am using Excel 2010.

Thank you,

[mperrah]

hello radka.silva

if you use workbooks(1)
it wont matter what the name is.
you can run a script from the first book opened and use a file open dialog to choose the next workbook.
then the next book will be workbooks(2) and so on.


Public Function WorkbookCount(Optional VisibleOnly As Boolean) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WorkbookCount
' This returns the count of all open workbooks. If VisibleOnly
' is True, only visible workbooks are counted. If VisibleOnly is
' False, all workbooks are counted.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim wb As Workbook
Dim n As Long
For Each wb In Application.Workbooks
If VisibleOnly = False Or (VisibleOnly = True And wb.Windows(1).Visible = True) Then
n = n + 1
End If
Next wb
WorkbookCount = n
End Function

Sub openFilePrompt()
Dim wb1, wb2 As Workbook
Dim fn$, fnp, r1 As Range, r2 As Range
Dim n As Long
fn = Application.GetOpenFilename
Workbooks.Open Filename:=fn
n = WorkbookCount()
Workbooks(n).Activate
End Sub

Sub grabNew()
Dim wb1, wb2 As Workbook
Dim wb1ws1, wb2ws1 As Worksheet
Set wb1 = Application.Workbooks(1)
Set wb2 = Application.Workbooks(2)
Set wb1ws1 = wb1.Sheets(1)
Set wb2ws1 = wb2.Sheets(1)
With wb2ws1
.range("A1:K2").copy destination:=wb1ws1.range("A14")
End With
wb2.Close False
End Sub
not tested, but something like this?
-mark

[Paul_Hossler]

Dealing with more than one open WB is a little tricky -- I like to Set WB objects and them refer to them that way

The macro is in a third workbook the way I did it. You could have the macro save the WBs when its done, or other things




Option Explicit

Const csModule As String = "CombineWorkbooks"

Sub CombineWorkbooks()
Dim wbPortfolio As Workbook, wsMaster As Workbook, wb As Workbook

'find open WB and save object
For Each wb In Application.Workbooks
If wb.Name Like "Portfolio_Detail*" Then
Set wbPortfolio = wb

ElseIf wb.Name Like "Masteraccountview*" Then
Set wsMaster = wb
End If
Next

'check to see they're open
If wbPortfolio Is Nothing Then
Call MsgBox("A 'Portfolio_Detail' workbook is not open", vbCritical + vbOKOnly, csModule)
Exit Sub
End If
If wsMaster Is Nothing Then
Call MsgBox("A 'Masteraccountview' workbook is not open", vbCritical + vbOKOnly, csModule)
Exit Sub
End If


Call wbPortfolio.Worksheets("Holdings").Range("A1:K81").Copy(wbPortfolio.Worksheets("PortfolioDetail").Range("A18"))

Call wsMaster.Worksheets("Sheet1").Range("A1:K2").Copy(wbPortfolio.Worksheets("PortfolioDetail").Range("A14"))
End Sub

[mperrah]

I agree, I just thought if he/she starts with one open workbook and chooses the next through a sub,
we could control what gets opened. And by using the object model of workbooks, its all numbers, so names wouldnt matter.
If the user has a list of workbooks or a range we can code for that, but if they are random, this might be a start.

[radka.silva]

mperrah:
I have to say, I don't understand most of the top portion of your code and it worked to a certain degree.
These are the problems:
- the workbooks are not always opened in the same order; sometimes Portfolio_Detail is the first one and sometimes it's the second one. Therefore, this macro sometimes copied from the wrong workbook
- it didn't paste the selection to the correct sheet, but rather a brand new one, which really doesn't help with the 'combining'
I will try to play with it further.

Paul: I wasn't able to make your macro work at all. I kept getting an error that Portfolio_Detail is not open, yet the workbook was open. When I am trying to combine these workbooks, they are always open, I have just downloaded them from the custodial site, so I am not sure if I understand the code about 'workbook not open'. Also, I always want them to be combined in 'Portfolio_Detail' workbook, on the first tab 'PortfolioDetail'.

Thank you for your help.

Radka

[radka.silva]

Hello,

so I have played around a little bit with Paul's code and this is what I ended up with:

Sub Macro2()
Dim wbPortfolio As Workbook, wsMaster As Workbook, wb As Workbook
For Each wb In Application.Workbooks
If wb.Name Like "PORTFOLIO_DETAIL*" Then
Set wbPortfolio = wb
ElseIf wb.Name Like "MASTERACCOUNTVIEW*" Then
Set wsMaster = wb
End If
Next
Call wbPortfolio.Worksheets("Holdings").Range("A1:K81").Copy(wbPortfolio.Worksheets("PortfolioDetail").Range("A18"))
Call wsMaster.Worksheets("Accounts").Range("A1:K2").Copy(wbPortfolio.Worksheets("PortfolioDetail").Range("A14"))

End Sub

I tested it and it works! Yeah! Thank you guys! I am still not sure I fully undestand the top portion about referencing the sheets, but I am learning in baby steps!

Radka

[mperrah]

Glad mine partially worked.
To simplify, I would make one master book with the macros in it.
If you close all workbooks, then open the one with this code, you are prompted to open the next file.
My code copies the data then closes the second book so you're back to only one open, so I believe multiple workbooks would not be a problem.
Are you trying to accomplish something else?
maybe alter this to a specific sheet for the destination


Sub grabNew()
Dim wb1, wb2 As Workbook
Dim wb1ws1, wb2ws1 As Worksheet
Set wb1 = Application.Workbooks(1)
Set wb2 = Application.Workbooks(2)
Set wb1ws1 = wb1.Sheets("PortfolioDetail")
Set wb2ws1 = wb2.Sheets(1)
With wb2ws1
.range("A1:K2").copy destination:=wb1ws1.range("A14")
End With
wb2.Close False
End Sub

[mperrah]

Also,
do you preform this task often and have the combining go to the same workbook
or make a new book each time

We can make the code to write data to the next row on the same sheet, pulling data from multiple other books.
So the first time goes to A14, then the next is 2 rows lower, or 3 if you want a blank row in between

[radka.silva]

mperrah: When I go to the custodial site (financial institution, like a bank), I am looking for three information files (unfortunately the custodian doesn't provide one combined file): Account profile (when opened, ..), Account details (account numbers and names, ..), and Position holdings (stocks, bonds, cash..). The account profile and the positions details come in one file, but on separate tabs. So, I am combining the two tabs and the account detail info from another sheet. Once I have all in one sheet, I can paste it into our internal system. I do this task every day with multiple accounts and copying and pasting got pretty unbearable when I had 109 accounts in one day.

I hope this sheds some light on the purpose of this macro.

Thank you so much for all your help! You guys are awesome!!!

Radka