I am trying to perform a few operations on a dynamic number of ranges that are also dynamically sized. Each time the program is run, the number of noncontiguous ranges may change, as well as the size of the ranges. I identify my ranges by finding the delimiter in Column A, which is the underscore. Right now, VBA is recognizing the ranges seen below as two different areas, but selects them both at the same time when called to find the strings containing the delimiter, which is correct. I am transposing my range areas from one page to the next. In the first column of each range, Column A, there is a name, and the rest of the row contains various numbers. In each range, the string names vary, but they all correspond to the string names in the other range(s). For instance, all the first string names in each range end with _1. The second string name in the range (or the cell in the row beneath the first string), ends in _2, for each range. Below is a picture:
I know I can remove the blank row in between each range, but I don’t think it is the best solution for my desired output. I would like to take the first row of the first area (in this example, A5:E5), transpose it, and paste it on a new sheet from (in this example) B5:B9. I would then like to go to the next area and do the same thing (so, copy A9:E9), transpose it, and paste it on the same sheet in the next column (so, C5:C9). Then I’d like to go back to my first range, and take the next row (A6:E6), tranpose it, and paste to D5:D9, then go to the next area and take (A10:E10), and so on until all the ranges have been pasted to the new page. So, ideally, I’d like to alternate between ranges and paste each row into a column, continuously through each range. This is my desired output:
I am having a hard time getting the data the way I want it, and know that I may not have the option to alternate between ranges, so I am up to hearing any ideas. Again, the number of ranges (or areas) can change with each run, and so can the size of the ranges. For this example, I had two ranges with 3 string names, but next run, I could have three ranges with 4 string names, so I can’t hard code anything. The string names will not always be the same (input_x, output_x) so I can’t hard code these either, but I can search for the delimiter, the underscore in the string name, as it will always be formatted into the string name.
My current output DOES copy and paste by each cell in the area, but this is the best I have gotten with what I have tried:
Here is my code (comments are some things I have been trying):
Dim myRange as Range Dim c as Range, a as Range Dim t As Long, m as Long Dim delimiterItem as Variant Dim newSheetName as String newSheetName = ActiveSheet.Name delimiterItem = "_" t = 2 With Sheets.Add(After:=Sheets(Sheets.Count)) .Name = "Final" If myRange Is Nothing Then MsgBox ("Value not present in this workbook.") Else For Each a In myRange.Areas For Each c In a.Rows Worksheets(newSheetName).Activate c.EntireRow.Copy 'For m = Cells(myRange.Rows.Count, 1).End(xlUp).Row To 2 Step -1 'If Split(InStrRev(myRange.Cells(m, 1).Text, delimiterItem))(0) = Split(InStrRev(myRange.Cells(m - 1, 1).Text, delimiterItem))(0) Then Worksheets("Final").Activate Cells(8, t).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True t = t 1 'Sheets("Final").Range("B8").Offset(0, (t - 2) * 2).PasteSpecial xlPasteValues Next c Next a End If End With
Any help or pointers in the right direction would be much appreciated. Thank you!
Sorry for the length of this question.