Macro to copy a range

  • Thread starter Thread starter PCOR
  • Start date Start date
P

PCOR

I have asked in other NG. Got some very good answers from very helpful
people (The trouble was that these people assumed that I know a great deal
about EXcel...I do not)that left me a little confused. Here is my
predicament
I have a sheet called db1
The sheet contains data in Col A, B ,C, D,E,F,G,H,I,J
I have applied a filter to this sheet. Col A is set to show only NON BLANK
lines

I want to copy the data currently displayed(Ie NON blank lines) to and other
sheet
The columns are always the same ones but the number of rows varies all the
time.
So I need a macro that will copy from ALL and only the visible data.
(ie copy the data in the above named columns down to the last row showing .
Can/will some one give me the code required to do this.
Thanks and Have a very happy 2004
 
One way:

Public Sub CopyVisible()
Sheets("db1").Range("A1").CurrentRegion.SpecialCells( _
xlCellTypeVisible).Copy Destination:=Sheets(2).Range("A1")
End Sub
 
hope the following helps, if someone more knoledgable than me is checking my
code, what variant type should i give to "rng"


Sub CopyNonBlanks()
Dim i As Integer, rng As Variant
Application.ScreenUpdating = False
sheets("db1").select
i = 0
For Each rng In Range(Range(("A1")), Range("A1").End(xlDown))
If rng <> "" Then
Range(rng, rng.Offset(0, 9)).Copy
Sheets("Sheet2").Range("A1").Offset(i, 0).PasteSpecial
i = i + 1
End If
Next rng
CutCopyMode = False
End Sub
 
Thank you for the code.
It did the trick and did copy the data to the second sheet....BUT
I also received an error message
"Run time error 1004"
"Copy method of range class failed"
I wonder why the error
 
Back
Top