Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Selecting a Range in a Macro

    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?
    Last edited by kweaver; 2014-02-26 at 17:41.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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"

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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.
    Last edited by kweaver; 2014-02-27 at 01:15.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Last edited by zeddy; 2014-02-27 at 05:37.

  9. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    kweaver (2014-02-27),Maudibe (2014-02-27)

  10. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    FANTASTIC.

    And, even better...I'm learning!! Thanks, Zeddy.

  11. #10
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    For learning VBA you might want to check hte below MS site

    http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx

    Hope this helps.

    TD

  12. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Zeddy,

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

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  14. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    I have Uncles Guido and Generosa but thanks for the offer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •