Solved: Transferring data from one workbook to another

[primaryteach]

Hi all,

I would like to be able to transfer some specific data from cells in one workbook (source) to another wookbook that is already opened (target). As the name/location on the harddrive of my source changes I have started my code of as:
Private Sub CommandButton3_Click()

Dim wbTgt
Dim wbSource

Application.ScreenUpdating = True

wbSource = Application.Dialogs(xlDialogOpen).Show
If wbSource = False Then Exit Sub


However, I then get stuck. My data is in different columns on different worksheets within the 'source' workbook. E.g. In the source wookbook I have a worksheet named "Reading" that I want A3:A36 copied and pasted into A3:A36 of "Reading" in the 'target' wookbook. Then C3:C36 copied and pasted from a sheet named "Writing" from the source workbook on another sheet into the target workbook, etc.

I don't know how to code this so the macro will copy from one workbook/worksheet and place it into another workbook on a specifically named worksheet, then change back to the source data to copy from another worksheet and paste again to the target workbook.

Any ideas?

Simon
Primary school teacher

[lucas]

Hi Simon,
Here's one way to do what you want. The code is in the runme.xls:

Option Explicit
Sub PutDataInClosedWorkbook()
Dim wb As Workbook
Dim sFileName As String
Application.ScreenUpdating = False ' turn off the screen updating
'Show the open dialog and pass the selected _
file name to the String variable "sFileName"
sFileName = Application.GetOpenFilename
'They have cancelled.
If sFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(sFileName)

' Set wb = Workbooks.Open("C:\Users\Steve\Documents\target.xls", True, False)
' read data to the target-from the source workbook...wb is the target
wb.Worksheets("Reading").Range("A3", "A36").Formula = ThisWorkbook.Worksheets("Reading").Range("A3", "A36").Formula
wb.Worksheets("Writing").Range("C3", "C36").Formula = ThisWorkbook.Worksheets("Writing").Range("C3", "C36").Formula
wb.Save
wb.Close
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
MsgBox "data transfer complete"
End Sub

[primaryteach]

Lucas,

Thanks for this, it works a treat, but I must have made an error/boob in my explanation, as I need the macro to run in the 'target' file, not from the 'source' file where the data is kept. How do I adjust the code to make this happen?

Simon

[lucas]

Maybe like this Simon:

[primaryteach]

Thanks again Lucas - the simplest things in life is what I struggle with!
On that note, how do I mark the post as solved?

Simon

[lucas]

You're welcome.
Use the thread tools at the top of the page.