D
dean.brunne
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(xlCellTypeBlanks).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
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(xlCellTypeBlanks).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