Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
After copy of row, cannot paste to activated sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
After copy of row, cannot paste to activated sheet
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
After copy of row, cannot paste to activated sheet
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
After copy of row, cannot paste to activated sheet
On Oct 30, 7:54 am, "Don Guillett" wrote:
Do it withOUT selections range("sourcerange").copy sheets("destsheet").range("destinationrange") -- Don Guillett Microsoft MVP Excel SalesAid Software "mr_skot" wrote in message oups.com... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
After copy of row, cannot paste to activated sheet
sub trythis() with sheets("destsheetname") lr=.cells(rows.count,"a").end(xlup).row+1 range("sourcerange").copy .cells(lr,"a") end with -- Don Guillett Microsoft MVP Excel SalesAid Software "mr_skot" wrote in message oups.com... On Oct 30, 7:54 am, "Don Guillett" wrote: Do it withOUT selections range("sourcerange").copy sheets("destsheet").range("destinationrange") -- Don Guillett Microsoft MVP Excel SalesAid Software "mr_skot" wrote in message oups.com... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
After copy of row, cannot paste to activated sheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy from one Sheet and paste on another sheet based on condition | Excel Discussion (Misc queries) | |||
Bug when Sheet Protection Activated | Excel Programming | |||
Macro to run when sheet activated | Excel Programming | |||
Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
automatic copy and paste from sheet to sheet in a workbook | Excel Programming |