+ Reply to Thread
Results 1 to 18 of 18

format issue

  1. #1
    farfromapro
    Guest

    format issue

    Background can be found here

    http://www.excelforum.com/showthread.php?t=582713

    That macro worked beautifully!! Now I have a workbook with 500+ worksheets....my first worksheet is a unique page; and then the following one (worksheet name "AA") has column names, frozen window panes, date format, and borders. I want all of the other worksheets to have the same "format" as the others....I don't want to ctrl + a, ctrl + c, pick the next workbook, ctrl + v 500+ times!!! anyone know of a faster way?

    also......this is probably easy but I can't figure it out....everytime I enter 001 it makes it 1....I don't want that....how can I make excell not remove my important zeros?

    Thanks for listening, thinking, replying!!! Ever since I've found this website everyone has been very very very extremely helpful....THANKS!!!! Hopefully i'll be good enough at this stuff to return the favors.....If anyone needs pro/e work i'm there!!! thanks!

  2. #2
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Do you only need a loop ...

    Please Login or Register  to view this content.
    HTH
    Carim

  3. #3
    farfromapro
    Guest

    ?

    i'm not sure how to impliment your reply.....do I need to save the page with the proper format as a template; then call out the template name somewhere in this code?

    Sub Test()
    Workbooks.Add
    ActiveSheet.Name = "Cover"
    For N = 65 To 90
    For M = 65 To 90
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = Chr$(N) & Chr$(M)
    Sheets("Cover").Hyperlinks.Add Anchor:=Sheets("Cover").Cells(((N - 65) * 26) + M - 64, 1), TextToDisplay:=Chr$(N) & Chr$(M), Address:="", SubAddress:=Chr$(N) & Chr$(M) & "!A1"
    Next M
    Next N
    End Sub

    I'm a little lost....I am surprized I was able to apply that code in the first place can you give me your reply in the "for dummies" version? Thanks!

  4. #4
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,


    I have got two questions for you :

    1. How is created the format of worksheet "AA" ... by code or not ...

    2. Currently to copy format have you tried the combination of
    - Selecting all cells
    - Format painter icon

    HTH
    Carim

  5. #5
    farfromapro
    Guest
    I just manually typed in the column headers, bordered the cells I need bordered.....no code.....so how do I continue?

  6. #6
    farfromapro
    Guest

    status

    i'm in the microsoft script editor and see the code for the sheet I want....I am attempting to paSte all of the code into multiple books but cannot....i'm getting close.....need a little help though!! I think the script editor might be the best way to do this no??? thanks for your replys !!!! I used the cycle paste command but only achieved the pasting on to one book...no all 546......any idea?
    Last edited by farfromapro; 12-12-2006 at 10:41 AM.

  7. #7
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    I understand you have moved on ...
    Can you post this new formatting code you want to apply to all you sheets ?

    HTH
    Carim

  8. #8
    farfromapro
    Guest

    here

    it's too long....way to many characters says the prompt....how can you what I have?
    it's a bunch of html for book"AA"....All books except the first "cover" book should look like what "AA"....just not sure how to make it happen fast! The link above shows the macro I used; which was generously given to me I'm wondering if I can work this format into that; or make a macro that will paste this format on sheets "AA" - "ZZ", or somehow copy and past this format from script into all the other sheets....I know I don't want to do it manually 500 + times!

    thanks for following this!!! I appreciate it!!! Any ideas??

    here is the begining...the rest is just borders going down 500 or so cells....the only import stuff is the column headers, the link back to the cover page, and the borders

    <html xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns="http://www.w3.org/TR/REC-html40">

    <head>
    <meta http-equiv=Content-Type content="text/html; charset=windows-1252">
    <meta name=ProgId content=Excel.Sheet>
    <meta name=Generator content="Microsoft Excel 11">
    <link id=Main-File rel=Main-File href="../509.htm">
    <link rel=File-List href=filelist.xml>
    <link rel=Edit-Time-Data href=editdata.mso>
    <link rel=Stylesheet href=stylesheet.css>
    <style>
    <!--table
    {mso-displayed-decimal-separator:"\.";
    mso-displayed-thousand-separator:"\,";}
    @page
    {margin:1.0in .75in 1.0in .75in;
    mso-header-margin:.5in;
    mso-footer-margin:.5in;}
    -->
    </style>
    <![if !supportTabStrip]><script language="JavaScript">
    <!--
    function fnUpdateTabs()
    {
    if (parent.window.g_iIEVer>=4) {
    if (parent.document.readyState=="complete"
    && parent.frames['frTabs'].document.readyState=="complete")
    parent.fnSetActiveSheet(1);
    else
    window.setTimeout("fnUpdateTabs();",150);
    }
    }

    if (window.name!="frSheet")
    window.location.replace("../509.htm");
    else
    fnUpdateTabs();
    //-->
    </script>
    <![endif]><!--[if gte mso 9]><xml>
    <x:WorksheetOptions>
    <x:Print>
    <x:ValidPrinterInfo/>
    <x:Scale>56</x:Scale>
    <x:HorizontalResolution>600</x:HorizontalResolution>
    <x:VerticalResolution>600</x:VerticalResolution>
    </x:Print>
    <x:CodeName>Sheet4</x:CodeName>
    <x:PageBreakZoom>60</x:PageBreakZoom>
    <x:Selected/>
    <x:Panes>
    <x:Pane>
    <x:Number>3</x:Number>
    <x:ActiveRow>21</x:ActiveRow>
    <x:ActiveCol>3</x:ActiveCol>
    </x:Pane>
    </x:Panes>
    <x:ProtectContents>False</x:ProtectContents>
    <x:ProtectObjects>False</x:ProtectObjects>
    <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
    </xml><![endif]-->
    </head>

    <body link=blue vlink=purple class=xl26>

    <table x:str border=0 cellpadding=0 cellspacing=0 width=1345 style='border-collapse:
    collapse;table-layout:fixed;width:1009pt'>
    <col class=xl26 width=216 style='mso-width-source:userset;mso-width-alt:7899;
    width:162pt'>
    <col class=xl26 width=173 style='mso-width-source:userset;mso-width-alt:6326;
    width:130pt'>
    <col class=xl26 width=113 style='mso-width-source:userset;mso-width-alt:4132;
    width:85pt'>
    <col class=xl26 width=199 style='mso-width-source:userset;mso-width-alt:7277;
    width:149pt'>
    <col class=xl26 width=288 style='mso-width-source:userset;mso-width-alt:10532;
    width:216pt'>
    <col class=xl26 width=356 style='mso-width-source:userset;mso-width-alt:13019;
    width:267pt'>
    <tr height=17 style='height:12.75pt'>
    <td colspan=6 height=17 class=xl30 width=1345 style='height:12.75pt;
    width:1009pt'><a name="RANGE!A1" href="sheet001.htm#RANGE!A1">BACK TO COVER</a></td>
    </tr>
    <tr class=xl25 height=18 style='mso-height-source:userset;height:13.5pt'>
    <td rowspan=2 height=66 class=xl31 width=216 style='height:49.5pt;border-top:
    none;width:162pt'>BASIC CONFIGURATION NUMBER (FORMAT:XXX)</td>
    <td rowspan=2 class=xl31 width=173 style='border-top:none;width:130pt'>DATE<span
    style='mso-spacerun:yes'>******************************* </span>(FORMAT
    DD/MM/YYYY)</td>
    <td rowspan=2 class=xl31 width=113 style='border-top:none;width:85pt'>INITIALS
    (FORMAT: TM)</td>
    <td rowspan=2 class=xl31 width=199 style='border-top:none;width:149pt'>DWO,
    ECN, ECR…ETC.<span style='mso-spacerun:yes'>************* </span>(FORMAT:
    ECN-E1234, ECR-)</td>
    <td rowspan=2 class=xl31 width=288 style='border-top:none;width:216pt'>DESCRIPTION
    1</td>
    <td rowspan=2 class=xl31 width=356 style='border-top:none;width:267pt'>DESCRIPTION
    2</td>

  9. #9
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    To copy a macro ...
    Alt F11 to get to VBE
    Double click on macro
    Copy code
    and paste it in the message ...

    HTH
    Carim

  10. #10
    farfromapro
    Guest

    Hhmm

    but that will only work for the one sheet I put the format in...i need to put the format into 500+ sheets. How would I make a macro to do a mass paste? I'd like to just say copy format from AA to AB thru ZZ.....not sure how to make that work though....or select all workbooks, and paste format to all the selected ones......any more ideas?

    I don't have a macro code for this. I have only made the format...(meaing borders, column headers, a link to the "cover" page...) I need a macro to do the implimenting of this format to multiple worksheets......

    thanks for the shortcut keys!

    how can I make this happen? besideds copy and pasting manually!!

  11. #11
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Hope following code will help you out ...

    Please Login or Register  to view this content.

    HTH
    Carim

  12. #12
    farfromapro
    Guest

    close

    Thank you for that!!!! It did everything except for the words (column "headers")...I tried tweaking with your macro to make it do it but I crashed my computer twice.....That is some pretty macro writing though! why wouldn't that take into account the text? From what I read (not that I can read lines of commands) it seems like it should take everything...hhmmm...if you can help me more i'd appreciate it!1...so so close...all becasue of you! and mrice..... thanks for following this! Hopefullly it will all work out!...thanks again!

  13. #13
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Indeed macro takes all the formats of a given sheet to copy them to next sheet ...

    I do not understand ...
    everything except for the words (column "headers")...
    what are they ? what is special about them ? how was created their format ?

    Carim

  14. #14
    farfromapro
    Guest

    More Info

    The column headers are the labels for the columns...for instance part number, date, description. There was also a cell with a link going back to the cover page.

    What you gave me (and I am very grateful by the way!! thank you!) copied everything except the "headers/column labels". I don't understand how the merged cells copied over but not the information in the cells....how do I make the headers copy over with the format? Thanks!

  15. #15
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Ok... got it now merged cells ... !!! as usual ...
    when there is a problem with XL ... 9 times out of 10 it is because of merged cells ...

    Merged cells are a nuisance ... Avoid them as much as possible ...

    Carim

  16. #16
    farfromapro
    Guest

    no merged

    I removed the merged cells as per your advice. After that I ran that macro but it still returned the same results...so very close. I've been searchign the vb help for things like "copyobjectswithcells" and such...still can't get it!!!

    I changed the value of transpose=false to true (thinking that maybe it will transpose the cell values i.e. the labels) but it crashed my machine! It's almost all done...THANKS TO YOU!!

    what else can I try besideds the unmerging of cells to copy the text along with the format in that macro?

    thanks!!!

  17. #17
    farfromapro
    Guest

    i got it!!!

    I GOT IT!!!!! I changed the past xlformats to xlall and it did it!!!! THANK YOU SO MUCH!!!! THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU

  18. #18
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    I am very happy to see how happy (and relieved..) you are ...

    Thanks for the feedback

    Carim

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1