Running Macro to copy Rows of Formulas

See if link helps

http://www.rondebruin.nl/copy1.htm

VBA Noob

Originally Posted by VBA Noob

See if link helps

http://www.rondebruin.nl/copy1.htm

VBA Noob

I am sure the code should work but because I am new to macro's, I might be missing something here.

Here's what I want to do, I want to copy the information from Row number 1 located in the sheet called IWLog and paste the information in an empty row as Values in the sheet called Log.

Thank you.

Here's my code:

Sub Copy_1_Value_PasteSpecial()
    Dim SourceRange As Range, DestRange As Range
    Dim DestSheet As Worksheet, Lr As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'fill in the Source Sheet and range
    Set SourceRange = Sheets("IWLog").Range("A1:Z1")

    'Fill in the destination sheet and call the LastRow
    'function to find the last row
    Set DestSheet = Sheets("Log")
    Lr = LastRow(DestSheet)

    'With the information from the LastRow function we can
    'create a destination cell
    Set DestRange = DestSheet.Range("A" & Lr + 1)

    'Copy the source range and use PasteSpecial to paste in
    'the destination cell
    SourceRange.Copy
    DestRange.PasteSpecial xlPasteValues, , False, False
    Application.CutCopyMode = False

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

You need to read this line again

Important: The macro examples use one function or two functions that you can find in the last section of this page.

As you forgot to add

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

or Instead of a function you can also check one row or column to find the last cell with a value.

Replace this line:

Lr = LastRow(DestSheet)

With:

Lr = DestSheet.Cells(Rows.Count, "A").End(xlUp).Row

VBA Noob

Your welcome

VBA Noob