Selecting a Range in a Macro

Topic

New Reply

I’m trying to learn VBA.

I have a sheet with 50 rows. Eventually, the sheet will have more rows, week by week.

In my macro, I want to select all of the rows and columns whenever the macro runs.

When I recorded the macro and selected all of them, I got this code:
Sheets(“Main”).Select
Range(“B1”).Select
Selection.AutoFilter
ActiveSheet.Range(“$A$1:$BN$50”).AutoFilter Field:=2, Criteria1:= _
“Dorado Hills”
Range(“$A$1:$BN$50”).Select
Range(“B1”).Activate
Selection.Copy
Sheets(“Dorado Hills”).Select
ActiveSheet.Unprotect Password:=”whatever”
Range(“A1”).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range(“A1″).Select
ActiveSheet.Protect Password:=”whatever”

The objective is to turn on filtering, select all of the rows, filter based on a name (in this case “Dorado Hills”) and copy the filtered results.
Move to another sheet and paste them.

But, if the 50 is in the macro, what will happen the next time when there are 80 rows?
And, if the columns go beyond BN?

Won’t I be missing the additional/new rows?

Reply | Quote

Replies

Use:
worksheets(“main”).usedrange

instead of the explicit range.
[BTW, you don’t need to select to work on ranges. Not selecting will speed up the macro;

Steve

Reply | Quote

Maud, I was counting on that working…

But it appears to have filtered on the A column when it should have been the B column. Then, the target sheet where the paste was done ended up starting at the first filtered line.

It’s also only selecting to the O column from MAIN rather than the BN column.

I’m trying to understand the code and maybe I’ll find a clue in my fumbling around with VBA.

I made a few uneducated changes and think I’m getting closer. Error still on: ActiveSheet.Paste

Sheets(“Main”).Activate
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Range(“B1”).Select
Selection.AutoFilter
Range(Cells(1, 2), Cells(1, LastCol)).AutoFilter Field:=2, Criteria1:= _
“Dorado Hills”
Range(“A1”).Activate
Range(Cells(1, 1), Cells(LastRow, LastCol)).Copy
Sheets(“Dorado Hills”).Activate
ActiveSheet.Unprotect Password:=”whatever”
Range(“A1”).Select
ActiveSheet.Paste
Range(“A1″).Select
ActiveSheet.Protect Password:=”whatever”
Sheets(“Main”).Activate
Range(“A1”).Select
End Sub

It seems I can run it once, but the second time I run it, I get the error.

Reply | Quote

Hi kweaver

see attached workbook; run following routine:

Code:

Sub filterCopy()

Sheets("Dorado Hills").Select                   'switch to destination sheet
ActiveSheet.Unprotect Password:="whatever"      'unlock
Cells.Clear                                     'clear everything

Sheets("Main").Activate                         'switch to main sheet
ActiveSheet.AutoFilterMode = False              'turn OFF any autofilters that have been set

[a1].AutoFilter Field:=2, Criteria1:="Dorado Hills" 'set required filter

[a1].CurrentRegion.Copy                         'copy headings and filtered record rows
Sheets("Dorado Hills").Select                   'switch to destination sheet
[a1].Select                                     'put cellpointer in copy-to location
ActiveSheet.Paste                               'paste copied data from clipboard
[a2].Select                                     'put cellpointer in tidy location

Application.CutCopyMode = False                 'cancel highlighted copy-to range
ActiveSheet.Protect Password:="whatever"        'lock sheet

Sheets("Main").Activate                         'switch back to main sheet
[a2].Select                                     'put cellpointer in tidy location

'ActiveSheet.AutoFilterMode = False              'turn OFF any autofilters that have been set

End Sub

The CurrentRegion is a useful property that will handle any rows that are added later.

let me know if this works for you.
I think it’s a lot simpler to follow,

zeddy

Reply | Quote

Hi kweaver

The reason you need to clear the destination sheet first is because you might otherwise overwrite an existing larger number of records on that sheet with a smaller subset of records from the filtered list.

zeddy

zeddy

Reply | Quote

Hi kweaver

..and Mauds typos was using
Field:=1
..instead of
Field:=2

and using..
LastCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
instead of”
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
..to get the last headings column in row 1 (rather than the last data column used in row2)

Maud: drink some coffee!
zeddy

Reply | Quote

Still learning…

This still isn’t working.

Am I getting close?

Sheets(“Main”).UsedRange
Range(“B1″).Select
Selection.AutoFilter
ActiveSheet.AutoFilter Field:=2, Criteria1:=”Dorado Hills”
Range(“B1”).Activate
Selection.Copy
Sheets(“Dorado Hills”).Select
ActiveSheet.Unprotect Password:=”whatever”
Range(“A1”).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range(“A1″).Select
ActiveSheet.Protect Password:=”whatever”

Reply | Quote

KW,

Try the following code

Code:

Public Sub CopyRange()
LastCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Range(“B1”).Select
Selection.AutoFilter
Range(Cells(2, 1), Cells(2, LastCol)).AutoFilter Field:=1, Criteria1:= _
        “Dorado Hills”
Range(“B1”).Activate
Range(Cells(2, 1), Cells(LastRow, LastCol)).Copy
Sheets(“Dorado Hills”).Activate
ActiveSheet.Unprotect Password:=”whatever”
Range(“A1″).Select
ActiveSheet.Paste
ActiveSheet.Protect Password:=”whatever”
End Sub

Reply | Quote

FANTASTIC.

And, even better…I’m learning!! Thanks, Zeddy.

Reply | Quote

For learning VBA you might want to check hte below MS site

http://msdn.microsoft.com/en-us/library/ee814737%28v=office.14%29.aspx

Hope this helps.

TD

Reply | Quote

Zeddy,

That wasn’t too far off done on a net book without Excel and untested. Thanks for the corrections!

Reply | Quote

Hi Maud

To do that on a net book without Excel is truly magic. Amazing.

Hang on. A net book without Excel????
That is major punishment.
What kind of world is that.
Who would do that to you.
Do you want somebody hurt?
I can call my brothers.

zeddy
PS Thanks for all the corrections you’ve done for me

Reply | Quote

I have Uncles Guido and Generosa but thanks for the offer.

Reply | Quote