Hi,

My code works when stepped through where the sheet CSV is opened but
does not work in a button when the sheet is not open. The goal of the
code is to format then create a copy of the sheet in a separate
workbook. Please advise of more efficient code than below:

Sub FormatCSV()
Dim r As Range
On Error Resume Next 'In case there are no blank rows
Worksheets("CSV").Columns("A").SpecialCells(xlCell TypeBlanks).EntireRow.Delete
On Error GoTo 0

Set r = Worksheets("CSV").Range("E1", Range("E10000").End(xlUp)) '
ERROR HERE
r.Value = "Default Project"
Worksheets("CSV").Columns("F:G").Select
Selection.Delete Shift:=xlToLeft
Worksheets("CSV").Rows("1:1").Select
Selection.Insert Shift:=xlDown

Application.Goto Reference:="header"
Selection.Copy
Sheets("CSV").Select
Range("A1").Select
ActiveSheet.Paste

Worksheets("CSV").Copy
ActiveSheet.Select

End Sub

The error occurs because Range("E10000") isn't qualified.
Try this:
Set r = Worksheets("CSV").Range("E1", _
Worksheets("CDV").Range("E10000").End(xlUp))

Efficiency? Get rid of the Selects as much as possible, e.g.:
Worksheets("CSV").Columns("F:G").Delete Shift:=xlToLeft

Hth,
Merjet