[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!

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?

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).

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

This is an example of what the output table would look like.
Attachment 124073

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.



Attachment 124077 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).

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

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.

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

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.

This part:

Quote:

---

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.

Quote:

---

Originally Posted by vbfbryce

I'll bow out and let someone else jump in.

---

:afrog: Me Jump. :)

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

Attachment 124151

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

---

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. Attachment 124177