After copy of row, cannot paste to activated sheet

[mr_skot]

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

 

[Don Guillett]

Do it withOUT selections
range("sourcerange").copy sheets("destsheet").range("destinationrange")

 

[OssieMac]

Don is right in his suggestion but just to point out why your your code does
not work is executing other code between the copy and paste and the copied
data is lost from the clipboard before it is pasted. Find the destination
first and then copy and paste without other code in between.

Regards,

OssieMac

 

[mr_skot]

Do it withOUT selections
range("sourcerange").copy sheets("destsheet").range("destinationrange")

Thank you for the reply. One more question.

So I should test for a valid target row before performing the paste?

Something along the lines of:

-Get the source row
-Check for clear target row
-Set valid target row
-Copy source row
-Paste to valid target row

Something like this?

 

[Don Guillett]

sub trythis()
with sheets("destsheetname")
lr=.cells(rows.count,"a").end(xlup).row+1
range("sourcerange").copy .cells(lr,"a")
end with

 

[OssieMac]

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