+ Reply to Thread
Results 1 to 16 of 16

Copy individual data values in one column to a single cell location on multiple sheets.

  1. #1
    Registered User
    Join Date
    01-24-2014
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    15

    Copy individual data values in one column to a single cell location on multiple sheets.

    I am trying to come up with the most efficient way to copy data to multiple sheets within the same Excel workbook. The original data exists within one column on a summary sheet (could have up to 500 individual entries). I want to copy each individual entry to a unique sheet (that already exists), but in the exact same cell location within each sheet. I would only want to copy the original data value and not any formatting. Is there an efficient way to do this?

    In my example spreadsheet, the original data is on the SUMMARY sheet. Sheets A through J would be the target sheets, with cell B2 as the target location for each of those sheets. My example shows the result of a manual copy paste value process, but I am hoping to automate that.

    Thanks in advance for any help.
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    How do you wish to marry Sheet Name to the row you are pulling from? Is there a pattern?

    A combination of cell("filename") and Get.Workbook could possibly used to identify the # sheet in a series and match the value from your range.

    Click Ctrl+F3 to define a name. I called mine Sheets and set the value to =Get.Workbook(1)

    Now, in B2 of each tab put:

    =INDEX(Summary!$B$2:$B$11,MATCH(MID(CELL("filename"),FIND("[",CELL("filename")),99),Sheets,0)-1)


    Cell("filename") grabs your filepath, file, & tab and we use Mid to pull out just the Filename and Tab.

    Sheets the defined range pulls an array of all tab names in your workbook

    Match finds where the current tab falls in that list

    Index pulls the value of whatever we matched, -1 to account for skipping your summary tab


    Working from the inside then out in B2 of worksheet A:

    =Match("[Example%201(1).xlsx]A","[Example%201(1).xlsx]Summary","[Example%201(1).xlsx]A","[Example%201(1).xlsx]B","[Example%201(1).xlsx]C","[Example%201(1).xlsx]D","[Example%201(1).xlsx]E","[Example%201(1).xlsx]F","[Example%201(1).xlsx]G","[Example%201(1).xlsx]H","[Example%201(1).xlsx]I","[Example%201(1).xlsx]J",0) = 2, the 2nd match

    =INDEX(Summary!$B$2:$B$11,2-1) the first value in B2:B11 = 11
    Last edited by daffodil11; 01-24-2014 at 04:01 PM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    01-24-2014
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    "How do you wish to marry Sheet Name to the row you are pulling from?"
    I DON'T. THE SHEET IS AUTO NAMED WHEN CELL B2 IS POPULATED, USING THE FOLLOWING:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("g1")) Is Nothing Then
    Me.Name = Target.Value
    End If
    End Sub

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    Okay. But it has a name to begin with?

    It doesn't seem to matter either way.

  5. #5
    Registered User
    Join Date
    01-24-2014
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    I'm just trying to transpose the original list of data into separate copy + paste value executions within cell B2 of each of the separate sheets. So, there is no pattern. I guess you could call the original data list an array of sorts, where each item gets copied into a separate sheet.

    There really isn't any ability to match a cross reference, I just need to copy and paste sequential values in the list. So, on the first copy and paste (into sheet A, Cell B2), I would copy and paste the first value of 11. For the second copy and paste (into sheet B, Cell B2), I would copy and paste 23. And, so on.

    Thanks for the input though.

  6. #6
    Registered User
    Join Date
    01-24-2014
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    Quote Originally Posted by daffodil11 View Post
    Okay. But it has a name to begin with?

    It doesn't seem to matter either way.
    True. I guess you would have to see the original. I put together a very simpified example of the actual spreadsheet. The actual is "bit" more complicated. Suffice it to say there is a lot going on with creating and renaming new sheets temporarily until the final name is assigned when the B2 value is finally copied and pasted.

    Not sure I can explain it any better. I do sincerely appreciate the help though.

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    Perhaps another VB approach, to pull the range and copy each to a new worksheet and rely on the code to rename the worksheet after the copy to perform the renaming?

  8. #8
    Registered User
    Join Date
    01-24-2014
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    YES, kinda what I was thinking. Basically, I could copy the original list of values to a sacraficial column that would allow me to copy and paste the first value, then delete that first row. The data would move up and the second value would now be in the same position where the original first value was. Copy + paste and delete. Then, the third value would be in the first position....and so on, until a blank cell was encountered.

    The only problem is that I'm not too swift with VB. I can hack away and will finallly get what I need, but it's usually the result of the sun shining on a dumb dogs behind kinda routine....
    I don't use the syntax enough to be good at it.

  9. #9
    Registered User
    Join Date
    01-24-2014
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    I just need some sun....!!!!!

  10. #10
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    I threw this together, it would work for you if modify the initial range from B2:B5 to whatever fits your purpose, and also the paste to range lower which is set to B2.

    This replaces the need for renaming worksheets as well. It takes each value in the B2:B5, creates a new sheet, inserts a value, renames sheet after that value.


    Please Login or Register  to view this content.
    I attached a sample. Just run the macro, see if it works for you.

    copy and stuff.xlsm
    Last edited by daffodil11; 01-24-2014 at 05:09 PM.

  11. #11
    Registered User
    Join Date
    01-24-2014
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    YOU ARE AWESOME! I think that's the best (and quickest) sunburn I've ever gotten!!!

  12. #12
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    Thanks, glad I could help out. I'm pretty terrible at VB myself but I've franken-coded something similar a few months ago.

    It was just a matter of tearing it apart to the point that I could build something functional out of it. (I had a guy who needed to consolidate 50 files, with 100 tabs each, into a single tab of continuous data. I pulled code from all over the web that ultimately copied all tabs from all files from a single folder into one giant workbook, and then appended the first tab with each other tab's data and then deleted it. I had no idea what I was doing. Still kind of don't..)

    Good times.

    If you want to make it even sexier, you can start the macro with:

    Application.ScreenUpdating = False

    before the For Each and then after the End For

    Application.ScreenUpdating = True


    to hide the tab changing and stuff. Personally I like watching my computer take control and doing it, but with a lot of data sometimes its best to hide it.
    Last edited by daffodil11; 01-24-2014 at 05:19 PM.

  13. #13
    Registered User
    Join Date
    01-24-2014
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    Quote Originally Posted by daffodil11 View Post
    I threw this together, it would work for you if modify the initial range from B2:B5 to whatever fits your purpose, and also the paste to range lower which is set to B2.

    This replaces the need for renaming worksheets as well. It takes each value in the B2:B5, creates a new sheet, inserts a value, renames sheet after that value.


    Please Login or Register  to view this content.
    I attached a sample. Just run the macro, see if it works for you.

    Attachment 292442
    Any way to modify this such that it will not automatically create a new sheet? I need to do that manually at this point. So it would just have to copy and paste the value into an existing sheet.

    I tried playing with the syntax, but the only sub option I get for Sheet.Select is Replace...again, not the greatest at syntax here.

    Thanks again. Cool stuff.

  14. #14
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    Are the tabs already named after the contents of the cells that will land there?

    If yes, could probably target specific tabs by content of cell. If not, hmmm...

    Maybe copy contents of each cell to Sheet(cell's row -1), which may work because even when you name a sheet VB also keeps a record of the sheet as the nth sheet created.

  15. #15
    Registered User
    Join Date
    01-24-2014
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    Are the tabs already named after the contents of the cells that will land there?
    ANS: NO. WHAT HAPPENS IS I CREATE MULTIPLE SHEETS(TABS) AT ONCE. IN DOING SO, I ESSENTIALLY CREATE AN INITIAL INDIVIDUAL SHEET FOR ASSET I HAVE TO INSPECT. SO MY INITIAL TAB LIST MIGHT LOOK SOMETHING LIKE: LEVEE (1), LEVEE(2), LEVEE(3), BUILDING(1), BUILDING(2), ROAD(1), ROAD(2), ROAD(3)....

    THEN, AFTER I IMPORT THE UNIQUE ASSET LIST FOR A GIVEN STATION ONTO MY SUMMARY SHEET, I COPY + PASTE EACH ASSET NUMBER INTO A CELL WITHIN EACH OF THE INDIVIDIAL SHEETS. YEAH, IT'S SOMEWHAT CLUNKY RIGHT NOW, BUT I'M TAKING IMPROVEMENTS ONE AT A TIME BECAUSE I ONLY HAVE SO MUCH TIME TO MAKE THEM. THIS IS ALL ON MY OWN INITIATIVE BECAUSE i KIND OF TOLD MY BOSS GOING TO THE FIELD WITH PENCIL AND PAPER WAS SOMEWHAT STUPID IN THE 21ST CENTURY (OPEN MOUTH INSERT FOOT). THAT SAID, SHE CAN'T ARGUE WITH MY INCREASED PRODUCTIVITY EITHER.

    ANYHOW, CREATING ALL OF THE INITIAL INDIVIDUAL SHEETS GOES PRETTY QUICK. THE TIME CONSUMING PART IS GETTING THE INDIVIDUAL ASSET NUMBER INTO EACH OF THEM, SO EVERTHING CAN REFERENCE OFF OF THAT. AND, TO BE HONEST, I DIDN'T START THIS AUTOMATION PROCESS COMPLETELY FROM SCRATCH. I SORTA INHERITED A FILE FROM ANOTHER GUY AND TOOK IT TO THE NEXT LEVEL. SO, I HAVE A FEW GAPS IN HOW A FEW PARTS OF IT ARE EXACTLY WORKING. BETTER TO BUILD IT YOURSELF IF YOU WANT TO UNDERSTAND SOMETHING COMPLETELY. BUT, HERE I AM....COMMITTED, OR MAYBE JUST STUBBORN, OR BOTH.

    THANKS AGAIN.

  16. #16
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Copy individual data values in one column to a single cell location on multiple sheets

    The defined name we wrote earlier might help with this. In another column off to the left in Z2 for example I put:

    =MID(INDEX(Sheets,ROW(A2)),FIND("]",INDEX(Sheets,ROW(A2)))+1,99)

    and then copied down

    This at least names every sheet, skipping the first. Now we just need to target each B into Z

+ 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. Replies: 1
    Last Post: 08-21-2012, 03:14 PM
  2. Replies: 2
    Last Post: 07-21-2012, 08:36 AM
  3. Copy data from multiple sheets to single sheet
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-22-2010, 05:33 PM
  4. Copy data from multiple sheets to single sheet
    By Ananta in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-30-2009, 05:38 AM
  5. Copy data from single sheet to multiple sheets
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-23-2009, 11:31 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