Solved

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

Posted on 2011-02-28
16
263 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
Comment Utility
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
Comment Utility
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
Comment Utility
Oops ... I should have changed the message, but it'll still work.
0
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:easycapital
Comment Utility
@ 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
Comment Utility
@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
Comment Utility
Here it is.
EE-Test.xlsm
0
 

Author Comment

by:easycapital
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
And now for the attachment.
EE-Test-sdw.xlsm
0
 

Author Closing Comment

by:easycapital
Comment Utility
Thanks,
JP
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now