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?

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?

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?

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?

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

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("A17").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
> >
> >

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("A2159").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("A17").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
> > >
> > >

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

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("A2159").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