Multiple workbook copy and paste programming help

I am trying to write a program to copy an array of data from a specific worksheet from one workbook and then open another workbook and paste it to a specific worksheet there.  I am successful at doing this within a workbook, but not involving two workbooks.  Please help!

It may be better to post the code you have now and we can add somthing to open and paste into another worksheet in another book.

Irrespective, I would have a couple of questions

1. Do you want the 'other' workbook to be dynamic or is it the same location and name each time or is it a new workbook?
2. Ditto the worksheet
3. Do you want to append the data to what is already in the sheet, overwrite it or start with a new sheet?

Let us have a bit more information and we will be able to solve your issue

Thanks for the reply.

I'l answer your questions and then have the code:

1. It will be the same loction and the same name each time.

2. Same with the worksheet.

3. The data will be appended each time the macro is run.

Please be aware, I am new to macros and not very good at them, but, I do understand how to read the code.

Sub MacroAvg()
'
' MacroAvg Macro
'

'
    Range("A7:E23").Select
    Selection.Copy
    Windows("Average Record File Test.xls").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

mtholme

you might find this helpful, we had a similiar project where we had a row list of names and had to transpose(pivot) the names into another workbook so the process toggled back and forth between the two workbooks, there may be some elements of code that don't pertain to your need but you can see how we got the two workbooks to work in synch with each other, hth.

***************************************************************************

Open two separate Workbooks(data.xls & empty.xls)
change sheet name to 'data','empty' in both workbooks(delete unused sheets ex..sheet2,sheet3)
Copy and Paste below in a new Module.
Run script from Module level>JoinCells>Run

****Make sure to change Range Selection to match number of rows in Worksheet******

============================================================================================

Sub JoinCells()
 For Each Cell In Range("A2:A56311")    'modify Range selection for specific worksheet
    Selection.EntireRow.Insert
    Range("A2:A254").Select
    Selection.Copy
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 1
    Columns("A:A").ColumnWidth = 15.43
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Cut
    Windows("empty.xls").Activate
    ActiveSheet.Paste
    Range("A2").Select
    Windows("data.xls").Activate
    Selection.Delete Shift:=xlUp
    Range("A1:A253").Select
    Selection.EntireRow.Delete
    Range("A1").Select
    Selection.EntireRow.Insert
    Range("A2:A254").Select
    Selection.Copy
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Cut
    Windows("empty.xls").Activate
    ActiveSheet.Paste
    Range("A3").Select
    Range("A1").Select
    Selection.EntireRow.Insert
    Range("A1").Select
    Selection.EntireRow.Insert
    Windows("data.xls").Activate
    Range("A1").Select
    Selection.EntireRow.Delete
    Range("A1:A253").Select
    Selection.EntireRow.Delete
    Range("A1").Select
 Next Cell
End Sub

This is highly theorectical as I am sure that the range each time in the source sheet will change, but let's presume it doesn't.

The code should go in the workbook that contains the code you are copying (or your personal workbook), although the worksheet with the range to be copied MUST be active. It will then open the named workbook at the file location given (you need to change this to whatever is actual) and it will copy the range in the originally active workbook and worksheet, find the last row in column A of the worksheet in the destination worksheet (Currently named Sheet1, chnage that to whatever it is) and pastes it there in one fell swoop.

Here is the code. Post back if you need more help

Sub CopyOpenAndPaste()
Dim wbSource As Workbook, wbDest As Workbook
Dim wksSource As Worksheet, wksDest As Worksheet
Dim lLastDestRow As Long

Set wbSource = ActiveWorkbook
Set wksSource = wbSource.ActiveSheet
Set wbDest = Workbooks.Open("C:\Average Record File Test.xls") 'Change to filename and path
Set wksDest = wbDest.Worksheets("Sheet1") 'Change this to what the sheet name is

lLastDestRow = wksDest.Cells(wksDest.Rows.Count, "A").End(xlUp).Row

wksSource.Range("A7:E23").Copy Destination:=wksDest.Range("A" & lLastDestRow)
End Sub

How about this?

Sub MacroAvg()
Dim wb As Workbook
dim this As Activesheet

    Set this = Activesheet
    Set wb = Workbooks.Open("C\MyDir\Average Record File Test.xls")
    this.Range("A7:E23").Copy wb.Worksheets("Sheet1").Range("A1").End(XlDown).Offset(1, 0)
End Sub

Thanks

Thanks for the help.