Copy Macro

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

PCOR

I have a macro that brings up a certain file.
Once the file has been loaded, the macro sets up a filter on Col AAll NO
blank lines)
Now I want to copy the results of the filter. The width(ie COL is always the
same Col A to J)
but the last entry(bottom) always varies.
How can I write a macro to copy EXCAYLY the display the Filter provides?
Thanks and A Merry Xmas to all
 
Hi

Something like this perhaps ?

Sheets(1).Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
Sheets(2).Range("A1")
 
Thanks BUT.......... I tried that an nothing happened.
Any other ideas
Thanks again

Harald Staff said:
Hi

Something like this perhaps ?

Sheets(1).Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
Sheets(2).Range("A1")
Click to expand...
 
I take it that you just ran it as is and didn't try to read/understand/adjust it to fit
your geography. It works fine here, so don't blame the idea.
--
HTH. Best wishes Harald
Followup to newsgroup only please.

PCOR said:
Thanks BUT.......... I tried that an nothing happened.
Any other ideas
Thanks again
Click to expand...
 
This slight change grabs the AutoFiltered Range. No error checking though
in case there is no AutoFiltered Range.

ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy
Sheets(1).Range("A1")

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Harald Staff said:
I take it that you just ran it as is and didn't try to
Click to expand...
read/understand/adjust it to fit
 
Very sorry....was not trying to blame anything or any body.
I assumed...wrongly that I could use the code just as presented.
To repeat this is my dilema
I am looking at a page that has been filtered on COL A for all NON BLANLKS.
I have data showing in Col A to Col J
I am trying to copy all the visible data. My predicament is that the
length(rows) varies from time to time.
So I need some code that will copy the block COL A1 to COL XXX(this point
being the last row in the data)
.. I want to copy that block to Sheet 2
Can you please advise
Thanks and have a very Happy 2004

Harald Staff said:
I take it that you just ran it as is and didn't try to
Click to expand...
read/understand/adjust it to fit
 
Thanaks for the reply.
I am fairly new at the stuff...Does you code say:
Take all in the active sheet that is visible and copy it to Sheet1 Col a1?

I tried the code you gave me and I git the following error message: COPY
METHOD OF RANFECLASS FAILED" but it did do the copy to sheet 1
Please advise
 
What you describe is exactly what the code is supposed to do -and what it does here. Sure
your list starts in Sheet 1 A1 and not in row 3 or something ? Change to A3 if so.
Sheets(1) btw means the very first sheet in the workbook and has nothing to do with its
name.
 
Hello. In your op, you mentioned...
....the macro sets up a filter on Col AAll...
Click to expand...

I wasn't sure what col AA11 was. I was assuming you meant that you have an
AutoFilter applied in columns AA:LL, and the top & bottom rows were
variable.

If you have an "Autofiltered" list, this grabs the AutoFiltered data for the
copy. Not the entire sheet. The only advantage here is that you do not
have to keep track of the exact address.

What kind of Filter are you using?

'Error check:
If Not ActiveSheet.AutoFilterMode Then
MsgBox "No AutoFilter!"
End If

ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy
Sheets("MySheet").Range("A1")
 
I am running "Data>filter>auto filter
Col A is filtered "NON blanks"
Like I said before, this code you gave me does work and the data is copied
BUT it still gives me an error message:"COPY
METHOD OF RANGE LASS FAILED"
Your help is greatly appreciated.
 
Back
Top