Trouble with runtime 1004:Method 'Range' of object '_Worksheet' failed 1

[bluegnu]

OK, the situation is this:

I have an old excel template that we use in our office. In order for the workbooks that use the old template to be compliant it has to be upgraded to a new approved template.

I could manually copy everything, but there are many workbooks and many worksheets.

I tried to create some code that would basically take the old workbook (downgrade.xls) and copy the appropriate data to the new template ("upgrade.xls")

The template consists of different, the ones I am copying all spin off a sheet called "Template".

The first thing that the code does is look at the sheet in downgrade.xls, take the name of that sheet and a couple of other bits of info. Then I go to upgrade.xls, copy the template sheet and rename the copy to be the name of the old sheet.

Then I go back to downgrade.xls and attempt to copy a range of data from the sheet. This is where it goes wrong:

    If wsu.Range("A16") = "Spec Ref" Then
    wsu.Range(Cells(18, 1), Cells(lRow, 30)).Copy
    Else
    wsu.Range(Cells(16, 1), Cells(lRow, 30)).Copy
    End If

where wsu = the worksheet (see full code below) and lRow = last unused row
I have tried setting wsu as teh active sheet and then replacing wsu with activesheet but it doesn't work. I have also tried using Rows rather than range, but still no luck.

The full code is below (sorry it's not very clean at the moment)

Dim wsu As Worksheet
Dim wsn
Dim wst
Dim wso
Dim lRow As Long
Application.DisplayAlerts = False
'Activate the old spreadsheet
    For Each wsu In ThisWorkbook.Worksheets
    If wsu.Name <> "Contents" Then
    If wsu.Name <> "Status" Then
    If wsu.Name <> "Introduction" Then
    If wsu.Name <> "Template" Then
    If wsu.Name <> "Upgrade" Then
    wsu.Activate
    Let wst = wsu.Range("D4")
    Let wso = wsu.Range("D6")
    Let wsn = wsu.Name
'Activate upgrade spreadsheet to insert new worksheet
    Windows("upgrade.xls").Activate
    Worksheets("Template").Copy Before:=Worksheets("Template")
    Worksheets("Template (2)").Visible = True
    Worksheets("Template (2)").Range("D4") = wst
    Worksheets("Template (2)").Range("D6") = wso
    Worksheets("Template (2)").Rows("19:26").Delete
    Worksheets("Template (2)").Name = wsn
    
    
'Add to contents of upgrade
    If Sheets("Contents").Range("B6") = "E1" Then
        Sheets("Contents").Range("B6") = wsn
        Else
        If Sheets("Contents").Range("B7") = "E2" Then
        Sheets("Contents").Range("B7") = wsn
        Else
        If Sheets("Contents").Range("B8") = "E3" Then
        Sheets("Contents").Range("B8") = wsn
        Else
        Sheets("Contents").Rows("7:7").EntireRow.Insert
        Sheets("Contents").Rows("8:8").Copy Destination:=Sheets("Contents").Rows("7:7")
        Sheets("Contents").Range("B7") = wsn
    End If
    End If
    End If
    Windows("downgrade.xls").Activate
      
    
''''''Copy data from active sheet
    
    ' Find the FIRST EMPTY row by adding 1 to the last row
    lRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row + 1
    
    'Copy all contents of active sheet
    If wsu.Range("A16") = "Spec Ref" Then
    wsu.Range(Cells(18, 1), Cells(lRow, 30)).Copy
    Else
    wsu.Range(Cells(16, 1), Cells(lRow, 30)).Copy
    End If
    'Paste to upgrade
    Windows("upgrade.xls").Activate
    Sheets(wsn).Select
    ActiveSheet.Range("A18").Select
    ActiveSheet.Paste
    
    End If
    End If
    End If
    End If
    End If
    Next wsu
    Application.DisplayAlerts = True
    Windows("upgrade.xls").SaveAs Filename:="Upgrade_To_Rename.xls"

hope this all makes sense, any ideas what the problem is? Many thanks

 

[combo]

Instead of wsu.Range(Cells(18, 1), Cells(lRow, 30)).Copy use:

wsu.Range(wsu.Cells(18, 1), wsu.Cells(lRow, 30)).Copy
' or
Range(wsu.Cells(18, 1), wsu.Cells(lRow, 30)).Copy

combo

 

[bluegnu]

Brilliant! So simple, but it worked. Many thanks