Copy to last row macro

[Thomp]

Can someone look at this macro that I copied from a website and tell
me why the code is not working for me. My source data work sheets is
named "Input KPI" and my destination worksheet is named "KPI Dash2". I
get a sub or function not defined error when I try to run and it seems
to highlight the last row code. I have limited knowledge of VBA so
maybe I am doing something wrong.

I need to copy only non-blank cells in a certain range and have them
copy to another worksheets first empty row in a certain range
Not sure if this code will do that.


Here is the code I was trying to use.

thanks,
Bill

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("Input KPI").Range("A1:K1")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("KPI Dash2")
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

 

[Don Guillett]

why not
sub copyit()
with sheets("KPI Dash2")
lr=.cells(rows.count,"a").end(xlup).row+1
.range(cells(lr,"a"),cells(lr,"k").value= _
Sheets("Input KPI").Range("A1:K1").value
end with
end sub

 

[Thomp]

why not
sub copyit()
with sheets("KPI Dash2")
lr=.cells(rows.count,"a").end(xlup).row+1
    .range(cells(lr,"a"),cells(lr,"k").value= _
Sheets("Input KPI").Range("A1:K1").value
end with
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software














- Show quoted text -

Don, I got a syntax error. I think I am making some kind of error when
I copy it. The .range and Sheets line is showing up in red

Sub copyit()
With Sheets("KPI Dash2")
Lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
.range(cells(lr,"a"),cells(lr,"k").value= _
Sheets("Input KPI").Range("A1:K1").Value
End With
End Sub

 

[Don Guillett]

The .range line ended with a line continuation which is a space and an
underscore instead of making it ONE line.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

why not
sub copyit()
with sheets("KPI Dash2")
lr=.cells(rows.count,"a").end(xlup).row+1
.range(cells(lr,"a"),cells(lr,"k").value= _
Sheets("Input KPI").Range("A1:K1").value
end with
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software














- Show quoted text -

Don, I got a syntax error. I think I am making some kind of error when
I copy it. The .range and Sheets line is showing up in red

Sub copyit()
With Sheets("KPI Dash2")
Lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
.range(cells(lr,"a"),cells(lr,"k").value= _
Sheets("Input KPI").Range("A1:K1").Value
End With
End Sub

 

[Don Guillett]

I was missing a ) in the macro I sent.
Since you are only getting values anyway and you want the formatting, use:

Sub copyit3() 'or since it's only value just copy
With Sheets(" KPI Dash2 ")
Lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
Sheets("Input KPI").Range("A1:K1").Copy .Cells(Lr, "a")
End With
End Sub