Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Activesheet.Paste does OK manually between worksheets but not in M
Background: An area containing data is manually hi-lited and designated for
copying into WorkSheet A (W/S-A). W/S-B is the intended destination. Under the manual mode, the selected area is pasted into W/S-B without a hitch. The paste process in W/S- B was carried out while a Macro was being recorded. When the Macro was subsequently Run, it failed at Activesheet.Paste Question: Why does the process work manually, but not from a Macro? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Activesheet.Paste does OK manually between worksheets but not in M
If the macro failed what was the error? what was the code you used to perform the task? we need more than you gave to give you an answer! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34846 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Activesheet.Paste does OK manually between worksheets but not in M
Hi Billy,
A picture is worth a 1000 words. Likewise a sample of the code. Would you like to post the recorded code. -- Regards, OssieMac "Billyruben" wrote: Background: An area containing data is manually hi-lited and designated for copying into WorkSheet A (W/S-A). W/S-B is the intended destination. Under the manual mode, the selected area is pasted into W/S-B without a hitch. The paste process in W/S- B was carried out while a Macro was being recorded. When the Macro was subsequently Run, it failed at Activesheet.Paste Question: Why does the process work manually, but not from a Macro? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Activesheet.Paste does OK manually between worksheets but not
Hey Ossie
This is what the Macro generated sans the standard lines: Range("C4").Select ActiveSheet.Paste Running the Macro through from the begining, however, produces an error. (Again the data to be copied resides (originates) in a different worksheet(A). But as stated, doing a manual Paste into an worksheet(B) works fine and even generates a Macro. It's when Running the Macro that the thing falls apart.) Thanks "OssieMac" wrote: Hi Billy, A picture is worth a 1000 words. Likewise a sample of the code. Would you like to post the recorded code. -- Regards, OssieMac "Billyruben" wrote: Background: An area containing data is manually hi-lited and designated for copying into WorkSheet A (W/S-A). W/S-B is the intended destination. Under the manual mode, the selected area is pasted into W/S-B without a hitch. The paste process in W/S- B was carried out while a Macro was being recorded. When the Macro was subsequently Run, it failed at Activesheet.Paste Question: Why does the process work manually, but not from a Macro? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Activesheet.Paste does OK manually between worksheets but not
Hi again Billy,
I need all the code from where you select, copy and then paste it. I am assuming there is something wrong in the selecting/activating of windows/worksheets. -- Regards, OssieMac "Billyruben" wrote: Hey Ossie This is what the Macro generated sans the standard lines: Range("C4").Select ActiveSheet.Paste Running the Macro through from the begining, however, produces an error. (Again the data to be copied resides (originates) in a different worksheet(A). But as stated, doing a manual Paste into an worksheet(B) works fine and even generates a Macro. It's when Running the Macro that the thing falls apart.) Thanks "OssieMac" wrote: Hi Billy, A picture is worth a 1000 words. Likewise a sample of the code. Would you like to post the recorded code. -- Regards, OssieMac "Billyruben" wrote: Background: An area containing data is manually hi-lited and designated for copying into WorkSheet A (W/S-A). W/S-B is the intended destination. Under the manual mode, the selected area is pasted into W/S-B without a hitch. The paste process in W/S- B was carried out while a Macro was being recorded. When the Macro was subsequently Run, it failed at Activesheet.Paste Question: Why does the process work manually, but not from a Macro? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Activesheet.Paste does OK manually between worksheets but not
Hey Simon
Sub Macro15() ' ' Macro recorded 11/28/2008 by Billy ' ActiveSheet.Paste Range("C4").Select ' End Sub While Running the Macro, it stops and I get a MS Visual Basic window displaying: Run-time error '1004': Paste method of Worksheet class failed "Simon Lloyd" wrote: If the macro failed what was the error? what was the code you used to perform the task? we need more than you gave to give you an answer! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34846 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Activesheet.Paste does OK manually between worksheets but not
You need to select the required worksheet then the first cell of the range to
paste and then paste it. Like the full recorded macro below to copy and paste. Range("A1:D7").Select Selection.Copy Sheets("Sheet2").Select Range("B3").Select ActiveSheet.Paste If you have already selected the range and copied it before running the macro to paste then it would look like this Sheets("Sheet2").Select Range("B3").Select ActiveSheet.Paste -- Regards, OssieMac "Billyruben" wrote: Hey Simon Sub Macro15() ' ' Macro recorded 11/28/2008 by Billy ' ActiveSheet.Paste Range("C4").Select ' End Sub While Running the Macro, it stops and I get a MS Visual Basic window displaying: Run-time error '1004': Paste method of Worksheet class failed "Simon Lloyd" wrote: If the macro failed what was the error? what was the code you used to perform the task? we need more than you gave to give you an answer! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34846 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Activesheet.Paste does OK manually between worksheets but not
WOW OssieMac
I gathered from what you were saying that a Macro can span more than one Workbook(W/B). (I am sorry; I kept saying WorkSheet when I should have been saying WorkBook.) With that in mind, I started the Macro recorder and my first step was to open W/B A. After selecting the desired range, I right clicked and selected Copy. Next I went back to W/B B. Once I had selected the appropriate cell, I right clicked and selected Paste. IT WORKED! The final Macro looks like this: Sub Macro16() ' Windows("2008 Bank Statements.xls").Activate Range("A21:D59").Select Selection.Copy Windows("Bank Statement Import Worksheet.xls").Activate Range("C4").Select ActiveSheet.Paste End Sub Since the data in W/B A varies in the number of rows from one month to the next, I will first select the range that I need; then I will go to W/B B to Run the Macro. Ah, but that's a different story. Thanks for your help; it has been educational as well. Best Wishes "OssieMac" wrote: You need to select the required worksheet then the first cell of the range to paste and then paste it. Like the full recorded macro below to copy and paste. Range("A1:D7").Select Selection.Copy Sheets("Sheet2").Select Range("B3").Select ActiveSheet.Paste If you have already selected the range and copied it before running the macro to paste then it would look like this Sheets("Sheet2").Select Range("B3").Select ActiveSheet.Paste -- Regards, OssieMac "Billyruben" wrote: Hey Simon Sub Macro15() ' ' Macro recorded 11/28/2008 by Billy ' ActiveSheet.Paste Range("C4").Select ' End Sub While Running the Macro, it stops and I get a MS Visual Basic window displaying: Run-time error '1004': Paste method of Worksheet class failed "Simon Lloyd" wrote: If the macro failed what was the error? what was the code you used to perform the task? we need more than you gave to give you an answer! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34846 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Activesheet.Paste does OK manually between worksheets but not
I am grateful as well to Simon Lloyd for his interest in assisting me.
Best wishes to you Simon. "Simon Lloyd" wrote: If the macro failed what was the error? what was the code you used to perform the task? we need more than you gave to give you an answer! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34846 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Activesheet.Paste does OK manually between worksheets but not
Hi again Billy,
Obviously both workbooks were open at the correct worksheets when you were recording the macro. For a number of reasons, the activesheet of the workbook might not be the one you need. Therefore add lines to select the correct worksheet both for the source and the output otherwise it will select cells in whatever is the active sheet. Sample below showing where to place them. Replace Sheet1 and Sheet2 with the name of the worksheets where you are copying from and pasting to. Sub Macro16() ' Windows("2008 Bank Statements.xls").Activate Sheets("Sheet1").Select Range("A21:D59").Select Selection.Copy Windows("Bank Statement Import Worksheet.xls").Activate Sheets("Sheet2").Select Range("C4").Select ActiveSheet.Paste End Sub Also, does the data to be copied always start at A21 and do you only have blank space below the data to be copied. if so, the following code will select the range of varying number of rows. Sub Macro1() Sheets("Sheet1").Select Range(Cells(21, "A"), Cells(Rows.Count, "D").End(xlUp)).Select End Sub Cells(Rows.Count, "D").End(xlUp) is like placing the cursor on the very last cell in column D and then holding the Ctrl key down and pressing up arrow and it selects the first cell it comes to with data in it. Also another way when recording a macro you can select a range where there is no blank cells by first selecting the start cell then holding the Ctrl and Shift keys down and pressing first the down arrow and then the right arrow. Of course if there is data to the right or below the required range then it will include it so it depends on how your data is on the worksheet. Looks like this. Range("A21").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Don't confuse any of this with good programming. It is not how I would do it but it works and it will get you started on the road to programming. -- Regards, OssieMac |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Activesheet.Paste does OK manually between worksheets but not in M
Billy, if it is in the same workbook it will look like below. Lets assume you have copied the data on sheet1 range A1:F30 and want to paste it in sheet2 range C4 the macro looks like this: Code: -------------------- Sub copy_to_sheet() Range("A1:F30").Select Selection.Copy With Sheets("Sheet2").Range("C4") .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With Application.CutCopyMode = False End Sub -------------------- -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34846 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I name a picture as I paste it from VBA? (ActiveSheet.Past. | Excel Discussion (Misc queries) | |||
ActiveSheet.Paste (error) | Excel Worksheet Functions | |||
ActiveSheet.Paste - Error help? | Excel Discussion (Misc queries) | |||
Can you sort worksheets other than manually? | Excel Discussion (Misc queries) | |||
Working with Macros-Activesheet.Paste | Excel Discussion (Misc queries) |