Copying Data from one spreadsheet to another

[R. Woltmann]

Can you help me to write a macro to copy data from any one of a number of spreadsheets
to another spreadsheet. Macro will need to be able to choose which spreadsheet to copy the data from. Thank you

 

[JoeMo]

Can you help me to write a macro to copy data from any one of a number of spreadsheets
to another spreadsheet. Macro will need to be able to choose which spreadsheet to copy the data from. Thank you

Can you provide some more detail on what you want to do? Will the macro be run from the workbook containing the sheets you want to copy data from? Will the pasting of the copied data be to a sheet in the originating workbook or another workbook? Do you want to copy the entire sheet or just part of it (and which part)? Where does the paste begin on the destination sheet? etc

 

[R. Woltmann]

The macro will be placed in the data sheet to which the data is being copied. Only a portion of the data from one worksheet tab will be copied.

 

[R. Woltmann]

Spreasheet A to which data is to be copied will contain the macro. Data will be copied from one of the worksheets "Data for Graphs" in spreadsheet B. The area ro be copied is A:6 to W:14. THe data will be copied to the same worksheet in spreadsheet A and placed at A:19 to W:27

 

[JoeMo]

Spreasheet A to which data is to be copied will contain the macro. Data will be copied from one of the worksheets "Data for Graphs" in spreadsheet B. The area ro be copied is A:6 to W:14. THe data will be copied to the same worksheet in spreadsheet A and placed at A:19 to W:27

Can I assume that "spreadsheet" A means workbook A and "spreadsheet" B means workbook B? What is the name of the sheet in A to which the data will be pasted (Sheet1 or ...)? What is the name of workbook B (include the file extension please)? Also, will an input box that asks the user to enter the sheet name from which data will be copied work for you?

 

[R. Woltmann]

They are Workbook A and B. The sheet fom which data will be copied or pasted is called "Data for Graphs". An input box would work to choose the workbook from which data will be copied. Both workbook A & B are identical in format and the sheets they contain Both are .xls files

 

[JoeMo]

Try this:

Sub GetData()
Dim sBk As Workbook, rBk As Workbook
Dim sSh As Worksheet, rSh As Worksheet
Dim msg As String, fName As String

Set rBk = ThisWorkbook
msg = "Enter the name of the workbook containing the data you want to copy."
fName = InputBox(msg)
If fName = "" Then Exit Sub
If InStr(1, fName, ".xls") > 0 Then
    If WorkbookOpen(fName) Then Set sBk = Workbooks(fName)
Else
    If WorkbookOpen(fName & ".xls") Then
        Set sBk = Workbooks(fName & ".xls")
    Else
        MsgBox "Open the workbook " & fName & " and then run this macro again"
        Exit Sub
    End If
End If
With sBk.Sheets("Data for Graphs")
    .Range("A6:W14").Copy
End With
With rBk.Sheets("Sheet1")  'Change sheet name in destination workbook to suit
    .Range("A19").PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False
End Sub
Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
   
WorkBookNotOpen:
End Function