Hi again,

I have edited your code. It is not the way I would write it but I am sure
you will progress to better code with some more practice. Note my comments
throughout the code.

Should not use integers for row numbers because integers can only hold
values in the range -32,768 to 32,767 and there are more rows than that in a
worksheet.
Long can hold values in range -2,147,483,648 to 2,147,483,647.

There is no need to convert numbers to strings if you use the ambersand (&)
to concatenate. VBA handles the conversion for you.

When changing the active workbook, it is a good idea to specifically address
the required worksheet otherwise you might have the wrong worksheet active.

Feel free to get back to me if you still have problems.


Sub test()
Dim myRow As Long 'changed by OssieMac
Dim myRowS As Long 'changed by OssieMac'row source
Dim myRowT As Long 'changed by OssieMac'row target
'Dim myRowSS As String 'Not required. OssieMac
'Dim myRowTS As String 'Not required. OssieMac

Dim wsSource As Worksheet 'OssieMac Added
Dim wsTarget As Worksheet 'OssieMac Added
'----------------------------------------------------------------

'declare and set the names of your workbooks
'----------------------------------------------------------------
Dim sourceBook As Workbook
Dim targetBook As Workbook

Set sourceBook = Workbooks("Book1.xls")
Set targetBook = Workbooks("Book2.xls")
'----------------------------------------------------------------

'get the row the cursor is in now on the source sheet
'----------------------------------------------------------------
Set wsSource = ActiveSheet 'OssieMac Added
myRow = ActiveCell.Row
myRowS = myRow 'set source row Integervariable
myRowT = myRow 'set target row Integer variable
'----------------------------------------------------------------

'select that row, copy and activate the target sheet

'----------------------------------------------------------------
'Rows(myRowS).Select 'OssieMac commented out
'Selection.Copy 'OssieMac commented out

targetBook.Activate 'activate the target Worksheet

'Identify the specific sheet because it only works
'if the correct sheet is the active sheet when the
'target workbook is activated.
'Example:
'Sheets("Sheet1").Activate
Set wsTarget = ActiveSheet 'OssieMac added

'----------------------------------------------------------------

'start subroutine to check if we have already pasted in this row
'and if we have, move down until we find an empty row
'----------------------------------------------------------------
'myRowTS = myRowT 'set a String var for target

'Sheet 'OssieMac commented out. Does nothing

Range("A" & myRowT).Activate 'select the row on the target

'Sheet 'OssieMac commented out. Does nothing

'check to see if there is any text in that cell
'and if there is, increment the row by 1 AND...
'keep going till we find an open row to paste in

While (ActiveCell < "")
myRowT = myRowT + 1
'myRowTS = myRowT 'Not required. OssieMac
Range("A" & myRowT).Activate '+ to & by OssieMac
Wend

'Not sure what you are doing with the above code.
'Do you have gaps in the target data and you simply
'want to find the first blank row after the row number
'being copied?

'If you just want to go to the bottom of the existing
'data use the following in lieu of the While/Wend loop.

'myRowT = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

'The above is like selecting the last cell in
'column A, hold Ctrl and press up arrow and then
'offset down one cell. OssieMac

'----------------------------------------------------------------

'now that we have an empty row on the target, get ready to paste
'----------------------------------------------------------------

'Copy/Paste by OssieMac.
'Note that a space and underscore at the end of
'a line is a code break in what is otherwise a
'single line of code.
wsSource.Rows(myRowS).Copy _
Destination:=wsTarget.Range("A" & myRowT)

'Rows(myRowT).Select
'ActiveSheet.Paste 'paste the entire row
'sourceBook.Activate 'activate the source
'spreadsheet
'----------------------------------------------------------------

'time to position the cursor to the next line on
'the source sheet so we can enter more data without stopping
'----------------------------------------------------------------

'Need to re-activate the source workbook before resetting
'to the next row otherwise it activates the cell in the
'target workbook. OssieMac

wsSource.Activate 'Added by OssieMac

'myRowSS = " " 'clear out the string
'myRowSS = (myRowS + 1) 'advance row to the next row

'In the following line the + sign actually adds 1 to the row
'value and the & sign concatenates the values. OssieMac
Range("A" & myRowS + 1).Activate 'set the cursor to the nextRow
'----------------------------------------------------------------

'Below is now fixed by selecting source workbook
'and then selecting the next cell. OssieMac

' **** NOTE TO SELF ****
' The line above this one will still be in a selected mode
'(from the copy request)
' but that goes away when you start entering data again.
' I need to solve how to de-select that line to make it cleaner.

End Sub


Regards,

OssieMac