Member_2_5230414
asked on
Help with current Macro - deleted and renews speadhseet but i need to just over write the data on it
Hi all,
Currently my Macro deletes the sheet "Results" and re-creates it.
Is it possible to just overwrite the data on the sheet without deleting the actuall sheet itself???
Currently my Macro deletes the sheet "Results" and re-creates it.
Is it possible to just overwrite the data on the sheet without deleting the actuall sheet itself???
Sub TidyUp()
Application.ScreenUpdating = False
On Error Resume Next
Set ResultSht = Sheets("Result")
On Error GoTo 0
If Not (IsEmpty(ResultSht)) Then
Application.DisplayAlerts = False
Sheets("Result").Delete
Application.DisplayAlerts = True
End If
ActiveWorkbook.Worksheets("Info").Copy After:=Sheets(Sheets.Count)
Set ResultSht = Sheets(Sheets.Count)
ResultSht.Name = "Result"
Cells.NumberFormat = "General"
Range("1:4").EntireRow.Delete
Range("N1") = "Number"
Range("O1") = "Total"
Idx = 1
lastRow = Range("A" & Rows.Count).End(xlUp).Row
rwIdx = 2
AreaIsOn = True
Do While rwIdx <= lastRow
If Cells(rwIdx, 1) = "No." Then
AreaIsOn = True
If Range("J" & rwIdx) = "Stall" Then
Range(Range("J" & rwIdx), Range("J" & rwIdx).End(xlDown)).Delete Shift:=xlToLeft
End If
Cells(rwIdx, 1).EntireRow.Delete
Idx = Idx + 1
Range("N" & rwIdx).Value = Idx
rwIdx = rwIdx + 1
ElseIf AreaIsOn = True And Cells(rwIdx, 1) = "" Then
AreaIsOn = False
Cells(rwIdx, 1).EntireRow.Delete
ElseIf AreaIsOn = False And (Cells(rwIdx + 1, 1) <> "" Or Cells(rwIdx + 2, 1) <> "" Or Cells(rwIdx + 3, 1) <> "") Then
Cells(rwIdx, 1).EntireRow.Delete
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Else
Range("N" & rwIdx).Value = Idx
rwIdx = rwIdx + 1
End If
Loop
Range(Range("O2"), Range("O" & Range("A1").End(xlDown).Row)).FormulaR1C1 = "=(RC[-5]+RC[-3])/2"
Range("N1").End(xlDown).ClearContents
Range("A1").Activate
Application.ScreenUpdating = True
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did the above and get an error here (runtime error 1004) ResultSht.Name = "Result"
Sub TidyUp()
Application.ScreenUpdating = False
On Error Resume Next
Set ResultSht = Sheets("Result")
On Error GoTo 0
If Not (IsEmpty(ResultSht)) Then
Application.DisplayAlerts = False
Sheets("Result").Cells.Clear
Application.DisplayAlerts = True
End If
ActiveWorkbook.Worksheets("Info").Cells.Copy Sheets("Result").Cells
Set ResultSht = Sheets(Sheets.Count)
ResultSht.Name = "Result"
Cells.NumberFormat = "General"
Range("1:4").EntireRow.Delete
Range("N1") = "Number"
Range("O1") = "Total"
Idx = 1
lastRow = Range("A" & Rows.Count).End(xlUp).Row
rwIdx = 2
AreaIsOn = True
Do While rwIdx <= lastRow
If Cells(rwIdx, 1) = "No." Then
AreaIsOn = True
If Range("J" & rwIdx) = "Stall" Then
Range(Range("J" & rwIdx), Range("J" & rwIdx).End(xlDown)).Delete Shift:=xlToLeft
End If
Cells(rwIdx, 1).EntireRow.Delete
Idx = Idx + 1
Range("N" & rwIdx).Value = Idx
rwIdx = rwIdx + 1
ElseIf AreaIsOn = True And Cells(rwIdx, 1) = "" Then
AreaIsOn = False
Cells(rwIdx, 1).EntireRow.Delete
ElseIf AreaIsOn = False And (Cells(rwIdx + 1, 1) <> "" Or Cells(rwIdx + 2, 1) <> "" Or Cells(rwIdx + 3, 1) <> "") Then
Cells(rwIdx, 1).EntireRow.Delete
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Else
Range("N" & rwIdx).Value = Idx
rwIdx = rwIdx + 1
End If
Loop
Range(Range("O2"), Range("O" & Range("A1").End(xlDown).Row)).FormulaR1C1 = "=(RC[-5]+RC[-3])/2"
Range("N1").End(xlDown).ClearContents
Range("A1").Activate
Application.ScreenUpdating = True
End Sub
Just delete the line
ResultSht.Name = "Result"
ResultSht.Name = "Result"
I think you would get the same results if you even deleted the line
Sheets("Result").cells.cle ar
Sheets("Result").cells.cle
pls try
Open in new window
Regards