Copy data to archive

[emukiss10]

Hello.

I need help.

I have a file named ARCHIVE. It has only headers. I need a VBA script to copy contents of another workbooks to Archive ranged "B2" to Lastrow "Z" and it should be inserted to next free row in Archive.

Couple ppl will work on some files and at the end they should add their work to Archive. So i should expand. I would like to add this macro to a button and it should pop msgbox with YES/NO confirmation "Do you want to add your work to archive?". The copy should be made by exporting current work to archive not by importing files to archive.

Its beyond my knowledge.

Best Regards
W.

 

[ranman256]

you cannot export data.
The target file ARCHIVE will have to be opened, then locate the last row, then paste new values.

 

[emukiss10]

That can be done. Open it with macro, copy data, save and close. BUT I cannot do it myself.. Above my skills

 

[emukiss10]

Please make it work

open other xls file, copy everything from sheet 1 A2 to ZlastRow activeWB into sheet 1 opened (archive) under last row so it can expand.

 

[emukiss10]

Bump!

 

[emukiss10]

Ive managed to do it myself - it took me very long time thou'

Dim WB1 As Workbook
Dim WB2 As Workbook
Dim LastRow As Long


Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open("C:\MyArchiveFile.xlsx")


With WB2.Sheets("MyArchiveSheet")
    LastRow = .Range("A" & .Rows.count).End(xlUp).row
End With

WB1.Sheets("MySourceSheet").Range("AB:Z" & LastRow).Copy
With WB2.Sheets("MyArchiveSheet").Cells(Rows.count, 1).End(xlUp).Offset(1, 1)
        .PasteSpecial xlPasteColumnWidths
        .PasteSpecial xlPasteValues, , False, False
        .PasteSpecial xlPasteFormats, , False, False
End With

WB2.Save
WB2.Close

Close Thread

Thank You