Using Excel 2003 SP2 (11.8105.8107)

I was asked to create an Excel Macro that would perform the following:

- Copy newly entered data from a source spreadsheet (regardless of
worksheet) to a target spreadsheet.
- Reactivate the source and allow for more data entry.

The data from the source to the target needs to be copied regardless
of the worksheet the user is on. For example if there are multiple
tabs on the source sheet (maybe different regions) I should always
copy the data to the first tab on the target sheet.

Now for my question.

I had the code working fine when I recorded the macro. Not a
problem. But the next day I ran into an issue with the dreaded 'Error
#9' when I attempted to activate the new sheet stepping through the
macro. I'm guessing it was because Excel didn't recognize the sheet
name.

So I compensated by declaring the sheet (as workbooks) ahead of time.
This also allows the user to define the name of the spreadsheets ahead
of time. Tested it and it works!

BUT....

For some odd reason when I run this macro it will not copy the row
from the target to the source sheet.

So I'm looking for ideas. It appears to highlight the row and perform
the copy, select the target sheet and paste, but there is never
anything there.

Maybe something is getting lost between the activates?

Here is the code. (Please be gentle with you critique since I am
really new at this and have not programmed any VB / Excel Macro code
before this attempt. Obviously this code is not optimized as I am a
noob.)



Sub SourceCopyToTarget()

' ** This macro requires Excel to position
' ** the cursor to the right after a <return
' ** otherwise it's not going to work
' **
' ** See: TOOLS- OPTIONS- EDIT tab
' ** Set position after return to RIGHT
' **
' As always YMMV. Good luck.


' declare some variables
'----------------------------------------------------------------
Dim myRow As String
Dim myRowS As Integer 'row # for source sheet
Dim myRowT As Integer 'row # for target sheet
Dim myRowSS As String 'we need string versions also
Dim myRowTS As String 'we need string versions also
'----------------------------------------------------------------


'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
'----------------------------------------------------------------
myRow = ActiveCell.Row
myRowS = myRow 'set source row Integer
variable
myRowT = myRow 'set target row Integer
variable
'----------------------------------------------------------------



'select that row, copy and activate the target sheet

'----------------------------------------------------------------
Rows(myRowS).Select 'select the entire row
Selection.Copy 'copy the entire row
targetBook.Activate 'activate the target
worksheet

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



'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
Range("A" + myRowTS).Activate 'select the row on the target
sheet

'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
Range("A" + myRowTS).Activate
Wend

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



'now that we have an empty row on the target, get ready to paste
'----------------------------------------------------------------
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
'----------------------------------------------------------------
myRowSS = " " 'clear out the
string
myRowSS = (myRowS + 1) 'advance row to the next row
Range("A" + myRowSS).Activate 'set the cursor to the next
row
'----------------------------------------------------------------



' **** 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




There you have it. I appreciate any help you can give.

-mr_skot