Hi - I have a spread sheet where you will enter a "Y" or "N" in row 45. I then want to be able to hit a button that will create a new sheet named "option 1" where I27:O24 will be copied to A1 and I38:J115 to A12 on the new sheet. Then any column with a "Y" in row 45 will have that column from row 38 - 115 will be copied to the next available column in row 12 on the new sheet. The sheet naming doesn't work, I want it to name sheets Option 1,2,3...as new sheets are added. It seems to loop through the range, but only pastes the final column.
VBA Code:
Set ws1 = Sheet9
Dim ws As Worksheet
iStart = 1
With ws1
Sheets.Add after:=Sheets(Sheets.Count)
Set ws = ActiveSheet
On Error Resume Next
ws.Name = "Option " & iStart.Value
On Error GoTo 0
.Range("I27:X38").COPY
With ws.Range("A1")
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End With
'.Range("I38:J115").SpecialCells(xlCellTypeVisible).COPY
.Range("I38:J137").COPY
With ws.Range("A12")
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Range("A13:B17").Delete Shift:=xlUp
End With
End With
Dim C As Long, LR As Long, LC As Long, SC As Long
With ws1
LR = .Cells(.Rows.Count, 9).End(xlUp).Row
LC = .Cells(44, .Columns.Count).End(xlToLeft).Column
For C = 1 To LC
For i = 3 To LC
If .Cells(45, C) = "Y" Then
.Range(.Cells(44, C), .Cells(137, C)).COPY
With ws.Cells(13, i)
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End With
End If
Next i
Next C
End With