+ Reply to Thread
Results 1 to 7 of 7

Loop to write sheets name in column

  1. #1
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Loop to write sheets name in column

    Hello. I have a workbook with 180 Sheest. I need to copy sheet name and paste to column Name.
    In the Column Year write 2013.Finally I need to all sheets as show below in in Sheet Farms.
    Doing this one by one is time consuming and with error risk. I think a loop can do this,
    nevertheless, I donīt Know how to do it. Your help is really appreciated.
    Column size can be different in each Sheet

    Sheet name Famr1
    Cod Name Year Desc Total1 Total2 Prov Cnt
    1234 Apples 200 xxx yyy zzz
    3412 Bananas 400 xxx yyy zzz
    2358 Oranges 500 xxx yyy zzz
    8956 Pines 800 xxx yyy zzz
    ...
    ...

    Sheet name Famr2
    Cod Name Year Desc Total1 Total2 Prov Cnt
    2345 Apples 200 xxx yyy zzz
    4126 Bananas 400 xxx yyy zzz
    ...
    ...

    Sheet name Famr3
    Cod Name Year Desc Total1 Total2 Prov Cnt
    2345 Apples 200 xxx yyy zz
    4126 Bananas 400 xxx yyy zz
    5698 Peanuts 650 xxx yyy zz
    8756 Grapes 350 xxx ppp zz
    5698 Olives 280 xxx ppp
    5879 Pear 150 xxx rrr
    ....
    ....

    Sheet Farms
    Cod Name Year Desc Total1 Total2 Prov Cnt
    1234 Farm1 2013 Apples 200 xxx yyy zzz
    3412 Farm1 2013 Bananas 400 xxx yyy zzz
    2358 Farm1 2013 Oranges 500 xxx yyy zzz
    8956 Farm1 2013 Pines 800 xxx yyy zzz
    2345 Farm2 2013 Apples 200 xxx yyy zzz
    4126 Farm2 2013 Bananas 400 xxx yyy zzz
    2345 Farm3 2013 Apples 200 xxx yyy zz
    4126 Farm3 2013 Bananas 400 xxx yyy zz
    5698 Farm3 2013 Peanuts 650 xxx yyy zz
    8756 Farm3 2013 Grapes 350 xxx ppp zz
    5698 Farm3 2013 Olives 280 xxx ppp
    5879 Farm3 2013 Pear 150 xxx rrr
    ....

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Loop to write sheets name in column

    You are correct. A loop would do this. If you supply a sample workbook with a few sheets with some sample data it will be easier to write the code rather than have to build a spreadsheet and hope we have it correct and then build the code. Time is important to all of us so make it easier and upload a sample spreadsheet.

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Loop to write sheets name in column

    Many Thanks.
    Unfortunally my comp crashed yesterday and an tech is fixing it.
    I include a sample xlsx file as example of what I need. In the Workbook I, include only 3 sheests with data.
    In Farms Sheet I show what I need.

    Many thanks
    Attached Files Attached Files

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Loop to write sheets name in column

    Try this code to see if this works as per your requirement.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Loop to write sheets name in column

    Hello. The code works 100%
    Thank you very much indeed

  6. #6
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Loop to write sheets name in column

    This is Solved

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Loop to write sheets name in column

    Glad to help you. Thanks for the feedback.
    Since you are new to forum so just to tell you that, if that takes care of your question, please mark your thread as solved.
    Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How write sheets array to sort multiple worksheet in the column
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 04-03-2014, 04:54 AM
  2. For Each Loop to sort column in multiple sheets
    By kbryan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-21-2014, 09:26 AM
  3. Macro to Loop Through Alternate Column in Different Excel Sheets
    By sanjeevpandey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2013, 10:52 AM
  4. Loop through sheets copy column move to end of data
    By apunc1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-30-2013, 11:49 AM
  5. Loop Column, write to another
    By kerl in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-20-2007, 06:39 PM

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