I am stuck on the below. I am sure its an easy problem, but I am not seeing it.
This code works well, and is doing exactly what I want it to do, with one exception.
As you can probably tell, this is creating new columns after column E, for each named spreadsheet in the range bounded by cell B1, and cell D1, but ignoring the sheets called "input" and "summary" (which are sheets 1 and 2). It is then copying the formulas in E into those columns (I did this because all the formulas down the column are not the same). The formulas from E pull data from the respective tabs.
It is then creating a new column 2 columns out, putting an equation in row 8 of the new column, which is perfect.
Here is the problem, that bolded "F8"......I need that to be the dynamically created figure in the last column. So for instance, if B1 was "3" and D1 was "5", it would create columns in F-H, and a new column in J with the equation. I need the column in J in this case to be "d8-h8", but if say B1 was "3" and D1 was "4", I would need it to be "d8-g8", which would now be in the created column i.
Any input would be great.
Bonus if there is an easy way to put text in row 6 on the created equation column as part of this.
Sub WSNames()
Dim x As Integer
Dim count As Integer
Sheets("Summary").Range("F6:AZ100").ClearContents
count = 6
For x = Sheets("Summary").Range("B1") To Sheets("Summary").Range("D1")
Select Case Worksheets(x).Name
Case "Input", "Summary"
Case Else
Sheets("Summary").Cells(6, count).Value = Worksheets(x).Name
Sheets("Summary").Range("$E$8:$E$94").Copy (Sheets("Summary").Cells(8, count))
count = count + 1
End Select
Next x
With Worksheets("Summary")
NextCol = .Cells(6, Columns.count).End(xlToLeft).Column + 2
LastRow = .Cells(Rows.count, 1).End(xlUp).Row + 1
.Cells(8, NextCol).Resize(LastRow - 1).Formula = "=$d8-f8"
End With
End Sub
This code works well, and is doing exactly what I want it to do, with one exception.
As you can probably tell, this is creating new columns after column E, for each named spreadsheet in the range bounded by cell B1, and cell D1, but ignoring the sheets called "input" and "summary" (which are sheets 1 and 2). It is then copying the formulas in E into those columns (I did this because all the formulas down the column are not the same). The formulas from E pull data from the respective tabs.
It is then creating a new column 2 columns out, putting an equation in row 8 of the new column, which is perfect.
Here is the problem, that bolded "F8"......I need that to be the dynamically created figure in the last column. So for instance, if B1 was "3" and D1 was "5", it would create columns in F-H, and a new column in J with the equation. I need the column in J in this case to be "d8-h8", but if say B1 was "3" and D1 was "4", I would need it to be "d8-g8", which would now be in the created column i.
Any input would be great.
Bonus if there is an easy way to put text in row 6 on the created equation column as part of this.
Sub WSNames()
Dim x As Integer
Dim count As Integer
Sheets("Summary").Range("F6:AZ100").ClearContents
count = 6
For x = Sheets("Summary").Range("B1") To Sheets("Summary").Range("D1")
Select Case Worksheets(x).Name
Case "Input", "Summary"
Case Else
Sheets("Summary").Cells(6, count).Value = Worksheets(x).Name
Sheets("Summary").Range("$E$8:$E$94").Copy (Sheets("Summary").Cells(8, count))
count = count + 1
End Select
Next x
With Worksheets("Summary")
NextCol = .Cells(6, Columns.count).End(xlToLeft).Column + 2
LastRow = .Cells(Rows.count, 1).End(xlUp).Row + 1
.Cells(8, NextCol).Resize(LastRow - 1).Formula = "=$d8-f8"
End With
End Sub