Macro Help

  • Thread starter Thread starter Jason G
  • Start date Start date
J

Jason G

I'm recording a macro in excel, in short i have a s/sheet template with 5
worksheets in it and i want to record a macro to run the following;

I paste a dataset into worksheet A
i autofilter it and cut out chunks of data based on the autofilter
selections and paste it into the other sheets based on several different
filter selections on a couple of different columns, leaving me with a
percentage of the original dataset in worksheet A and chunks of it in
worksheets B-D

now when i autofilter i select all rows under this filter to cut and paste
into one of the other worksheets

in my macro this reads as a long list of the row numbers of the rows in
question from the instance in which i record the macro, but next time i want
to run this macro i want it to just take the rows displayed under the
autofilter selections, not the row numbers that were relevant first time
round.....

how do i get it to pick up on the fact that i'm cutting out the results (all
rows) of a specified autofilter criteria rather than specific rows?
 
Assuming a header row in row 1, you can use these two lines of code to select
only visible data starting in row 2:

Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
 
thansk, this helped loads, although it prompted another query, while running
different filters to grab different chunks of data if i manually select the
first empty row in my destination worksheets this is giving me the same
problem when recording my macro, i.e. it's recording a macro with a row
number in it, what's the code equivelent for 'first empty row'?

many thanks.....
 
Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).Select is coming up
highlighted as an error in the debugger, any clues?
 
'Some code to find first blank cell in a column:

'Find row number of first blank cell in column A
x = Range("A:A").Find(What:="", SearchOrder:=xlByRows).Row


For your second question, I am unsure. I can't get that line of code to
produce any error on my hand. Perhaps the fastest solution would be to record
the action and reproduce? Record a macro of you selecting cell A2, and then
hitting Ctrl+Shift+End.
Stop recording, and see what the coding says.
 
Gord and Luke

many thanks for your help, have finally fudged a working macro, now to test
it against different datasets

many thanks again though guys, coding is not my bag!
 
Back
Top