Clear data before updating

[Abdulkhadar]

Hello Excel Experts,
I want to add a code to clear all sheets data except sheet main (Sheet Name) from 4th row to last used row before updating the sheets in below VBA.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Range("F" & Rows.Count).End(xlUp).Row
    Dim AccNum As Range
    For Each AccNum In Range("F4:F" & bottomA)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, -4)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum.Offset(0, -3)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, -2)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
    Next AccNum
    Application.ScreenUpdating = True
End Sub

Thanks in advance

 

[Peter_SSs]

Assuming those sheets, if they have any data at all, that data starts in row 1, then try this in a copy of your workbook.

Sub CopyData()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    If ws.Name <> "main" Then ws.UsedRange.Offset(3).ClearContents
  Next ws
  
  'Rest of your code goes here
  
  
End Sub

 

[Abdulkhadar]

Assuming those sheets, if they have any data at all, that data starts in row 1, then try this in a copy of your workbook.

Sub CopyData()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    If ws.Name <> "main" Then ws.UsedRange.Offset(3).ClearContents
  Next ws
  
  'Rest of your code goes here
  
  
End Sub

Nice Sir, Thanks once again

 

[Peter_SSs]

Nice Sir, Thanks once again

No problem, glad to help.

 

[Abdulkhadar]

No problem, glad to help.

Hello Sir,
I want to keep one sheet "RP" as it is. how can i add code in your VB.
Thanks

 

[Peter_SSs]

Hello Sir,
I want to keep one sheet "RP" as it is. how can i add code in your VB.
Thanks

We already had one sheet (main) as it was. Just change that to RP.
Or do you really mean that you want to keep two sheets as is (main and RP)?

 

[Abdulkhadar]

We already had one sheet (main) as it was. Just change that to RP.
Or do you really mean that you want to keep two sheets as is (main and RP)?

Thank for quick rply sir
want to keep as it is 2 sheet main & RP

 

[Peter_SSs]

want to keep as it is 2 sheet main & RP

Try

If ws.Name <> "main" And ws.Name <> "RP" Then ws.UsedRange.Offset(3).ClearContents

 

[Abdulkhadar]

Try

If ws.Name <> "main" And ws.Name <> "RP" Then ws.UsedRange.Offset(3).ClearContents

Thanks again, i m using "&" instead of "And"

 

[Abdulkhadar]

Thanks again, i m using "&" instead of "And"

Hello Sir,
I want to clear only data from A5 to E cells (A5:E), (i.e from 5th row to last used cell upto E column). please provide me the code .
Thanks in advance....

If ws.Name <> "main" And ws.Name <> "RP" Then ws.UsedRange.Offset(3).ClearContents