Replicate column formats on multiple sheets

    Hi everyone,
    I'm trying to hide columns depending on whether one of the cells is blank or not, and I need to apply this to several sheets, all of which have the same layout and formatting.

    I'm very much a beginner with VBA, so if anyone can suggest a better way of going about it I would really appreciate it.



    Thankyou all,
    Matt

    Re: Replicate column formats on multiple sheets


    Hi Mateous,
    Nice to meet in Ozgrid.

    Code
    Dim ws As Worksheet
For Each ws in ThisWorkbook.Worksheets
ws.Select
'copy your macro
Next


    Regards, junho

    Re: Replicate column formats on multiple sheets


    Hi Junho,
    Thankyou for your reply!
    Sorry I should have mentioned that I don't want to apply the macro to all worksheets, just certain ones.

    Also, I've been told that using "Select Case" statements would be more efficient than using "If Else" statements, but I don't know how I'd put my code into a "Select Case" type statement?

    Thanks again,
    Matt

    Re: Replicate column formats on multiple sheets


    hi, write like this.

    Code
    select case ws.name
case "sheet1","sheet2","sheet3"
'
case "sheet4"
'
case else
'
end select


    regards, junho

    Re: Replicate column formats on multiple sheets


    You could use something like


    Code
    Dim oneSheet as Variant


For each oneSheet in Array(Sheet1, Sheet2, Sheet4)
    With oneSheet
        Rem your Macro
    End With
Next oneSheet

    Re: Replicate column formats on multiple sheets


    Hi Junho,
    Am I correct in thinking that will replicate the formatting in 'Sheet 4' and apply it to sheet 1, 2 & 3?

    And Hi MikeRickson,
    Putting the 'Rem' in front of my macro makes the string green, which as I understand means it's not a part of the calculation (like a comment).
    Is this correct?

    Thankyou all for your help!

    Re: Replicate column formats on multiple sheets


    Thanks Mike, I'm getting very close! But it doesn't seem to copy all the formatting, it leaves out the merged cells, leaving them all split up on each sheet.
    Any ideas?

    Re: Replicate column formats on multiple sheets


    Hi again,
    I'm going insane trying to get this work!
    I'll try to explain what I'm trying to do again...

    I have one worksheet for each month of the year (names of which can change depending on the project start date).
    I've managed to get it so the unrequired columns are hidden automatically in the first worksheet, but I need each
    worksheet to have the same columns hidden/showing. The worksheet I need to copy is 'Sheet7', and it needs copying
    to 'Sheet9' through 'Sheet19'.

    If you would like me to upload the s/sheet please let me know, and I really appreciate your help!

    Re: Replicate column formats on multiple sheets


    oh, hello! To apply your working code to other sheets in your workbook, you could colour code the sheet tabs you want to apply the code to and then point your macro to only those tabs.


    To do this, first apply one colour to all the tabs and only those ones you want the macro to change. You'll then need to find the colour code for these tabs you just coloured. Open up the immediate pane whilst in VBA (and whilst you're in one of the tabs), by pressing CTRL+G. In the pane, type the following and press Enter.


    Code
    ? activesheet.tab.colorindex

    Now use this code


    Re: Replicate column formats on multiple sheets


    Converteds - Please let me know where you live and I'll send you a box of chocolates or something!!! Thankyou so much!!!

    Thankyou everyone for all your help!! This forum is a lifesaver!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login