Clear data before updating

Abdulkhadar

Board Regular
Joined
Nov 10, 2013
Messages
165
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.

Rich (BB code):
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
 
Upvote 0
Solution
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.

Rich (BB code):
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
 
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top