All all - I need some help rewriting a code to copy and paste values in a columns

[spittingfire]

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.

 

[StephenCrump]

Welcome to MrExcel.

Can you perhaps take a step back and explain what it is that you are trying to do?

It seems to me that you want to apply a patch to already patched code that can almost certainly be simplified.

And I suspect that a couple of simple formulae in Excel will achieve the same result far more directly.

 

[spittingfire]

Welcome to MrExcel.

Can you perhaps take a step back and explain what it is that you are trying to do?

It seems to me that you want to apply a patch to already patched code that can almost certainly be simplified.

And I suspect that a couple of simple formulae in Excel will achieve the same result far more directly.

Thanks for your reply

The workbook is much more complicated than that. It consists of over 24 sheets that is driving all the data into one sheet. The workbook as itself works fine and does what it is supposed to do. I was asked to make an addition to the main page which I did and for all the cells except for the first few rows all the data is being pasted back as values. The top section of the sheet is used as an overall summary and hence there are formulas in most of those cells. Normally that would have been ok except for the information that was added I need it to paste the values back instead of the formulas. I was told that more than likely modifying

Range(Cells(psteCel.Row, colLetter), Cells(psteCel.Row + 10, colLetter)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

to paste the entire rows back as values will solve the issue. The problem is I don't know how change

Range(Cells(psteCel.Row, colLetter), Cells(psteCel.Row + 10, colLetter)).Select

to read columns instead of rows to try to see if that resolves the issue.

 

[StephenCrump]

It seems a roundabout way to do things, but if you want to replace formulae with values, a single line of code will suffice, e.g.

Range("A1:H1").Value = Range("A1:H1").Value

How are you going to determine the range, which I have hard-coded here as A1:H1?

 

[spittingfire]

good question and I don't have an answer

there are 9 columns B to J that is updated every 2 hours. The previous code would loop through the rows pasting the data and pasting back some formulas. What I am trying to accomplish is after it is done pasting I then want to take the column copy it and paste the values.

I really don't know - I am assuming that somehow I can use the "colLetter" as there are the columns that are being used for this purpose, but I don't know how to go about doing that.