Extract data from one workbook and copy and paste it to another

[TButhe]

Hi,
I have a workbook template that tracks weights on our patients (thankfully, not mine). We are now coming out with a new workbook template that we are adding a BMI (body mass index, if you wanted to know) calculation to. The information needs to be transferred from the original workbook to the new one. What I am envisioning is that there would be a button on the new template that would run a macro that would allow them to open the old workbook and then would copy the data and paste it to the appropriate places in the new workbook and then the button would disappear. (I assume I could put that code in the on_New event because they will save it when they are done and won't use the template again unless they really mess up.) These are not the most computer savvy users so to have them copy and paste manually would be almost impossible. I have no way of knowing what they called their files. I can do the copy and paste part of this (I think) and firefytr has helped me with the code to open another workbook. My problem lies with switching back and forth between the workbooks. How do I refer to a workbook when I don't know the name of it and what would the code look like for the button?

Sorry for the long explanation - it's probably a short solution!

I can't say enough good things about this forum!! I have looked at a lot of other places and this is the best organized IMHO.
THANKS to everyone here. I would have given up long ago with out you all.

[Glaswegian]

Hi TButhe

One way that springs to mind is to use an Input Box and get the user to tell you their file name. You assign that to a variable and you can use it in your code to reference that particular workbook. You wouldn't need to switch back and forth - just refer directly to the variable. Perhaps something like this

Sub GetAFile()
Dim strOldFile

strOldFile = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),*.xls", Title:="Select your file")
	If strOldFile = False Then Exit Sub
		Workbooks.Open Filename:=strOldFile
End Sub

You can add a command button like this

Sub AddAButton()
Dim ole As OLEObject

With ActiveSheet
	Set ole = .OLEObjects.Add(Classtype:="Forms.CommandButton.1", Left:=10, Top:=20, Height:=25, Width:=75)
		With ole.Object
			.Caption = "Click to begin transfer"
			.OnAction = "RunMyMacro"
		End With
End With
End Sub

You would need to play around with the Top and Left settings to work out exactly where it would go on your sheet.

Hope this gives you some ideas.

Regards

[TButhe]

THANKS!! I'll try that. Looks like what I am after. Sorry I didn't post back sooner - I'm not getting e-mail notifications right now . I'm still learning so I may have more questions. I'll post back and let you know how it goes. Thanks again

[TButhe]

Thanks, Glaswegian!

That got me started in the right direction. One more thing though if you don't mind...

I can get the data selected and copied from the old file - that is open with the code that was given above - but how do you use VBA to change the focus to the new worksheet that is open. Should I put code in that saves the document to their My Documents folder with a standard name before I begin copying the data? They will all have that folder - all computers are standardized and locked down. Then use that file name to switch the focus to it? The users will receive it as a template and will only need to run this code once. I can just inform them that once this process is run then their file will be in their My Documents folder with whatever name. What do you think? I know it is hard not knowing how we are set up and how this will be used.

I'm beginning to wonder if it is worth the trouble.


As always, Thank you for taking your time to help a newbie.

[Glaswegian]

Hi TButhe

Generally speaking you don't need to have one workbook with the focus or being active when copying data across. This, of course, depends on your code and what you are actually doing. Last year, I had to create a large macro to take data from one workbook and put it in specific cells and text boxes in the new version I had created. At no time did I specifically make one particular workbook 'active'. It was all done using something like

Workbooks(source).Sheets("Old"),Cells(1,1) .Value = Workbooks(destination).Sheets("New").Cells(2,1).Value

where the source and destination workbooks were assigned variables. Just an example - as I said, it all depends on the way you are taking the data from one book and putting it in the other.

If you are using my suggestion of the Input Box to obtain the relevant file name then you already have that name assigned to a variable. You can use that if you need it.

Sorry if that's not very helpful - without full details it's a bit difficult to be specific.

Post back if there is anything else - and I'll try and be a bit more useful to you!!

Regards

[TButhe]

Thanks - again. I am working on it now and will post back if I can't work my way through it. I really do appreciate your help.

Tracy

[TButhe]

I'm closing this for now. I won't have time to work on it for awhile. Thanks again for all the GREAT help!!