Solved

Macro to bring cell content from one of the other open excel files.

Posted on 2011-02-28
16
266 Views
Last Modified: 2012-05-11
understand this could be a bit hard to do.

I am working with worbook A-sheet"Figures" and want to trigger a macro that:

1) asks from which of the opened excel files at the time I want to bring the data into this workbook, ideally I just select from a drop down list.

2) It ALWAYS brings the data from the selected work book from Sheet called "YTD" and from cells A1:C30, copies and pastes it into my workbook A-sheet"Figures" on cell B5 (in other words B5:D34).

Thanks,
JP
0
Comment
Question by:easycapital
  • 7
  • 5
  • 4
16 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 250 total points
ID: 35000739
Check out the attached file. I have created a user form with the below code. If you copy the userform into your workbook A, and launch the Userform with the macro button, it should do what you need.

Thomas
Private Sub btnOK_Click()
On Error Resume Next

Workbooks(Me.cbWbks.Value).Sheets("YTD").Range("A1:C30").Copy ActiveSheet.Range("B5:D34")

If Err <> 0 Then MsgBox "Sheet YTD not found in workbook " & Me.cbWbks.Value

Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim wbk As Workbook

For Each wbk In Application.Workbooks
    Debug.Print wbk.Name & "{" & wbk.IsAddin
    If wbk.IsAddin = False Then Me.cbWbks.AddItem (wbk.Name)
Next

End Sub

Open in new window

Select-from-open-workbooks.xls
0
 
LVL 12

Expert Comment

by:sdwalker
ID: 35000780
This is untested, but I think it'll do what you asked.  Save a copy of your workbook before you begin.

Let me know if it doesn't work and I'll fix it.

Good luck,

sdwalker
Sub SetUpCase()


Dim wbName(100) As String

  For Each wb In Workbooks
    If wb.Name <> ThisWorkbook.Name Then
    
      x = x + 1
      
      wbName(x) = wb.Name
    
      strMsg = strMsg & "  " & x & ")  " & wbName(x) & vbCrLf
    
    End If
    
  Next wb
  
  strMsg = "Enter the number corresponding to the Optimization Workbook." & vbCrLf & vbCrLf & strMsg

  myWb = InputBox(strMsg, "Select Optimization Workbook", 1)

  If myWb <> vbCancel Then
    
    myWb = CInt(myWb)
    workbooks(wbName(myWb)).activate
    
    sheets("YTD").select
		range("A1:C30").select
		selection.copy

	thisworkbook.select
		sheets("Figures").select
		range("B5").select
		Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
			:=False, Transpose:=False
		
    
  End If
  

End Sub

Open in new window

0
 
LVL 12

Expert Comment

by:sdwalker
ID: 35000794
Oops ... I should have changed the message, but it'll still work.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:nutsch
ID: 35000822
@sdwalker

I like the idea of putting it in a msgbox. It's less pretty / user friendly, but it's more portable.

T
0
 

Author Comment

by:easycapital
ID: 35000908
Yes, the idea of placing the open excel files in the message box is because the information will be extracted from the a file which name will change.  So manually selecting the file from where the info will come from, make it is very dynamic.

JP
0
 
LVL 12

Expert Comment

by:sdwalker
ID: 35001036
Thanks.  If going to a larger population (i.e. users), I definitely agree that your form is a much more elegant solution.  Often, I write code for myself (or for a more sophisticated user) in which quick and dirty does the trick.  Glad you liked it.  Just hope it works.
0
 
LVL 12

Expert Comment

by:sdwalker
ID: 35001057
BTW, my previous comment was directed @nutsch.  I really love comments from Experts I respect (i.e. those I've seen post many good solutions) so your compliment is very much appreciated!
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35001074
@JP

Both my code and sdwalker's will work within your context. If you always use the same file to import the data to (Workbook A in your description), I'd give an edge to my code, as the userform is easier to select, and generally more user-friendly. If you use any workbook to import the data to, then I might go with sdwalker's code, so I can limit the number of userforms in my personal.xls.

Both should work just fine, it's your choice of deployment that will dictate the best solution for you,

Thomas
0
 

Author Comment

by:easycapital
ID: 35001118
@ nutch:  I like the drop down.  It works real nice.  Where do I find the code in the VBA window?  I looked in Microsoft Excel Objects, Forms, Modules.  

@ swwalker:  Could you please put it in a file.  Yes, it will be used by other users as well.

Thanks.
JP
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35001187
@JP. Right-click the userform in the vb editor and choose View Code, as in the attached screencast.



Thomas
nutsch-414705.flv
0
 
LVL 12

Expert Comment

by:sdwalker
ID: 35001345
Here it is.
EE-Test.xlsm
0
 

Author Comment

by:easycapital
ID: 35001519
Nutsch:  Real nice!

I will wait for sdwalkers update, but both look very good.

@ Both:  Could you please look at the follow up question?  Q_26853739  It asks for the file name selected and for the date and time.

Thanks,
JP

0
 

Author Comment

by:easycapital
ID: 35001578
In addition to my comment above.  I need the copying to be "only" values and format.  
Thanks,
JP
0
 
LVL 12

Expert Comment

by:sdwalker
ID: 35002399
I've modified mine to include values and formats (previously, it was values only).  The code is untested, but hopefully it will work.
0
 
LVL 12

Assisted Solution

by:sdwalker
sdwalker earned 250 total points
ID: 35002407
And now for the attachment.
EE-Test-sdw.xlsm
0
 

Author Closing Comment

by:easycapital
ID: 35004926
Thanks,
JP
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question