Excel VBA to loop through worksheets clearing entire rows from row 10 to last used row

0
votes

I have a workbook that has a 'Master Sheet' worksheet, which all products are entered on and updated on, and 'Stage Sheet' worksheets that are numbered 1 - 24. Eg. 'Stage 1 Sheet', 'Stage 2 Sheet' etc.This shows the layout of the worksheets. Stages 6 - 24 are hidden.

I want to have a macro that clears all the Stage Sheet's before re-entering(I have a macro for this part already) the related products from the Master Sheet. I have struggled with getting it to loop through so many worksheets and then having to find the last row for each of those worksheets.

The Stage sheets are exactly the same as this Master Sheet except that each stage has only the products(rows) relating to that stage.

Every worksheet needs to be cleared from Row 10 down to the last used row.Last used row can be determined by using Column A. Thanks in advance for your help.

1
vbaexcel

1 Answers

1
votes

You have to make a string and an integer that forms the name of the current file. Than you need to activate the sheet and you have to go to delete the whole square.

pseudo code:

Dim sheetName As String
For i = 1 To 24
 sheetName = "Stage " & i & " Sheet" 
 Sheets(sheetName).Activate   
 .Range("A10:A" & .range("A10").End(xlDown).row).ClearContents
Next i

I hope that this will help you getting your job done.