Pasting problems

C

cvach

I have this code thus far. I want all of this information to be on the
same line but as of right now it buts it on two seperate lines. How
can I combine the codes and have them com in on the same line.

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

Sub copy_3_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("H44:H44")
Set destrange = Sheets("Invoices").Range("H" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteFormulas, , 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
 
T

Tom Ogilvy

I assume there is some good reason you are using separate procedures to do
the copy. If you always do copy_1_Values_PasteSpecial first, then

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

Sub copy_3_Values_PasteSpecial()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Application.ScreenUpdating = False
' don't add a 1 here
Lr = LastRow(Sheets("Invoices"))
Set sourceRange = Sheets("PO Form").Range("H44:H44")
Set destrange = Sheets("Invoices").Range("H" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteFormulas, , 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Problems 0
Loop 2
Copy to Next Blank Row 5
Copy cell range to another sheet 4
Loop Macro 3
Copying a range to a separate excel database 1
Tried and true copy code lines fail me here 7
transfer macro 5

Top