Simple Code to Delete Range of Columns on All Sheets in WB

[z3115]

Hello,

I have macro now that copies 10 sheets from my workbook and puts them into their own .Xlsx workbook. Once that happens, I want to delete (or clear contents on) a range of columns (P through Z) in all 10 of those sheets (in the new .xlsx file I just made).

I must have tried 20 different ways and have still be unable to do it, but I'm very new to VBA. All I need is the piece that deletes the columns, the rest of the macro is fine.

Thank you for the help!

 

[sitewolf]

<code>
ActiveSheet.Range("P:Z").Select
Selection.ClearContents
Worksheets(ActiveSheet.Index + 1).Select

something like that work? not sure if you'd also need an end if
</pre></code>

 

[z3115]

<code>
ActiveSheet.Range("P:Z").Select
Selection.ClearContents
Worksheets(ActiveSheet.Index + 1).Select

something like that work? not sure if you'd also need an end if
</code>

When I put this into my code and run it, it highlights the right columns, but it still doesn't clear those cells. There' s literally like 5-10 cells per sheet with just some scribble and check formula that I CANNOT get rid of, so frustrating. When I manually right click the range that your code leaves selected and hit "clear contents" it works like a charm. ANY ideas why the macro which seems to be doing the same thing wouldn't work? This is driving me crazy, I thought this would be the easy part! Thank you so much for any help!

 

[Gavin T]

If the new workbook is the active one then this will work.

For Each wSheet In ActiveWorkbook.Worksheets
    wSheet.Range("P:Z").ClearContents
Next wSheet

 

[sitewolf]

realizing for one, there's need to repeat the first 2 lines on each sheet, so we need to work in a loop.....right now it should properly clear those columns but so far all we're telling it to do is go to the next sheet...where it currently stops

....or more succinctly, the code that just got posted while I was typing

 

[Caribeiro77]

Maybe this i'll do it to...

Sub deleteall()


Dim nrsheets As Worksheet


For Each nrsheets In ThisWorkbook.Worksheets


nrsheets.Range("P:Z").ClearContents


Next nrsheets


End Sub

 

[z3115]

Thank you all for the help! I used Gavin T's code and it seemed to work, but the others may have done the trick as well. Have a good day!

 

[Gavin T]

You're welcome.