Coloring worksheets and arranging column width in tabs as per active sheet !

[amlan009]

Dear Expert's,


My respect to anyone willing to take this up,i guess this should be easy for most of the pros out here .

Purpose -To color inner tabs and arrange formatting (Column width ) and individual row width as active sheet (most often first tab where macro button is there )

Please see Input file attached,

You will see 3 tabs ,sheet 1 ,sheet 2 ,sheet 2 ,

In sheet 1 ,you will see that the color of the sheet is light blue and each of the columns has a width of approx 50 ,

But in sheet 2 and sheet 3 ,you will note that the colors as well as the column widths change for each of the columns A,B,C,D,E,

I want that all tabs in this case (sheet 2 and sheet 3)have same colour and same width as sheet 1(active tab ) ,

The real case has more than 200 tabs ,so i want to do it for all tabs(all of them 200 ) based on active sheet (in this case sheet 1 ),in other cases it may vary sometimes 4,7,200,etc .So macro to itself first count number of sheets and then apply same color an formatting as parent sheet to all inner tabs

Also here i have 5 used columns ,but in real cases ,the sheet 1 may have 10 used columns sometimes 12 etc ,so i want the macro to self figure out used column and column width and row height for individual rows from active sheet (in this case sheet 1 wherein i will put a button (SEE BUTTON FORMAT ) and attach macro to that button ) and then apply same formatting to all sheets(Tabs in the same book) and all columns width as sheet 1 (active sheet ),

Will really appreciate if someone can take this up ,


If someone takes it up ,i will post output result file as well to show whether it works realtime or no!


Humble Regards ,


Amlan

[WasWodge]

Try

Please Login or Register  to view this content.

in a standard module.
1stI Edit: will see if I can do anything with the column widths if this part is correct.
2nd Edit: actually just make the 10 columns the correct width on the activesheet and run the code

[amlan009]

Wow ,that works like a dream !


I attach both input and output files ,i would love to know how you made it happen ,since i wanna learn it ,

is it i this 2 lines of code that you are unwrapping magic ,

Please Login or Register  to view this content.

what does := do ,can you just explain that bit ,i see this in many codes ,but don't understan what that means ,

Anyways sorry for the pain ,

The code works great ,so i will mark the thread as solved and give you few credits again !

You have been very sweet to me ,i am very greatful ,

In a next thread i will raise a query to delete unused rows and column (and formatting contained therein ),

I hope that you can solve that but won't bother you any further now ,

Hug's,

Amlan Dutta

[amlan009]

Hi Dear ,

I am sorry to ask but looking at your sheet results ,i wish to go one step further ,

In short ,i want now to have one worksheets formatting applied to a seperate worksheet (contained in a seperate workbook ) and all tabs of that workbook formatted in the same way as your code worked for the parent workbook in this case ,



i attach 3 files ->formatted workbook(parent ),to format workbook(target ) , and input file containing both files path

Basically i have 2 workbooks ->parent and ->target ,

In parent workbook ,in the first tab (worksheet) ,i will do formatting ,

My aim is to have same formatting in target workbook for all its tabs ( worksheet )

Basically in attached Input sheet ,you will see that i give path for both formatted spreadsheet of parent workbook and path for "to format "spreadsheet in all tabs of target workbook ,

I also attach formatted parent workbook ,

i also attach target workbook ,
I attach input sheet in which i mention the parent and target workbooks path ,

Once i press the button in INPUT hseet to which i assign your macro ,i wish that the formatting from formatted sheet gets applied to all tabs of the target worksheet ,

Do you think this needs a new thread ,i feel it to be connected and so am posting here ,

Would be humbled if you can post a solution !Please take a look at the attachments ,

Regards ,

Amlan

[WasWodge]

Try this, the output sheet with the formatting required needs to be open then the macro will ask you to select a file to be formatted.

Please Login or Register  to view this content.

[amlan009]

Dear ,

Partly succesful ,asked me to open the file path ,i selected the file


Then Run Time error 1004 happened

"Paste special method of range class failed "

in this part of the code

Please Login or Register  to view this content.

Important here is to note that in the already formatted worksheet ,i just want the macro to capture the formatting from first tab rather than studying all the tabs ,

Is that possible ?

Thank you so so much !

Regards ,


Amlan Dutta

[amlan009]

Strangely i redid it ,this time it didn't threw up a error ,but it landed up being the same
sheet ,

here, find before and after run files attached as also the file from which i wanted formatting to take place(titled Macrowidth and color inputsheet output) ,the file names are self explanatory ,


If you can save the files and try it out yourself ,i think you will be able to replicate the same thing i am facing ,basically before and after run is resulting in same sheet ,

I am sorry for paining you ,

Regards ,


Amlan Dutta

[amlan009]

Dear ,

I have figured out a bit ,

Then Run Time error 1004 happened

"Paste special method of range class failed " happens if the formatted file is also open ,it needs to be closed,can you handle the macro to not bothr whether the formatted file is open or close ,

I realise that the formatting is getting reversed ,lol.In stead of having the target file having the formatting of formatted file ,the formatted file takes the formatting of target file ,

I am studying your code and seeing whether i can reverse this ,but can you handle the working of the code when the excel file is either open or closed?
Regards ,


Amlan Dutta

[amlan009]

I just hope that i havent confused you ,this macro works but in a reverse way ,

Basically,i have the "To format "file -target file ...i open this file .. i want the module in this file (target file) which needs to be formatted ,then everything you have done is fine ,it can ask me to select the formatted file and then apply formatting of formatted file to the target file (i also wish that once the target file is formatted ,it opens up -if you can handle that !


Right now ,what happens is the formatted file is opened ,the module is needed to be pasted here and from here we select the target file to be formatted and then it works on the target file (formatting happens in target file ,but it is in closed state ,if opened ,results can be seen )......the paste range error happens ,if target file is open during macro execution ,


Can you handle the issues ,if possible ?i will deeply appreciate,i tried but i am too novice to reverse the workbook flow and then make the target file open once the macro finishes executing ,

Thank you so much for everything ,

Regards ,


Amlan Dutta

[WasWodge]

Which file did you put the macro in? I thought you needed it to be for multiple files so it needs to be in the output file (Macrowidth....).
The target file must be closed.

[amlan009]

I have put it in target file .I see your solution works brilliantly if i put macro in Formatted output file .

Actually ,i wanted it the other way round ,i wanted whichever file i wanted to format(target File ) to contain the macro and then give it the path of the file from which i want formatting to be done ,

Does it require a lot of code revision?Is there a chance to open the target workbook once the formatting in it gets applied ,

Regards ,

Amlan Dutta

[WasWodge]

What is the name of the file the source formatting is coming from going to be? need to know because it needs to be hard coded.

Is there a chance to open the target workbook once the formatting in it gets applied

It already was open you just need to remove the line

Please Login or Register  to view this content.

and probably if you want it left open then you will want to also remove line

Please Login or Register  to view this content.

[amlan009]

Brilliant ! Actually i have started to like the macro you have created !

Hard coding is not sounding nice ,because it wont be just one file that i wanna format .

Every time its gonna be a new file which i would like to be formatted ,so having your style of code which could ask for the formatted file path and then make the target file formatted that fashion would have been awesome !

But the fact that i can achieve that using your code as it is ,is also nice !
I guess reversing the code is not possible ,but seriously your code works like magic the other way round ,i simply have to train my mind to think this way )))


I will try the Ext book save trick and let know !(I feel silly for missing that part )

Regards ,

Amlan

[WasWodge]

Done now and so you might as well have it as well.

Please Login or Register  to view this content.

[WasWodge]

which could ask for the formatted file path and then make the target file formatted that fashion would have been awesome !

Can be done but you would have to have another to get the target file again or hard code it. The first would be a lot of manual input and so you might as well do it manually and the second is inflexible.

[amlan009]

You truly are excel God ! Unfortunately i dont have access to files right now since my other laptop is undergoing some backup operations ,but i can't thank you enough for this !!!!Your earlier code also worked like magic !

I am sure this one shall as well ,

I just need to know one thing ,i see that in the code you have hard coded the formatted file to be "Macrowidth and ColorInput sheet OUTPUT .xlsm" ,this means whenever i open any file to be formatted,it wil take the formatting of this file ,

What i was sinfully wishing was to select any file i wish to have liked you designed your earlier macro so that whenver i have a file for which i want formatting to be applied ,i can simply select the formattd file rathr than have it hard coded (If it's possible),

That would be awesome and i think you would be feeling to kill me now i am so sorry ,but let me be honest ,i am now very able to use your macro and it solves my purpose !

Like i said ,i can't thank you enough and i can't giv you more stars here because of rules (which you really deserve )

Regards ,

Amlan Dutta

[WasWodge]

But see my comments in previous post

Please Login or Register  to view this content.

[amlan009]

Thanks a tonne ,i somehow feel dumb that i can't use the last part of the code ,but please forget it ,you have helped beyond imagination and i can't thank you enough !

I would buy you a beer if i can ,

Regards ,

Amlan Dutta

[WasWodge]

Thanks for your response, the problem you are having is because when you open a file it does not automatically become the activesheet, I get intermittent results myself. It is one reason why it is easier to have the code in the source formatting workbook. If I come up with an adequate solution I will post it but it won't be anytime soon.

[amlan009]

I really like you soooooooo soooooooo much!It's okay even if that doesn't happen ,i will also keep trying with my limited knowledge ,if i come up with something i will also equally post it !

Thanks a tonne ,WasWodge ,i must admit noone has helped me as much as you did in my limited tenure at various excel forums and your solutions have also been amazing and classic .

Forget the last code which was anyways a extension of the thread and not the thread itself ,your solution for the original thread worked like magic and i am deeply greatful for that!

Take good care ,my friend and thanks again ,

Humble regards ,

Amlan Dutta

[amlan009]

Dear ,

I hit upon a obstacle in the first part of the following code ,i will explain

Please Login or Register  to view this content.

What happens is that if my first tab has just 30 rows and 14 columns ,then for each of inner tabs it does formatting for only 30 rows and 14 columns ,

What i want is that it does the same formatting for the used range of rows and columns ,is that possible ?

Sorry for the pain,

Regards ,


Amlan Dutta

[WasWodge]

Try

Please Login or Register  to view this content.

Please note that Column A has to be the longest in each sheet.
Now back to watching the football

[amlan009]

Dude ,

i might be missing something here but this one is throwing odd results ...i attach the input file and the output file with the macro for you to see the results yourself ,


See the INPUT FOOTBALL SHEET for Input file (without run ) and OUTPUT FOOTBALL REV (for output with run )

My input tab (active sheet ) had 14 rows and columns until E
If you see the Output tabs ,you will note that in the 2 sheets ,the formatting applies only until 14 rows after which the row width and height becomes narrow-with this particular code i note strangely that only column A is effected though in strange manner -Making all boundaries dissapear in active sheet and then retaining color only in each of the tabs without effecting any other column at all for the remaining tabs .....

Also i note that in the revised code the column width is not pasted as per input tab (active sheet),strangely the input sheet loses its boundaries and becomes ***** ,hehe))actually its fun to see it but gosh i wish that you can make it work and wear the same clothes it was wearing like before and the inner tabs to have the same jersey as well

The output sheet using this code somehow gets no formatting ,only column A color is retained ,


What i am wishing is that if i have 5 rows and 6 columns in active sheet in a workbook and say if i have inner tabs with X rows and Y columns where X and Y may be different for each tab ,then the formatting(color,column width ,row width ,boundaries ,wrap text etc terms included in formatting )applies to all the used ranges of X rows and and y columns of inner tabs ,


I see the brilliance when you discover the last row and last column for each tab (worksheet )and then apply formatting to the used range but somehow when i am trying it practically ,it doesn't work !Please see files attached along with button to which i have assigned your macro !


The 'INPUT FOOTBALL' is the one in which i have not run it and shows before the run ,

The 'OUTPUT FOOTBALL REV 'is the one which is after the run ,please see the inner tabs ,

All this is all-rite ,which team are you supporting ?
And thanks a tonne dude for posting your solutions ,even if they may minor glitches ,i learm so much from them ,its really really appreciated !


Regards ,


Amlan Dutta

[WasWodge]

I think you will find if you don't have the blank row at the top of Sheet2 and Sheet3 it will run fine.
I will leave you to adapt the code if needed.

[amlan009]

Hi,

Pure Brilliance on your part !(And silly me for not having observed it !)

I did successfully modify the code as follows

Please Login or Register  to view this content.

You will see that i made the range start from A2 because i noted that in each of my sheet the first row was blank and also i do ActiveSheet.Cells(2, 2).Copy because the sheet which i wanted to copy was also had first row blank ,so i copied the 2nd cell starting from row 2 .......

Things worked like a dream ,

(Prior to this ,let me be honest ,i tried rows ("1":"1").delete,but that didnt work and gave me pastespecial method failed )

Phase -2 (Deleting blank rows and columns for unused range

Then i decided that i will delete the blank rows and columns for unused ranges ,

I noted that you have already computed last row in the code
So i stored last used row +1 also in another variable X ,

Also ,I computed total number of rows first using rows.count property-> in a variable y ,

Then for deleting unused range (blank rows) ,exactly i needed to delete this range from->used rows +1 to total rows .....

Range("x:y").Delete

Same things for blank columns ,i.e

i saw that you had already computed last used column in code and stored it in variable LC

So i stored last used column + 1 in variable a (For going to one column more than the used column ...in this used column was 5 so this resulted in first unused column starting from 6 )

Also i basically figured out the total column count in a variable b using columns.count property

Then for deleting unused range (blank columns) ,exactly i needed to delete this range from used column +1 to total columns left .....

Range("a:b").Delete .....

I ,only in this part got a paste special error and the sheets get cut when i try this part ..i am playing around and think i will get it shortly ,once i do i will definitedly post
the solution ,

I really thank you ,using your code i can now understand VB more better than i earlier did ,also it made my practical work of having same formatting across 745 sheets a breeze !

Using some minor modification ,i think i can delete the unused rows and columns shortly ,


Thank you so so much
Regards ,


Amlan Dutta

[WasWodge]

Using my phone at the moment and so I can't give you proper help but your syntax for deleting rows is incorrect. Record yourself using the macro recorder and look at the code. You will see what I mean

[amlan009]

Dear ,

Used the macro recorder ,i see it uses a different syntax as below ,do you think this looks right ,i basically want to delete unudes rows and columns and formatting contained therein ...if you think the below code will exhibit the same when run succesfully ,then i will put it to testing

Please Login or Register  to view this content.

Regards ,


Amlan Dutta

[amlan009]

This throws up a error ,while deleting unused rows and unused columns ,i want sheet formatting to apply in all used rows and columns (boundaries ,wrap text ,row width ,column width ),

As of now when i run it i get a error in line Rows("x:y").Select

I got this line from the macro recorder itself ,may be we cant store row numbers in a variable ,is that so ?

I get Application defined or object defined error,on step through i realise its the line

Rows("x:y").Select,

Regards ,


Amlan Dutta

[WasWodge]

Still on my phone but the one thing i didn't like / understand when I looked at your original data was why were you were filling entire columns to start with? Surely it is better to deal with that rather than deleting the ranges afterwards ?

[amlan009]

Hiya ,sorry for the delayed response .....

You see that i am trying to create a hypothetical situation wherein each tab has seperate used range of rows and columns ....it mimics a real time situation wherein the output of a software gives me similar different used ranges of rows and columns in each tab,

what i fail to understand is why should my code throw up a error because i used the macro recorder to do it like suggested !

Regards ,

Amlan Dutta

[WasWodge]

Not sure I am quite clear what you want (I think you just just want to set a usedrange on each sheet) but try this. Before you run it clear any fill colour in the sheets as I am using the fill colour to show the usedrange. Most of the code is just to reset the usedrange (there are shorter methods but I still have more faith in the longer version).

Please Login or Register  to view this content.

[amlan009]

Hiya ,was having problem logging in ...am in now ,the code looks brilliant ,i mean the logic (still clueless as to why mine doesn't work ,when mine is based on macro recorder )guess excel is not happy with me .......

Will test your's and let you know whether it works shortly ,

Thanks a tonne for your efforts
Humble Regards ,

Amlan Dutta

[amlan009]

Everything becomes green in the tabs and the existing sheet !

Is it my case or even you have the same problems i face with this code ,

I mean ,if you can please see the attached files ,i did remove the fill color like you suggested in the trailing thread

Look at the file attached ,


Personally i feel that if used range can be detected using numbers rather than color ,it will be better

Is a cell with color and having no number considered to be empty or filled ,i would want it to be considered empty but i understand that in real definition the cell contains the color

I guess this is a tricky bit !

How is the football thing going besides?

i am really sorry for giving you so much pain in this thread ,

Regards ,


Amlan Dutta

[WasWodge]

You can do what you like with the used range, the green colour was just to show you the range, it is detected whether you colour it or not. The used range is the cells with content and not defined by fill colour/bolders or other formatting. It can (depending how you define it) contain nullstring results from formulas or not.

Have a look at Sheet2 on the output sheet and scroll down to rows 3280 and 3281 and you will see where the used range ends. Then colour fill the cells manually from row 3281 down red (say 20 rows) and run the macro again and then look at the result again at cells 3280 and 3281 downwards.

Finally in your output workbook clear all the colour again from the sheets and run the code below, the UsedRange is being utilized with no colour involved.

Please Login or Register  to view this content.

[amlan009]

Dear ,

I did color manually in sheet 2 20 odd entries "see attached sheet color before run " and then run your macro -see sheet "color after run "

i relaise what you mean ,i do observe that the used range correctly deletes my color in the 20 odd rows for color if there is no content exactly what i want ,

However ,you can note that the column width gets shrinked even in parent tab as well as other tabs ....the first solution you posted in the thread would keep column width and formatting same in all tabs

Also in tab 2 ,i find the first column boundary getting self deleted - i am trying to figure whether i am doing something wrong ?

In tab 3 the same problem ?

Here i also like to mention , that for the range is getting identified correctly but the other features like wrap ,boundaries etc are not getting done though i understand that can be done and i will atemmpt it once myself too !Main problem was figuring the range ,so kewl but column width and row width is still aquestion for all used range !


Using 2nd code which you gave :

See the sheet -INPUT AND OUTPUT

I guess it's the same thing without color and looks better ,

But same problem ,column width in tabs not structures as per parent tab ,

Row width also a concern ,

I am sorry for this ,buddy ,i still have the first code which works like magic for column width in all tabs if used range is similar in all tabs ........

Humble regards ,


Amlan Dutta

[WasWodge]

The first macro was to help with the original question, the later macros were just to demonstrate using the usedrange (they weren't intended to resize the columns).
If you read them and step through them using F8 (take/comment out any lines with Application.ScreenUpdating in them) so you understand what they are doing then you should be able to combine them yourself quite easily.

Post back in a new thread (obviously with a link back to this thread) if you encounter any major problems.

I will leave you to it for now

[amlan009]

Okay i will make a new thread )))