Copy Patse Macro
 

Hi,

I begin to learn to set a macro for the following codes where the data
from the table in an excel worksheet ( ie the range could be from A1
to H50 or A1 to K245 depending the data file ) exclude hidden rows,
is copied to a cell below 5 rows of the above table : -

Range("A1").End(xlDown).Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Exp_Grp").Select
Range("A1").End(XlDown).Offset(5,0).Select
ActiveSheet.Paste
Application.CutCopyMode = False

When I execute this macro, it prompts an error message
Please help to rectify the above codes, thanks

Regards
Len

Copy Patse Macro
 

like this, maybe?

Sheets("Exp_Grp").Select
Range("A1").End(xlDown).Select
ActiveCell.Offset(5, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Copy Patse Macro
 

On Jun 20, 3:34*pm, "Snake Plissken"
wrote:
like this, maybe?

* * Sheets("Exp_Grp").Select
* * Range("A1").End(xlDown).Select
* * ActiveCell.Offset(5, 0).Select
* * ActiveSheet.Paste
* * Application.CutCopyMode = False

Hi,

Thanks for prompt reply.
After rectifying the codes based on your suggestion, it still turns
out the same error message at ActiveSheet.Paste ??

Regards
Len

Copy Patse Macro
 

What kind of message? Please specify.

Copy Patse Macro
 

On Jun 20, 4:00*pm, "Snake Plissken"
wrote:
What kind of message? Please specify.

Hi,

The error message " Run Time error '1004', This selection is not
valid. There are several possible reasons : -
a) Copy & paste areas cannot overlap unless they're the same size and
shape
b) If you're using the Create command on the Name submenu of the
Insert menu, the row or column containing the proposed names won't.....
( not enough space to show the remaining text )

Note : macro run on excel'2000 and the table that copy over is after
running subtotal functions

Regards
Len

Copy Patse Macro
 

It seems that the destination sheet must have at least 2 rows of data.
Otherwise macro tries to select the very bottom of the sheets and them paste
data below it - which is of course impossible
Run macro step by step (F8) then you'll see what's going on

I have changed the code to omit the problem somehow but it can be done in a
a better wa I suppose

Sub test()
'it should be run from source sheet

Range("A1").End(xlDown).Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Exp_Grp").Select
Range("A1").End(xlDown).Select
Selection.CurrentRegion.Select
If Selection.Rows.Count 1 Then
Range("A1").End(xlDown).Select
ActiveCell.Offset(5, 0).Select
ActiveSheet.Paste
Else
Range("A1").Select
ActiveCell.Offset(5, 0).Select
ActiveSheet.Paste

End If

Application.CutCopyMode = False

End Sub

Copy Patse Macro
 

On Jun 20, 5:13*pm, "Snake Plissken"
wrote:
It seems that the destination sheet must have at least 2 rows of data.
Otherwise macro tries to select the very bottom of the sheets and them paste
data below it *- which is of course impossible
Run macro step by step (F8) then you'll see what's going on

I have changed the code to omit the problem somehow but it can be done in a
a better wa I suppose

Sub test()
'it should be run from source sheet

* * Range("A1").End(xlDown).Select
* * Selection.CurrentRegion.Select
* * Selection.SpecialCells(xlCellTypeVisible).Select
* * Selection.Copy
* * Sheets("Exp_Grp").Select
* * Range("A1").End(xlDown).Select
* * Selection.CurrentRegion.Select
* * If Selection.Rows.Count 1 Then
* * * * Range("A1").End(xlDown).Select
* * * * ActiveCell.Offset(5, 0).Select
* * * * ActiveSheet.Paste
* * Else
* * * * Range("A1").Select
* * * * ActiveCell.Offset(5, 0).Select
* * * * ActiveSheet.Paste

* * End If

* * Application.CutCopyMode = False

End Sub

Hi

Based on your new codes and run F8 step by step going thro the macro,
the same error message prompts at ActiveSheet.Paste and it stops

It seems that the selected range from A3 to O97 ( ie row
3,22,26,29,33,39,45,47,49,55,57,59,61,97 ) is unable to copy over and
it hangs at that worksheet

Regards
Len

Copy Patse Macro
 

On Jun 20, 7:47*pm, Len wrote:
On Jun 20, 5:13*pm, "Snake Plissken"
wrote:





It seems that the destination sheet must have at least 2 rows of data.
Otherwise macro tries to select the very bottom of the sheets and them paste
data below it *- which is of course impossible
Run macro step by step (F8) then you'll see what's going on

I have changed the code to omit the problem somehow but it can be done in a
a better wa I suppose

Sub test()
'it should be run from source sheet

* * Range("A1").End(xlDown).Select
* * Selection.CurrentRegion.Select
* * Selection.SpecialCells(xlCellTypeVisible).Select
* * Selection.Copy
* * Sheets("Exp_Grp").Select
* * Range("A1").End(xlDown).Select
* * Selection.CurrentRegion.Select
* * If Selection.Rows.Count 1 Then
* * * * Range("A1").End(xlDown).Select
* * * * ActiveCell.Offset(5, 0).Select
* * * * ActiveSheet.Paste
* * Else
* * * * Range("A1").Select
* * * * ActiveCell.Offset(5, 0).Select
* * * * ActiveSheet.Paste

* * End If

* * Application.CutCopyMode = False

End Sub

Hi

Based on your new codes and run F8 step by step going thro the macro,
the same error message prompts at ActiveSheet.Paste and it stops

It seems that the selected range from A3 to O97 ( ie row
3,22,26,29,33,39,45,47,49,55,57,59,61,97 ) is unable to copy over and
it hangs at that worksheet

Regards
Len- Hide quoted text -

- Show quoted text -

One more thing, the macro copies the selected range from A3 to O97 and
move to cell A3 and it stops when it hit the code "
Range("A1").End(xlDown).Select and ActiveCell.Offset(5, 0).Select"

Copy Patse Macro
 

I'll look on that next week but it works with sample data I put. Strange...

Copy Patse Macro
 

On Jun 20, 8:25*pm, "Snake Plissken"
wrote:
I'll look on that next week but it works with sample data I put. Strange....

Hi Snake,

After exploring more scenarios, I discovered copy/paste VBA based on
the following modified codes, is workable in 1st scenario ( ie where
copy range starts from A1:A1103 of which filtered cells are from
A2:A1103) and not workable in 2nd scenario ( ie where copy range
starts from A3:A97 of which filtered cells are from A22:A1103 and
cells A1 to A3 are not filtered) where it stops at cell A3 after it
hits the code " ActiveSheet.Paste "

Sub Copy_Paste()

Sheets("GL-LG").Select
Range("A1").End(xlDown).Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Range("A1").End(xlDown).Select
ActiveCell.Offset(10, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Any idea on the above problem ?, thanks


Regards
Len