Archiving Results

Hi,

I want to archive results from one spreadsheet to another. I have a macro button to do this (code from a colleague below) and it works when I use it initially. However, if I run the macro again it then fails on the line in bold. I'm not sure why this would be. I have tried stepping through the code and it seems to select the right cell but won't paste.

Any ideas would be appreciated.

Many Thanks,
Zoe

'set to the current active workbook (the source book)
Set Test061020 = ActiveWorkbook

'get the active sheetname of the book
strName = "Archive"

'open a workbook that has same name as the sheet name
Set TestArchive = Workbooks.Open("Link to archive")

 
'activate the source book
 Test061020.Activate

'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False

'copy the range from source book
Worksheets("Archive").Select
Worksheets("Archive").Range("A2:I2").Copy

'activate the source book
TestArchive.Activate

Sheets("Sheet1").Unprotect Password:="Test"

 
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("A1").Select 'Selects cell A2 in  archive worksheet

        If Worksheets("Sheet1").Range("A1").Offset(1, 0) <> "" Then 'Finds first empty line in archive worksheet
        Worksheets("Sheet1").Range("A1").End(xlDown).Select
        End If

    'Steps through the cells of the first empty row and populates the cells with data
    ActiveCell.Offset(1, 0).Select
    ActiveCell.PasteSpecial Paste:=xlPasteValues
   
  Sheets("Sheet1").Protect Password:="Test"

Application.ScreenUpdating = True

'save the target book
TestArchive.Save

'close the workbook
TestArchive.Close

'activate the source book again
Test061020.Activate
Worksheets("Info").Select
MsgBox ("Data archived")
'clear memory
Set Test061020 = Nothing
Set TestArchive = Nothing

End Sub

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)