Copy to next empty cell in another workbook
 

A3 in ActiveSheet is a formula =CONCATENATE(F3&"
"&TEXT($F$7,"DD-MMM-YY")&F2&" ZONE")
The macro (courtesy of Ron de Bruin, amended for workbook,sheet and ranges
only) is required to copy the contents of A3 in the ActiveSheet to the NEXT
EMPTY CELL in Corporate Returns Main.xls!Sheet2 column B. Error: Copying is
always done on Sheet2!B3 and not on successive cells. Could someone tell me
what I am doing wrong.

Sub CorporateReturnsToMain()
'
' CorporateReturnsToMain Macro

'
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("Corporate Returns Main.xls") Then
Set destWB = Workbooks("Corporate Returns Main.xls")
Else
Set destWB = Workbooks.Open("c:\Zone1\Corporate Returns Main.xls")
End If
Lr = LastRow(destWB.Worksheets("Sheet2")) + 1
Set sourceRange = ThisWorkbook.ActiveSheet.Range("A3")
Set destrange = destWB.Worksheets("Sheet2").Range("B" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True

'
End Sub
======
Function LastRow(sh As Worksheet)


On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("B2"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
=====
Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Thank you
--
Robert

Copy to next empty cell in another workbook
 

I think have solved it after many hours. Ron de Bruins code is perfect.
--
Robert