copying data from current workbook to another different ranges

excel 2003
need a vba sub
to be placed behind a command button


2 workbooks
1.  workingcopy.xls
1st workbook can be placed and used from anywhere
sheet name =  sheet1


2.
C:\PROGRAM FILES\DATA1\FINAL.XLS
always in this location
sheet name =  sheet1

Column Headers in BOTH worksheets
A1:AU1

The data will always start on row2 in  workingcopy.xls

THE amount of rows will always vary...

copying data from  workingcopy.xls  to final.xls




Thanks
fordraiders





LVL 3
FordraidersAsked:
Who is Participating?
 
Jignesh TharConnect With a Mentor Senior ManagerCommented:
Use below in workingcopy

Sub CopyData()
    Range("A2:AU65536").Copy
    Workbooks.Open Filename:= _
        "D:\Documents and Settings\jignesh.thar\My Documents\FINAL.xls"
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWindow.Close
    Range("A1").Select
End Sub

Open in new window

0
 
Jignesh TharSenior ManagerCommented:
Replace path "D:\Documents and Settings\jignesh.thar\My Documents\FINAL.xls" with C:\PROGRAM FILES\DATA1\FINAL.XLS
0
 
TinTombStoneCommented:
Assuming that the headers in both Working and Final are the same

Run this from workingcopy.xls

Sub CopyToFinal()

Dim rng As Range

    Set rng = Range("'[C:\PROGRAM FILES\DATA1\FINAL.XLS]Sheet1'!A1")

    Range("A1").CurrentRegion.Copy rng

End Sub
0
 
TinTombStoneCommented:
Sorry, scrub the above.  I'm just not thinking straight, jigneshthar has it right
0
 
FordraidersAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.