COPY > PASTE (macro)

verluc

Well-known Member
Joined
Mar 1, 2002
Messages
1,451
Why doesn't work this macro?
I receive an error message on the line :
Range("A5").PasteSpecial

This is my macro :

Sub Filter()
currentworkbook = ActiveWorkbook.Name
currentsheet = ActiveSheet.Name
Columns("L:L").Select
Selection.autofilter Field:=1, Criteria1:="1"
Range("A5:" & Range("K65536").End(xlUp).Address).Copy
Workbooks.OpenText Filename:="G:Mijn documentenMap1.xls"
Sheets("Blad1").Select
Range("A5").PasteSpecial
Workbooks(currentworkbook).Sheets(currentsheet).Activate
Selection.autofilter
Range("A1").Select
End Sub

Many thanks for help.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hmm...this code worked for me. Maybe theres something wrong with your cells? Is range a5 merged with any others in the workbook you opened?
 
Upvote 0
On 2002-04-19 01:27, robfo0 wrote:
Hmm...this code worked for me. Maybe theres something wrong with your cells? Is range a5 merged with any others in the workbook you opened?
Thanks for your answer.You are right.There was a merged with any others in the workbook.
Maybe I may ask you another question: I want to clear all in the sheet Blad1,before pastespecial.Is this possible,or do I manuelly clear this before pastespecial?
Many thanks for your time.
 
Upvote 0
On 2002-04-19 02:58, Tommy Bak wrote:
insert this line when blad1 is selected
Cells.ClearContents
regards Tommy
Hi Tommy,

Yes the cells are cleared before paste,but it doesn't work,maybe the copied cells have an interiorcolor.Those colors are not cleared.Can I add any code to also clear those colors,before paste?
Thanks
 
Upvote 0
On 2002-04-19 04:05, Tommy Bak wrote:
Yes Verluc, no problem.
Cells.Clear

regards Tommy
Hi Tommy,

When I add the code and run the macro,I receive the error 1004
"Methode PasteSpecial of class range ..."
I have a yellow color on the line:
Range("A1").PasteSpecial

Have you any idea?
Thanks
 
Upvote 0
On 2002-04-19 04:13, verluc wrote:
On 2002-04-19 04:05, Tommy Bak wrote:
Yes Verluc, no problem.
Cells.Clear

regards Tommy
Hi Tommy,

When I add the code and run the macro,I receive the error 1004
"Methode PasteSpecial of class range ..."
I have a yellow color on the line:
Range("A1").PasteSpecial

Have you any idea?
Thanks
Hi Tommy,

Have you any idea about this erro message?
Many thanks
 
Upvote 0
On 2002-04-19 05:58, verluc wrote:
On 2002-04-19 04:13, verluc wrote:
On 2002-04-19 04:05, Tommy Bak wrote:
Yes Verluc, no problem.
Cells.Clear

regards Tommy
Hi Tommy,

When I add the code and run the macro,I receive the error 1004
"Methode PasteSpecial of class range ..."
I have a yellow color on the line:
Range("A1").PasteSpecial

Have you any idea?
Thanks
Hi Tommy,

Have you any idea about this erro message?
Many thanks
Hi Tommy,

When I delete the instruction Cells.Clear,then the macro works.
With the instruction Cells.Clear,the macro doesn't work.

Any Idea?
 
Upvote 0
Hi verluc.
I've changed your macro a couple of places.
Now it seems to run well.

Sub Filter()
currentworkbook = ActiveWorkbook.Name
currentsheet = ActiveSheet.Name
Columns("L:L").Select
Selection.AutoFilter Field:=1, Criteria1:="1"
Set kopi = Range("A5:" & Range("K65536").End(xlUp).Address)
Workbooks.OpenText Filename:="G:\Mijn documenten\Map1.xls"
Cells.Clear
kopi.Copy
Range("A5").Select
ActiveSheet.Paste
Workbooks(currentworkbook).Worksheets(currentsheet).Activate
Selection.AutoFilter
Range("A1").Select
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top