Macro Problems

[cvach]

I recently made a macro to insert certain values on a spreadsheet into
another worksheet in the same workbook. My code is this.

Sub copy_1_Values_PasteSpecial()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Application.ScreenUpdating = False
Lr = LastRow(Sheets("Invoices")) + 1
Set sourceRange = Sheets("PO Form").Range("A44:G44")
Set destrange = Sheets("Invoices").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

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


This formula worked great for the PO Form worksheet. I then wanted to
add an almost identical worksheet to the workbook and have it do the
same thing. I named it PO Form2 but I was unsuccessful in implementing
it. This was the code that I used.

Sub copy_1_Values_PasteSpecial()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Application.ScreenUpdating = False
Lr = LastRow(Sheets("Invoices")) + 1
Set sourceRange = Sheets("PO Form2").Range("A44:G44")
Set destrange = Sheets("Invoices").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

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

An error came back saying Ambiguous name detected :
copy_1_values_PasteSpecial

[curlydave]

I think the problem could be, from what I see anyway, is that, your
macro's have the same name

[Tim Williams]

You've defined two procedures with the same name. Change one of the names
and it should be fine, or make your original procedure more flexible. Since
the only variation is in the range being copied you can just pass that as a
parameter:


sub Tester1()
copy_1_Values_PasteSpecial Sheets("PO Form").Range("A44:G44")
end sub

sub Tester2()
copy_1_Values_PasteSpecial Sheets("PO Form2").Range("A44:G44")
end sub

Sub copy_1_Values_PasteSpecial(rngCopy as Range)

Dim destrange As Range
Dim Lr As Long
Application.ScreenUpdating = False
Lr = LastRow(Sheets("Invoices")) + 1
Set destrange = Sheets("Invoices").Range("A" & Lr)
rngCopy.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


--
Tim Williams
Palo Alto, CA


"cvach" <[email protected]> wrote in message
news:[email protected]...
> I recently made a macro to insert certain values on a spreadsheet into
> another worksheet in the same workbook. My code is this.
>
> Sub copy_1_Values_PasteSpecial()
> Dim sourceRange As Range
> Dim destrange As Range
> Dim Lr As Long
> Application.ScreenUpdating = False
> Lr = LastRow(Sheets("Invoices")) + 1
> Set sourceRange = Sheets("PO Form").Range("A44:G44")
> Set destrange = Sheets("Invoices").Range("A" & Lr)
> sourceRange.Copy
> destrange.PasteSpecial xlPasteValues, , False, False
> Application.CutCopyMode = False
> Application.ScreenUpdating = True
> End Sub
>
> 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
>
>
> This formula worked great for the PO Form worksheet. I then wanted to
> add an almost identical worksheet to the workbook and have it do the
> same thing. I named it PO Form2 but I was unsuccessful in implementing
> it. This was the code that I used.
>
> Sub copy_1_Values_PasteSpecial()
> Dim sourceRange As Range
> Dim destrange As Range
> Dim Lr As Long
> Application.ScreenUpdating = False
> Lr = LastRow(Sheets("Invoices")) + 1
> Set sourceRange = Sheets("PO Form2").Range("A44:G44")
> Set destrange = Sheets("Invoices").Range("A" & Lr)
> sourceRange.Copy
> destrange.PasteSpecial xlPasteValues, , False, False
> Application.CutCopyMode = False
> Application.ScreenUpdating = True
> End Sub
>
> 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
>
> An error came back saying Ambiguous name detected :
> copy_1_values_PasteSpecial
>