Results 1 to 12 of 12

Thread: [Excel] Cycle through named list and copy and paste resulting values to a new table

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    6

    [Excel] Cycle through named list and copy and paste resulting values to a new table

    Hi there,

    So I have a workbook with a data validated list which forms a toggle, changing calculations throughout the workbook.

    I need to create a code that cycles through the values of the list, whilst copying the results of each change to a table in a new sheet.

    The list is a named range, "BC", and the cell with the data validated list is named "BCToggle". All of the ranges which I wish to copy are named "Copy....."
    ("CopyBC" and "CopyDate" for example)

    All the copy ranges are (1,27) in size, as they are values over a 27 month period. The output table needs to have a column per each copy range, with the newly copied values appended below following each step through the list, thus creating a flat table.

    My current code is:

    Sub BCCycle()
    Dim counter As Long

    counter = 1
    For Each cell In Range("BC").Cells(counter, 1)
    Range("BCToggle") = cell.Value
    Call DataRip
    counter = counter + 1
    Next

    End Sub


    But I am unsure of how to cycle through each copy range, pasting each into a new column, and also of how to ensure the new values are appended to the table rather than overwriting what is already there.

    Many thanks!

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,570

    Re: [Excel] Cycle through named list and copy and paste resulting values to a new tab

    Couple of questions:

    1) Fixed number of ranges to be copied, or can it vary?
    2) Is the output table on a different sheet, but in the same workbook?
    3) What is the trigger to copy? When one of the copy ranges changes size?
    4) Each time #3 happens, will you need to validate that the new record is not already in the output table, or will you always add?
    5) Are there headings in the output table that tell you where to paste?

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    6

    Re: [Excel] Cycle through named list and copy and paste resulting values to a new tab

    1) Fixed number of ranges, 10.
    2) Output table is indeed within the same workbook, and doesn't necessarily have to be an actual table, I would be happy to adjust that manually myself afterwards.

    I will try to simplify what changing the value of the toggle does, and what I am trying to achieve.
    Basically the toggle is filtering the workbook calculations by billing centre. So upon moving to each different value in the list, the results are filtered by a different billing centre.

    Ideally I need to automate the copy and pasting of the resulting values for each billing centre, appending each new set below the last.. I have tried to create and example output table below (obviously the rest of the table would also be filled with values).

    The date range is the same for all different values from the list, and the headers would also apply to all. (The named copy ranges are ' 'Copy'& the column header'


    BC Date Volume Shortfall Average Median Weighted StdDev 25 75
    LHR Dec-12 20
    LHR Jan-13 17
    LHR Feb-13 32
    ... ...
    LHR Nov-14 21
    LHR Dec-14 19
    LHR Jan-15 18
    NYC Dec-12 45
    NYC Jan-13 47

    So for each different entry in the toggle, all copy ranges must be copied and pasted under their respective headings, appended to whatever existing data there is (all columns will be the same length).

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,570

    Re: [Excel] Cycle through named list and copy and paste resulting values to a new tab

    I'm still not exactly following. Can you zip and attach a sample, showing the before and after picture?

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    6

    Re: [Excel] Cycle through named list and copy and paste resulting values to a new tab

    This is an example of what the output table would look like.
    Name:  macrooutput.jpg
Views: 71
Size:  69.6 KB

    Thanks for your patience, this is my first post so was unsure of how clear (or not!) I was making myself.

    Attached are two screenshots of what the copy ranges are, and then ideally what the output table would look like. The toggle is in a different sheet to the copy ranges.

    I hope that makes more sense now.

    The first billing centre in the list is named All, as it consists of everything, unfiltered. The others consist of 54 3-letter codes.



    Name:  copy range.jpg
Views: 72
Size:  26.9 KB other picture uploaded so small, obviously this represents just the first few columns of the copy ranges, the date range is Dec-12 to Jan-15
    This picture is an example of the copy ranges (the actual cell references are different in the real workbook, but they are named ranges).
    Last edited by jdavey; Feb 25th, 2015 at 04:57 AM. Reason: clarification for others

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,570

    Re: [Excel] Cycle through named list and copy and paste resulting values to a new tab

    I can see the larger one, but not the smaller. It would be better if you could zip and attach an actual sample workbook.

  7. #7

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    6

    Re: [Excel] Cycle through named list and copy and paste resulting values to a new tab

    I don't know how to do that or really see why its necessary.. of the images uploaded above, the most easily readable is a zoomed in version of the one that uploaded so small, only showing the first few columns, but as I said the date range continues up until Jan-15, the values populating the rest of the table are unimportant.

    The biggest picture attached above demonstrates the output table, which effectively is a pivot of the copy ranges. As the macro cycles through the different billing centres, thus changing the values in the copy ranges, I need the resulting values to be pivoted and appended to the bottom of the output table. So if you can imagine, from the zoomed copy ranges shown, those values constitute rows 2-7 of the output table.
    Last edited by jdavey; Feb 24th, 2015 at 09:09 AM.

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,570

    Re: [Excel] Cycle through named list and copy and paste resulting values to a new tab

    Looks like you could simply copy and Paste > Transpose to make it horizontal, then move the date row down to the bottom.

  9. #9

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    6

    Re: [Excel] Cycle through named list and copy and paste resulting values to a new tab

    In theory, yes (although the other way round, the flat table is what the output table is required to look like).
    But there are 55 different options in the data validated list that forms the toggle, each one resulting in 27 lines of data for the flat table. Due to the size of the workbook (amount of data and number of calculations), adjusting the toggle takes the workbook around 45 seconds to calculate.
    This is why it would be better to use a macro, so I can leave it running overnight/in the background.

    And also this will eventually be a tool used time and time again in the future, so automating the processes required to produce the required outputs is obviously preferable.

  10. #10
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,570

    Re: [Excel] Cycle through named list and copy and paste resulting values to a new tab

    This part:

    But there are 55 different options in the data validated list that forms the toggle, each one resulting in 27 lines of data for the flat table. Due to the size of the workbook (amount of data and number of calculations), adjusting the toggle takes the workbook around 45 seconds to calculate.
    is not very clear, which is why I suggest you upload an actual sample workbook. To do so, in Windows Explorer, you can right click the workbook and Send To >>> Compressed (zipped) folder, and you will then be able to upload.

    If you do that, I'll attempt to help; if you still don't think it's necessary, I'll bow out and let someone else jump in.

  11. #11
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: [Excel] Cycle through named list and copy and paste resulting values to a new tab

    Quote Originally Posted by vbfbryce View Post
    I'll bow out and let someone else jump in.
    Me Jump.

    I was bored, so here is an example of my interpretation of what you want to do.

    TransposeData.zip
    Code:
    Public Sub Test()
       ' some test values
       Dim bcValues As New Collection
       bcValues.Add "All"
       bcValues.Add "AKL"
       bcValues.Add "PPP"
       
       Application.Calculation = Excel.XlCalculation.xlCalculationManual
       
       Dim wsDest As Excel.Worksheet
       Set wsDest = Sheet3
       ' set up destination worksheet
       Call SetupOutputWorksheet(wsDest)
          
       Dim bc As Variant
       For Each bc In bcValues
          ' set the bc value
          ThisWorkbook.Names("setBC").RefersToRange.Value = bc
          ' update calcs
          Application.CalculateFullRebuild
          ' copy results
          Call CopyData(wsDest)
       Next
       
       Application.Calculation = Excel.XlCalculation.xlCalculationSemiautomatic
    End Sub
    
    Private Sub SetupOutputWorksheet(ws As Excel.Worksheet)
       ws.UsedRange.Delete
       With ws
          .Cells(1, 1).Value = "BC"
          .Cells(1, 2).Value = "Date"
          .Cells(1, 3).Value = "Volume"
          .Cells(1, 4).Value = "Shortfall"
          .Cells(1, 5).Value = "Average"
          .Cells(1, 6).Value = "Median"
          .Cells(1, 7).Value = "Weighted"
          .Cells(1, 8).Value = "StdDev"
          .Cells(1, 9).Value = "'25"
          .Cells(1, 9).Errors(Excel.XlErrorChecks.xlNumberAsText).Ignore = True
          .Cells(1, 10).Value = "'75"
          .Cells(1, 10).Errors(Excel.XlErrorChecks.xlNumberAsText).Ignore = True
       End With
       With ws.UsedRange
          .HorizontalAlignment = xlCenter
          With .Interior
             .Pattern = xlSolid
             .PatternColorIndex = xlAutomatic
             .Color = 15773696
             .PatternTintAndShade = 0
          End With
       End With
    End Sub
    
    Private Sub CopyData(wsDest As Excel.Worksheet)
    
       Dim rngSource As Excel.Range
       Set rngSource = ThisWorkbook.Names("SourceRange").RefersToRange
       Dim sourceValues As Variant
       sourceValues = rngSource.Value
       Dim data As Variant
       data = WorksheetFunction.Transpose(sourceValues)
    
       Dim rngDest As Excel.Range
       'set rngdest to the first cell in the first row beneath the UsedRange
       Set rngDest = wsDest.UsedRange.Offset(wsDest.UsedRange.Rows.Count, 0).Cells(1, 1)
    
       'resize rngDest to match the size of data
       Set rngDest = rngDest.Resize(rngSource.Columns.Count, rngSource.Rows.Count)
       rngDest.Value = data
       
       ' the Date values are the last row in sourceValues
       ' therefore these values will be in the last column after transposing
       ' these values need to be placed in the 2nd column
       ' 1st copy values into an array
       Dim cut As Variant
       cut = rngDest.Columns(rngDest.Columns.Count).Value
       ' delete these values from the worksheet
       rngDest.Columns(rngDest.Columns.Count).Delete (Excel.XlDeleteShiftDirection.xlShiftToLeft)
       ' make room for the Date values
       rngDest.Columns(2).Insert (Excel.XlInsertShiftDirection.xlShiftToRight)
       ' set the values
       rngDest.Columns(2).Value = cut
    End Sub

  12. #12

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    6

    Re: [Excel] Cycle through named list and copy and paste resulting values to a new tab

    I understand that a sample would be helpful, it was more the question of confidentiality of data that was holding me back, rather than deliberately trying to be unhelpful.

    Attached is a data removed sample, to give an idea of how the toggle would change the data in the copy ranges, and how it would ideally be output into a table.

    The main idea is that when you change the toggle, all the values in all the copy ranges will change (except the dates - the data covers the same time period for all). As the macro cycles through each value in the list, it should let the workbook calculate, then copy the copy ranges, and the paste them, appending to the end of the output table, into the correct columns (which do not need to be in any particular order).

    Please get back to me if you need any more explanation or if this very simple sample doesn't demonstrate the required level of detail.

    Thanks. Sample Workbook.zip

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width