CODEWITHSUNDEEP
excelvba
Alex
Alex

Reputation: 47

Range().Select and error 1004 issues

I am trying to fun a selection execution from CoverPage Sheet of a excel workbook. Its intended to select a cell range on the next page and do whatever.

ThisWorkbook.Sheets("Purpose").Range(Cells(48, 1), Cells(48, 9)).Select

With Selection
    .Borders (xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With

However the code runs when I have the sheet "Purpose" opened. But once I run it from the coverpage it fails. Its something to do with the referencing I guess, however I tried other solutions I could find and it would not work.

Anyhelp is apprciated, Steven

Upvotes: 1

Views: 414

Answers (1)

David Zemens
David Zemens

Reputation: 53623

If sheet name Purpose is not active when you run the macro, you will get an error since Excel cannot Select a range on an inactive worksheet.

Revise:

With ThisWorkbook.Sheets("Purpose").Range(Cells(48, 1), Cells(48, 9))
    .Borders (xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With

Upvotes: 2

Related Questions