Paste Values

[rlmarriott1]

Hi All

Hoping for a little bit of help please - I'm not great with VBA so have developed a code based on adapting others. I'm trying to transpose information from one sheet into another. I've managed to do this so far, but I need to update the code to paste values. Can anyone help?

Thanks in advance

Sub Run_Cost_Report2()

Dim ws As Worksheet
Dim wsLast As Long
Dim wsStart As Range
Dim LR As Long, i As Long, j As Long
Dim mStart As Double
 
mStart = Timer
 
'Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
Set ws = Sheets("Cost Report")
ws.Range("C12:I65536").ClearContents
ws.Range("K12:M65536").ClearContents
Set wsStart = ws.Range("C" & ws.Rows.Count).End(xlUp)
 
With Sheets("Change")
     
    LR = .Range("C" & .Rows.Count).End(xlUp).Row
    j = 1
     
    For i = 12 To LR
         
        .Range("C" & i & ":E" & i).Copy wsStart.Offset(j, 0).Resize(5)
        .Range("G" & i & ":G" & i).Copy wsStart.Offset(j, 3).Resize(5)
        .Range("I" & i & ":J" & i).Copy wsStart.Offset(j, 4).Resize(5)
        .Range("L" & i & ":L" & i).Copy wsStart.Offset(j, 6).Resize(5)
         
        .Range("N" & i & ":O" & i).Copy wsStart.Offset(j, 8)
        .Range("P" & i & ":Q" & i).Copy wsStart.Offset(j + 1, 8)
        .Range("R" & i & ":S" & i).Copy wsStart.Offset(j + 2, 8)
        .Range("T" & i & ":U" & i).Copy wsStart.Offset(j + 3, 8)
        .Range("V" & i & ":W" & i).Copy wsStart.Offset(j + 4, 8)
        .Range("AA" & i & ":AA" & i).Copy wsStart.Offset(j, 10)
        .Range("AA" & i & ":AA" & i).Copy wsStart.Offset(j + 1, 10)
        .Range("AA" & i & ":AA" & i).Copy wsStart.Offset(j + 2, 10)
        .Range("AA" & i & ":AA" & i).Copy wsStart.Offset(j + 3, 10)
        .Range("AA" & i & ":AA" & i).Copy wsStart.Offset(j + 4, 10)
        j = j + 5
    Next i
End With
Call FormatCostReport
Application.Calculation = xlCalculationAutomatic
'Application.ScreenUpdating = True
 
Debug.Print Timer - mStart
End Sub

 

[Special-K99]

This won't solve your problem but you could remove

j=1

and

j=j+5

and replace all other references to j within that loop to

(i-1)*5+12