VBA: Possibility of Adding a Copy and Paste Value and Format

  • Is it possible to add in a Copy and Paste Value and Format only Function at the end of each loop. For this following Code !!


    -----------------


    Sub MakePretty()
    Dim i As Long, k As Long, ib As Long, s As String, lngCalcMode As Long
    lngCalcMode = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Sheet2.UsedRange.Offset(1, 0).Clear
    Sheet1.Activate
    k = 13
    ib = 13
    i = 13
    s = Cells(i, 1)
    With Sheet2
    .Cells(k, 1) = s
    .Cells(k, 3) = Cells(i, 3)
    .Cells(k, 5) = Cells(i, 5)
    'Calculate Conversion Rate
    .Cells(k, 7).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-4])),RC[-4]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-4],""""),"""")"
    'Calculate AVG Order Size
    .Cells(k, 10).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-5])),RC[-5]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-5],""""),"""")"
    k = k + 1
    .Cells(k, 2) = Cells(i, 2)
    .Cells(k, 4) = Cells(i, 4)
    .Cells(k, 6) = Cells(i, 6)
    .Cells(k, 7).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-4])),RC[-4]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-4],""""),"""")"
    .Cells(k, 8) = Cells(i, 8)
    .Cells(k, 9) = Cells(i, 9)
    .Cells(k, 10).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-5])),RC[-5]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-5],""""),"""")"
    .Cells(k, 11) = Cells(i, 11)
    End With
    i = i + 1
    k = k + 1
    While Not IsEmpty(Cells(i, 1))
    If Cells(i, 1) = s Then
    With Sheet2
    .Cells(k, 2) = Cells(i, 2)
    .Cells(k, 4) = Cells(i, 4)
    .Cells(k, 6) = Cells(i, 6)
    .Cells(k, 7).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-4])),RC[-4]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-4],""""),"""")"
    .Cells(k, 8) = Cells(i, 8)
    .Cells(k, 9) = Cells(i, 9)
    .Cells(k, 10).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-5])),RC[-5]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-5],""""),"""")"
    .Cells(k, 11) = Cells(i, 11)

    End With
    Else
    With Sheet2

    .Cells(ib, 4).FormulaR1C1 = "=SUM(R" & ib + 1 & "C:R" & k - 1 & "C)"
    .Cells(ib, 6).FormulaR1C1 = "=SUM(R" & ib + 1 & "C:R" & k - 1 & "C)"
    .Cells(ib, 8).FormulaR1C1 = "=SUM(R" & ib + 1 & "C:R" & k - 1 & "C)"
    .Cells(ib, 9).FormulaR1C1 = "=SUM(R" & ib + 1 & "C:R" & k - 1 & "C)"
    '.Cells(ib, 10).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-5])),RC[-5]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-5],""""),"""")"
    .Cells(ib, 11).FormulaR1C1 = "=SUM(R" & ib + 1 & "C:R" & k - 1 & "C)"

    .Range("A" & ib & ":K" & ib).Font.Bold = True
    s = Cells(i, 1)
    ib = k

    .Cells(k, 1) = s
    .Cells(k, 3) = Cells(i, 3)
    .Cells(k, 5) = Cells(i, 5)
    'Calculate Conversion Rate
    .Cells(k, 7).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-4])),RC[-4]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-4],""""),"""")"
    'Calculate AVG Order Size
    .Cells(k, 10).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-5])),RC[-5]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-5],""""),"""")"

    k = k + 1

    .Cells(k, 2) = Cells(i, 2)
    .Cells(k, 4) = Cells(i, 4)
    .Cells(k, 6) = Cells(i, 6)
    .Cells(k, 7).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-4])),RC[-4]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-4],""""),"""")"
    .Cells(k, 8) = Cells(i, 8)
    .Cells(k, 9) = Cells(i, 9)
    .Cells(k, 10).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-5])),RC[-5]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-5],""""),"""")"
    .Cells(k, 11) = Cells(i, 11)

    End With
    End If
    i = i + 1
    k = k + 1
    Wend
    With Sheet2
    .Cells(ib, 4).FormulaR1C1 = "=SUM(R" & ib + 1 & "C:R" & k - 1 & "C)"
    .Cells(ib, 6).FormulaR1C1 = "=SUM(R" & ib + 1 & "C:R" & k - 1 & "C)"
    .Cells(ib, 8).FormulaR1C1 = "=SUM(R" & ib + 1 & "C:R" & k - 1 & "C)"
    .Cells(ib, 9).FormulaR1C1 = "=SUM(R" & ib + 1 & "C:R" & k - 1 & "C)"
    '.Cells(ib, 10).FormulaR1C1 = "=IF(AND((ISNUMBER(RC[-5])),RC[-5]<>0), IF(ISNUMBER(RC[-2]),RC[-2]/RC[-5],""""),"""")"
    .Cells(ib, 11).FormulaR1C1 = "=SUM(R" & ib + 1 & "C:R" & k - 1 & "C)"
    .Range("A" & ib & ":K" & ib).Font.Bold = True
    .Activate

    Sheets("Sheet2").Select
    Columns("C:C").Select
    Selection.NumberFormat = "#,##0"
    Columns("D:D").Select
    Selection.NumberFormat = "#,##0"
    Columns("E:E").Select
    Selection.NumberFormat = "#,##0"
    Columns("F:F").Select
    Selection.NumberFormat = "#,##0"
    Columns("G:G").Select
    Selection.NumberFormat = "0.00%"
    Columns("H:H").Select
    Selection.Style = "Currency"
    'Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
    'Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    'Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
    'Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    Columns("I:I").Select
    Selection.Style = "Currency"
    'Selection.NumberFormat = "0.00%"
    Columns("J:J").Select
    Selection.Style = "Currency"
    Columns("K:K").Select
    Selection.Style = "Currency"

    End With
    Application.Calculation = lngCalcMode
    End Sub

  • May have misunderstood but why don't you use the macro recorder to generate the code for the "Copy and Paste Value and Format only function" and add that to your macro

    &lt;a href="http://www.mrexcel.com/relayforlife.shtml" target="new"&gt;&lt;img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"&gt;&lt;/a&gt;

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!