spittingfire
New Member
- Joined
- Aug 27, 2014
- Messages
- 16
This is the code at it stands.
Public Function svlPageOveralls(lobRng As Range)
'##############################################################################
'Paste in the actual data from the DATA sheet
'##############################################################################
Dim lobData As Range
Dim lobNam As Range
Dim psteCel As Range
Dim intvl As Range
Dim colLetter As Integer
Set lobNam = lobRng
Worksheets("SUMMARY").Select
Set intvl = Workbooks(ThisWorkbook.Name).Worksheets("SUMMARY").Range("A2")
If mainCtrlFrm.run24HrCkbx.Value = True Then
colLetter = "9"
GoTo continue
End If
Select Case intvl
Case "Interval: 00:00 to 8:30 hrs"
colLetter = 1
Case "Interval: 00:00 to 10:30 hrs"
colLetter = 2
Case "Interval: 00:00 to 12:30 hrs"
colLetter = 3
Case "Interval: 00:00 to 14:30 hrs"
colLetter = 4
Case "Interval: 00:00 to 16:30 hrs"
colLetter = 5
Case "Interval: 00:00 to 18:30 hrs"
colLetter = 6
Case "Interval: 00:00 to 20:30 hrs"
colLetter = 7
Case "Interval: 00:00 to 22:30 hrs"
colLetter = 8
Case "Interval: 00:00 to 24:00 hrs"
colLetter = 9
'Case "Executive Summary"
'colLetter = 5
End Select
continue:
Do While lobNam <> ""
Worksheets("SUMMARY").Select
Cells.Select
Cells.Find(What:=lobNam, After:=ActiveCell, LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=True).Select
ActiveCell.Offset(1, 0).Select
Set psteCel = ActiveCell
Worksheets("DATA").Select
If lobNam = "NTSD CABLE" Or lobNam = "NTSD WIRELESS" Then GoTo cont
Cells.Select
Cells.Find(What:=lobNam & " TOTAL", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=True).Select
Set lobData = ActiveCell
psteCel.Offset(0, colLetter).Formula = "=IF('DATA'!" & lobData.Offset(0, 6).Address & "=""-"",""-"",'DATA'!" & lobData.Offset(0, 6).Address & "/100)"
psteCel.Offset(1, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 8).Address
psteCel.Offset(2, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 5).Address
psteCel.Offset(4, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 2).Address
psteCel.Offset(5, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 3).Address
psteCel.Offset(8, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 7).Address
cont:
Set lobNam = lobNam.Offset(1, 0)
Loop
Worksheets("SUMMARY").Select
Range("A1").Select
End Function
This issue is that the below lines paste back formulas instead of values...
psteCel.Offset(0, colLetter).Formula = "=IF('DATA'!" & lobData.Offset(0, 6).Address & "=""-"",""-"",'DATA'!" & lobData.Offset(0, 6).Address & "/100)"
psteCel.Offset(1, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 8).Address
psteCel.Offset(2, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 5).Address
psteCel.Offset(4, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 2).Address
psteCel.Offset(5, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 3).Address
psteCel.Offset(8, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 7).Address
to try to get around that I got some help and was given this code to try to resolve the issue
Range(Cells(psteCel.Row, colLetter), Cells(psteCel.Row + 10, colLetter)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
However that did not work and what I want to try now is to modify the code to copy the entire column and paste it back as a value instead of looping through rows.
Can someone out there help me rewrite the below code to use columns instead of rows?
Range(Cells(psteCel.Row, colLetter), Cells(psteCel.Row + 10, colLetter)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Thanks in advance for your help.
Public Function svlPageOveralls(lobRng As Range)
'##############################################################################
'Paste in the actual data from the DATA sheet
'##############################################################################
Dim lobData As Range
Dim lobNam As Range
Dim psteCel As Range
Dim intvl As Range
Dim colLetter As Integer
Set lobNam = lobRng
Worksheets("SUMMARY").Select
Set intvl = Workbooks(ThisWorkbook.Name).Worksheets("SUMMARY").Range("A2")
If mainCtrlFrm.run24HrCkbx.Value = True Then
colLetter = "9"
GoTo continue
End If
Select Case intvl
Case "Interval: 00:00 to 8:30 hrs"
colLetter = 1
Case "Interval: 00:00 to 10:30 hrs"
colLetter = 2
Case "Interval: 00:00 to 12:30 hrs"
colLetter = 3
Case "Interval: 00:00 to 14:30 hrs"
colLetter = 4
Case "Interval: 00:00 to 16:30 hrs"
colLetter = 5
Case "Interval: 00:00 to 18:30 hrs"
colLetter = 6
Case "Interval: 00:00 to 20:30 hrs"
colLetter = 7
Case "Interval: 00:00 to 22:30 hrs"
colLetter = 8
Case "Interval: 00:00 to 24:00 hrs"
colLetter = 9
'Case "Executive Summary"
'colLetter = 5
End Select
continue:
Do While lobNam <> ""
Worksheets("SUMMARY").Select
Cells.Select
Cells.Find(What:=lobNam, After:=ActiveCell, LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=True).Select
ActiveCell.Offset(1, 0).Select
Set psteCel = ActiveCell
Worksheets("DATA").Select
If lobNam = "NTSD CABLE" Or lobNam = "NTSD WIRELESS" Then GoTo cont
Cells.Select
Cells.Find(What:=lobNam & " TOTAL", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=True).Select
Set lobData = ActiveCell
psteCel.Offset(0, colLetter).Formula = "=IF('DATA'!" & lobData.Offset(0, 6).Address & "=""-"",""-"",'DATA'!" & lobData.Offset(0, 6).Address & "/100)"
psteCel.Offset(1, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 8).Address
psteCel.Offset(2, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 5).Address
psteCel.Offset(4, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 2).Address
psteCel.Offset(5, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 3).Address
psteCel.Offset(8, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 7).Address
cont:
Set lobNam = lobNam.Offset(1, 0)
Loop
Worksheets("SUMMARY").Select
Range("A1").Select
End Function
This issue is that the below lines paste back formulas instead of values...
psteCel.Offset(0, colLetter).Formula = "=IF('DATA'!" & lobData.Offset(0, 6).Address & "=""-"",""-"",'DATA'!" & lobData.Offset(0, 6).Address & "/100)"
psteCel.Offset(1, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 8).Address
psteCel.Offset(2, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 5).Address
psteCel.Offset(4, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 2).Address
psteCel.Offset(5, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 3).Address
psteCel.Offset(8, colLetter).Formula = "='DATA'!" & lobData.Offset(0, 7).Address
to try to get around that I got some help and was given this code to try to resolve the issue
Range(Cells(psteCel.Row, colLetter), Cells(psteCel.Row + 10, colLetter)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
However that did not work and what I want to try now is to modify the code to copy the entire column and paste it back as a value instead of looping through rows.
Can someone out there help me rewrite the below code to use columns instead of rows?
Range(Cells(psteCel.Row, colLetter), Cells(psteCel.Row + 10, colLetter)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Thanks in advance for your help.