+ Reply to Thread
Results 1 to 18 of 18

Create new sheets copying All the data of a specific sheet and gining names to all sheets

  1. #1
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Create new sheets copying All the data of a specific sheet and gining names to all sheets

    Hi

    My goal is this.

    I have a master sheet and in column A exist the working days of the month. This sheet using formulae receive data from all the others sheets.
    I have sheet1 and sheet2 completed with DIFFERENT datas.

    Rename sheet1 with the first date of the month that ALWAYS exist in MASTERA2! A2 of this new named sheet must have the date of MASTERA2!, formatting as Date.

    Rename the sheet2 with 2nd date of the month tHAt will be always in MASTER!A3. iN A1 of this sheet must appears the date.

    Create so many sheets as the list in MASTER sheet column A, give them as name the dates and in A1 of eatch this sheet appears the date AND copy in eatch of these sheets ALL the data from sheet2!

    Thank you in advance!
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    Hi Fortis,
    You know you are unable to create sheets names with/\ , so you need to reformat your date in dd-mm-yy before you can create them

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    Correct. Yes i know, but...Ok. No problem. i'll do it. Any idea?

    And AB33: You must have fat fingers.. Fotis. Not Fortis..

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    This code create as many sheets as exist in the list of column A(starting in A2) in the Master sheet and give the names

    Please Login or Register  to view this content.
    Then i record a macro for the Copy & paste in the sheets. Then using same way i can record a macro for the dates in A1

    So 3 different codes-macros. In this case only left the auto rename of the 2 first sheets.

    So i almost have my solution, BUT I AM SURE that must be an "easier" solution.

  5. #5
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    Give this a shot

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    Hi my friend. Thanks for one more time.

    Please Login or Register  to view this content.
    The red part of the code, gives me this error

    Compile error
    Variable no defined
    Any idea?

  7. #7
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    Thats the sheets Code Name. Did you try in on the example workbook you uploaded?

  8. #8
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    So sheet1 must rename by the first working date of the month that ALWAYS will be in A2. Same for Sheet2
    Being that the sheet names will be changing I used the sheets Code Name. I guess you could also use the sheets index, as long as they will always remain in the same postion.

  9. #9
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    Mike,
    In my little experience of VBA world this some times does not work

    Please Login or Register  to view this content.
    But this work all the time

    Please Login or Register  to view this content.

  10. #10
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    With Sheet2 will always work as long as the that sheets code name is Sheet2. Using the Code Name of the sheet in this case I think is better being that sheet1 and sheet2 names will always be changing but the Code names will ramain the same.

  11. #11
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    Mike apologize for the dalay. An unexpected visitor...

    YES. i try to use the code in my example sheet that i uploaded here.

  12. #12
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    I know your using a Greek Version. The workbook you uploaded I see

    Code Name=Sheet1 Tab Name= Master
    Code Name=Sheet2 Tab Name= Sheet1
    Code Name=Sheet3 Tab Name= Sheet2

    Is this not the case for you?

  13. #13
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    Exactly as you say.

    Do you think that maybe a reason could be the greek translate of sheet? What i mean is this.

    In the sheet that i uploaded, i see this.

    Code Name=Φύλλο1 Tab Name= Master
    Code Name=Φύλλο2 Tab Name= Sheet1
    Code Name=Φύλλο3 Tab Name= Sheet2

    "Φύλλο"= Sheet

    Can you pls upload the my workbook with your code to test it again?

  14. #14
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    Try

    Please Login or Register  to view this content.

  15. #15
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    Mike this works but we have some issues...

    1)the named sheet 310113 is sorting after my first sheet. Not at the end!

    2) From sheet 030113 unti 110113 the format in A1 is "mm/dd/yyyy" All the others sheets have the format that i want "dd/mm/yyyy"

  16. #16
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    hey my friend,

    I'll be stepping out from now till Sunday. If you don't receive a solution by then I'll keep an eye on this thread and respond back Sunday.

  17. #17
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    Ok Mike. I'll upload the example that i get and i'll wait..because VBA, as anyone knows, is not my strong card..

    Thank you!

  18. #18
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Create new sheets copying All the data of a specific sheet and gining names to all she

    ..And here it is!
    Attached Files Attached Files

+ 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