Solved

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

Posted on 2011-02-28
16
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

615 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