Solved Delete Data from a number of worksheets

June 18, 2013 at 13:22:55
Specs: Windows 7
Hi guys, not really looked into any forums as yet but this one is very tricky for me.

WS 'LEGEND' has a list of names in Range "AP2:AP49". There may be 30 names or 25, or 45, but they are in alphabetical order with blanks at the bottom

These names relate to the titles of worksheets

What I would like to do is to delete the information in a range of cells ("K:UE") on each worksheet that is listed in AP2:AP49

I then need to add some data which I think I know how to do.

I am creating quite a large VBA to which I need to add your response into, so some help along those lines as well, subject to how your answer appears, would be appreciated


See More: Delete Data from a number of worksheets

Report •


#1
June 18, 2013 at 14:49:25
✔ Best Answer
Sub DelData()
'Determine last Row with data in Legends!A2:A49
 lastRw = Sheets("Legends").Range("A49").End(xlUp).Row
'Loop through Sheet Names, clearing data from listed Sheets
  For sht = 2 To lastRw
   Worksheets(Sheets("Legends").Range("A" & sht).Value).Range("K:UE").ClearContents
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 22, 2013 at 11:01:20
'deletes all entered scorercards on a ws

'Determine last Row with data in Legends!AP2:AP49
 lastRw = Sheets("LEGEND").Range("AP49").End(xlUp).Row
'Loop through Sheet Names, clearing data from listed Sheets
  For sht = 2 To lastRw
   Worksheets(Sheets("LEGEND").Range("AP" & sht).Value).Range("K:UE").ClearContents
  
  Sheets("LEGEND").Select
    Range("AU1:BC31").Select
    Selection.Copy
        shtname = Sheets("LEGEND").Range(("AP" & sht).Value)
    
    With Sheets(shtname).Range("K1")
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteFormats
      .PasteSpecial Paste:=xlPasteColumnWidths
   End With
   Application.CutCopyMode = False
   Next

Your vba works very well indeed. What I would like to do is , when a sheetname is cleared is to then apply the following

' Sheets("LEGEND").Select
Range("AU1:BC31").Select
Selection.Copy
shtname = Sheets("LEGEND").Range( 'sheetname that has had its contents deleted')

With Sheets(shtname).Range("K1") '

I have a feeling that it could be a really easy command or I have to rethink the process,
any ideas


Report •

#3
June 22, 2013 at 14:45:48
Thanks for your help on this one, with a slight manipulation of what you provided and a few extra lines, I am able to do exactly as I require

Report •

Related Solutions

#4
June 22, 2013 at 15:35:03
I'm glad you worked it out.

I may have mentioned this before, but I'll mention it again because it is important...

You should try to eliminate the use of Select in your code. Rarely, if ever, do you need to Select an Excel object to perform a VBA operation on it. Selecting objects seriously slows your code down and makes it very inefficient.

All of the Selects also make the code cumbersome to read and follow while debugging.

The following 2 snippets do the same thing, but the second one is a much more efficient method.

Imagine reading through a very long macro with lots of Copy operations. Which method would you rather read?

Sheets("LEGEND").Select
Range("AU1:BC31").Select
Selection.Copy

or

Sheets("LEGEND").Range("AU1:BC31").Copy

Which one is easier to read and gets the reader to the point faster? VBA feels the same way.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question