Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 29

Thread: Selecting Sheets in VBA

  1. 11-11-2006, 03:06 PM #1
    ragamuffin
    ragamuffin is offline
    VBAX Regular
    Joined
    Oct 2006
    Posts
    54
    Location

    Selecting Sheets in VBA

    I have recorded a macro that reads information from several sheets. The recorder has input the names of the sheets (e.g. Sheets ("Name").Select). The workbook will be sent out to several groups where the sheet names will change but the same macro will be used. So I am attempting to use the vba encoded name (e.g. Sheet3.Select) as I have read about in some forums. However, this is not working for some reason. Is there something that I am doing wrong? I am stil a little new to all of this, so thanks very much for your help!

    ragamuffin
    Reply With Quote Reply With Quote
  2. 11-11-2006, 03:21 PM #2
    Norie
    Norie is offline
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Well generally it's not actually needed to select/activate anything.

    But that depends on what you are actually doing.

    It would be helpful if you posted your current code along wih an explanation of what it's meant to do.
    Reply With Quote Reply With Quote
  3. 11-11-2006, 03:48 PM #3
    ragamuffin
    ragamuffin is offline
    VBAX Regular
    Joined
    Oct 2006
    Posts
    54
    Location
    What the code does is this: Unprotects the summary sheet, selects each individual sales rep's sheet, and copies selected cells to a designated area on the summary sheet. Then it uses a filter to organize the data. Lastly, it reprotects the summary sheet. I tried as hard as I could to figure out an easier way to get this all to work, but I just could not figure it out. Well, it works for what I need now, but the workbook needs to be used by other people who will have different reps in their stores, which means that the names of the worksheets will change. That's why I want to make the macro universal, so that when I email the workbook to another store, they can change and reorganize the tabs all they want and the macro will still work.

    I hope that explanation helps. If I can provide anymore info that would be of help, please let me know.

    My code is posted below.



    [VBA]Sub ESN_Summary()
    '
    ' ESN_Summary Macro
    ' Macro recorded 10/30/2006 by pos1
    '
    ' Keyboard Shortcut: Ctrl+Shift+T
    '
    ActiveSheet.Unprotect
    Sheets("Erika").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=24
    Sheets("Evan").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    Range("B50").Select
    ActiveSheet.Paste
    Sheets("Joe").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=42
    Range("B96").Select
    ActiveSheet.Paste
    Sheets("Luis").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=45
    Range("B142").Select
    ActiveSheet.Paste
    Sheets("Priscilla").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=39
    Range("B188").Select
    ActiveSheet.Paste
    Sheets("Rebecca").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=51
    Range("B234").Select
    ActiveSheet.Paste
    Sheets("Rosa T.").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=54
    Range("B280").Select
    ActiveSheet.Paste
    Sheets("Rosie").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=45
    Range("B326").Select
    ActiveSheet.Paste
    Sheets("Open").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=36
    Range("B372").Select
    ActiveSheet.Paste
    Sheets("Luke").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=39
    Range("B418").Select
    ActiveSheet.Paste
    Sheets("James").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=69
    Range("B464").Select
    ActiveSheet.Paste
    Sheets("Juan").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=42
    Range("B510").Select
    ActiveSheet.Paste
    Sheets("Maria").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=42
    Range("B556").Select
    ActiveSheet.Paste
    Sheets("Benita").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=54
    Range("B602").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=21
    Range("A648").Select
    ActiveWindow.ScrollRow = 603
    ActiveWindow.ScrollRow = 601
    ActiveWindow.ScrollRow = 599
    ActiveWindow.ScrollRow = 597
    ActiveWindow.ScrollRow = 594
    ActiveWindow.ScrollRow = 592
    ActiveWindow.ScrollRow = 589
    ActiveWindow.ScrollRow = 585
    ActiveWindow.ScrollRow = 582
    ActiveWindow.ScrollRow = 577
    ActiveWindow.ScrollRow = 574
    ActiveWindow.ScrollRow = 568
    ActiveWindow.ScrollRow = 562
    ActiveWindow.ScrollRow = 557
    ActiveWindow.ScrollRow = 551
    ActiveWindow.ScrollRow = 544
    ActiveWindow.ScrollRow = 537
    ActiveWindow.ScrollRow = 530
    ActiveWindow.ScrollRow = 522
    ActiveWindow.ScrollRow = 514
    ActiveWindow.ScrollRow = 507
    ActiveWindow.ScrollRow = 500
    ActiveWindow.ScrollRow = 493
    ActiveWindow.ScrollRow = 486
    ActiveWindow.ScrollRow = 478
    ActiveWindow.ScrollRow = 472
    ActiveWindow.ScrollRow = 467
    ActiveWindow.ScrollRow = 460
    ActiveWindow.ScrollRow = 455
    ActiveWindow.ScrollRow = 449
    ActiveWindow.ScrollRow = 443
    ActiveWindow.ScrollRow = 436
    ActiveWindow.ScrollRow = 431
    ActiveWindow.ScrollRow = 426
    ActiveWindow.ScrollRow = 419
    ActiveWindow.ScrollRow = 413
    ActiveWindow.ScrollRow = 407
    ActiveWindow.ScrollRow = 400
    ActiveWindow.ScrollRow = 394
    ActiveWindow.ScrollRow = 387
    ActiveWindow.ScrollRow = 381
    ActiveWindow.ScrollRow = 376
    ActiveWindow.ScrollRow = 369
    ActiveWindow.ScrollRow = 362
    ActiveWindow.ScrollRow = 357
    ActiveWindow.ScrollRow = 350
    ActiveWindow.ScrollRow = 344
    ActiveWindow.ScrollRow = 338
    ActiveWindow.ScrollRow = 332
    ActiveWindow.ScrollRow = 325
    ActiveWindow.ScrollRow = 319
    ActiveWindow.ScrollRow = 313
    ActiveWindow.ScrollRow = 307
    ActiveWindow.ScrollRow = 301
    ActiveWindow.ScrollRow = 295
    ActiveWindow.ScrollRow = 289
    ActiveWindow.ScrollRow = 284
    ActiveWindow.ScrollRow = 278
    ActiveWindow.ScrollRow = 272
    ActiveWindow.ScrollRow = 265
    ActiveWindow.ScrollRow = 259
    ActiveWindow.ScrollRow = 253
    ActiveWindow.ScrollRow = 247
    ActiveWindow.ScrollRow = 241
    ActiveWindow.ScrollRow = 234
    ActiveWindow.ScrollRow = 228
    ActiveWindow.ScrollRow = 223
    ActiveWindow.ScrollRow = 218
    ActiveWindow.ScrollRow = 213
    ActiveWindow.ScrollRow = 210
    ActiveWindow.ScrollRow = 207
    ActiveWindow.ScrollRow = 203
    ActiveWindow.ScrollRow = 201
    ActiveWindow.ScrollRow = 199
    ActiveWindow.ScrollRow = 195
    ActiveWindow.ScrollRow = 193
    ActiveWindow.ScrollRow = 190
    ActiveWindow.ScrollRow = 187
    ActiveWindow.ScrollRow = 185
    ActiveWindow.ScrollRow = 182
    ActiveWindow.ScrollRow = 179
    ActiveWindow.ScrollRow = 176
    ActiveWindow.ScrollRow = 173
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 161
    ActiveWindow.ScrollRow = 158
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 134
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 115
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 87
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 1
    Sheets(Array("Erika", "Evan", "Joe", "Luis", "Priscilla", "Rebecca", "Rosa T.", _
    "Rosie", "Open", "Luke", "James", "Juan", "Maria", "Benita")).Select
    Sheets("Erika").Activate
    Range("A2").Select
    Sheets("ESN Summary").Select
    ActiveSheet.Unprotect
    Selection.AutoFilter Field:=1, Criteria1:="<>"
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlNoSelection
    End Sub[/VBA]

    Thanks again!
    Reply With Quote Reply With Quote
  4. 11-11-2006, 03:52 PM #4
    ragamuffin
    ragamuffin is offline
    VBAX Regular
    Joined
    Oct 2006
    Posts
    54
    Location
    Also, now that you see the code, if you happen to know of some ways to clean it up, please let me know.

    Thanks!
    Reply With Quote Reply With Quote
  5. 11-11-2006, 03:57 PM #5
    lucas
    lucas is offline
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    would it be possible for you to post the workbook after you remove any personal or private info. Use manage attachments below where you post after hitting the Post Reply button.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln
    Reply With Quote Reply With Quote
  6. 11-11-2006, 04:09 PM #6
    ragamuffin
    ragamuffin is offline
    VBAX Regular
    Joined
    Oct 2006
    Posts
    54
    Location
    Sure. I had to edit it by taking out several sheets to bring down the file size, so the code that I posted earlier and that is still in the workbook will not exactly correspond with this edited workbook. But, here it is...

    Thanks again!
    Reply With Quote Reply With Quote
  7. 11-14-2006, 03:31 PM #7
    ragamuffin
    ragamuffin is offline
    VBAX Regular
    Joined
    Oct 2006
    Posts
    54
    Location
    I tried to clean up my code a bit and reorganize it. I hope this is a little bit easier to read.

    [VBA]Sub ESN_Summary()
    '
    ' ESN_Summary Macro
    ' Macro recorded 10/30/2006 by pos1
    '
    ' Keyboard Shortcut: Ctrl+Shift+T
    '
    ActiveSheet.Unprotect

    Sheets("Erika").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=24

    Sheets("Evan").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    Range("B50").Select
    ActiveSheet.Paste

    Sheets("Joe").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=42
    Range("B96").Select
    ActiveSheet.Paste

    Sheets("Luis").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=45
    Range("B142").Select
    ActiveSheet.Paste

    Sheets("Priscilla").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=39
    Range("B188").Select
    ActiveSheet.Paste

    Sheets("Rebecca").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=51
    Range("B234").Select
    ActiveSheet.Paste

    Sheets("Rosa T.").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=54
    Range("B280").Select
    ActiveSheet.Paste

    Sheets("Rosie").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=45
    Range("B326").Select
    ActiveSheet.Paste

    Sheets("Open").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=36
    Range("B372").Select
    ActiveSheet.Paste

    Sheets("Luke").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=39
    Range("B418").Select
    ActiveSheet.Paste

    Sheets("James").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=69
    Range("B464").Select
    ActiveSheet.Paste

    Sheets("Juan").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=42
    Range("B510").Select
    ActiveSheet.Paste

    Sheets("Maria").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=42
    Range("B556").Select
    ActiveSheet.Paste

    Sheets("Benita").Select
    Range("A2:G47").Select
    Range("G47").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ESN Summary").Select
    ActiveWindow.SmallScroll Down:=54
    Range("B602").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollRow = 1

    Sheets(Array("Erika", "Evan", "Joe", "Luis", "Priscilla", "Rebecca", "Rosa T.", _
    "Rosie", "Open", "Luke", "James", "Juan", "Maria", "Benita")).Select

    Sheets("Erika").Activate

    Range("A2").Select

    Sheets("ESN Summary").Select

    ActiveSheet.Unprotect
    Selection.AutoFilter Field:=1, Criteria1:="<>"
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub[/VBA]

    Thanks again for any and all help.
    Reply With Quote Reply With Quote
  8. 11-14-2006, 04:03 PM #8
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base     VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Is the spacing between pasted data for specific cells or just to create some approximate spacing.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  9. 11-14-2006, 04:14 PM #9
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    The spacing may not be correct, especia;;y if you're pasting to fit other data but try
    [vba]
    Sub ESN_Summary()

    Dim WShts, sh
    Dim i As Long

    Sheets("ESN Summary").Unprotect
    WShts = Array("Erika", "Evan", "Joe", "Luis", "Priscilla", "Rebecca", "Rosa T.", _
    "Rosie", "Open", "Luke", "James", "Juan", "Maria", "Benita")

    i = 4
    For Each sh In WShts
    Sheets(sh).Range("G47").Copy Sheets("ESN Summary").Cells(i, 2)
    i = i + 35
    Next


    Sheets("ESN Summary").Activate

    Selection.AutoFilter Field:=2, Criteria1:="<>"
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub

    [/vba]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  10. 11-14-2006, 04:24 PM #10
    ragamuffin
    ragamuffin is offline
    VBAX Regular
    Joined
    Oct 2006
    Posts
    54
    Location
    The space between is for the paste. The macro copies everything, scrolls down to the appropriate row where the next paste will occur, and so on until all the copies and pastes are done. Then it filters out any blanks and protects the sheet.

    I tried your code, but I did not get a paste feature. Am I missing something or could you tell me how to add that.

    Also, is there a way to get the sheet names to be non-specific (i.e. to use Sheet3, Sheet12, etc)? Once this is done, a lot of people are going to use it so it has to be universal.

    Thanks so much again!!!
    Reply With Quote Reply With Quote
  11. 11-14-2006, 04:35 PM #11
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    How do you determine where the pastes occur. Is there specific data that can be found by code?
    Regarding the paste feature, this is implied in the copy statement. You can also write
    [VBA]
    Sheets(sh).Range("G47").Copy Destination:=Sheets("ESN Summary").Cells(i, 2)
    [/VBA]

    This will handle sheet numbers instead of names. It will copy only the values from G47 in each sheet into column B. I can't follow the logic of this however.
    [VBA]
    Option Explicit
    Sub ESN_Summary()

    Dim WShts, sh
    Dim i As Long, j As Long

    Sheets("ESN Summary").Unprotect

    i = 4
    For j = 2 To Sheets.Count
    Sheets(j).Range("G47").Copy Sheets("ESN Summary").Cells(i, 2)
    i = i + 3
    Next


    Sheets("ESN Summary").Activate

    Selection.AutoFilter Field:=2, Criteria1:="<>"
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub

    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  12. 11-14-2006, 04:54 PM #12
    ragamuffin
    ragamuffin is offline
    VBAX Regular
    Joined
    Oct 2006
    Posts
    54
    Location
    The copies occur on each rep sheet from A2:G47. Those 46 rows are pasted to the ESN Summary sheet. I simply measured 46 rows after every paste (or selected the next cell after the hilighted area from the previous paste) and performed the paste feature. I know that sounds confusing, but basically, the paste occurs every 46 rows.

    I tried the updated code you gave with the new paste feature and it still does not seem to work...

    Maybe I am running the macro incorrectly. I deleted my current code, then pasted yours just below. So now I have:

    [VBA]Sub ESN_Summary()
    '
    ' ESN_Summary Macro
    ' Macro recorded 10/30/2006 by pos1
    '
    ' Keyboard Shortcut: Ctrl+Shift+T
    '
    Dim WShts, sh
    Dim i As Long, j As Long

    Sheets("ESN Summary").Unprotect

    i = 4
    For j = 2 To Sheets.Count
    Sheets(j).Range("G47").Copy Sheets("ESN Summary").Cells(i, 2)
    i = i + 3
    Next


    Sheets("ESN Summary").Activate

    Selection.AutoFilter Field:=2, Criteria1:="<>"
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub[/VBA]

    Is that correct?
    Reply With Quote Reply With Quote
  13. 11-14-2006, 04:58 PM #13
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Apologies, I was misreading your code
    Try
    [VBA]
    Sub ESN_Summary()
    Dim WShts, sh
    Dim i As Long, j As Long
    Sheets("ESN Summary").Unprotect
    i = 4
    For j = 2 To Sheets.Count
    Sheets(j).Range("A2:G47").Copy Sheets("ESN Summary").Cells(i, 2)
    i = i + 46
    Next
    Sheets("ESN Summary").Activate
    Selection.AutoFilter Field:=1, Criteria1:="<>"
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub

    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  14. 11-15-2006, 07:59 AM #14
    ragamuffin
    ragamuffin is offline
    VBAX Regular
    Joined
    Oct 2006
    Posts
    54
    Location
    Ok, I tried the revised code which looks like this when I add it into my sheet:

    [VBA]Sub ESN_Summary()
    '
    ' ESN_Summary Macro
    ' Macro recorded 10/30/2006 by pos1
    '
    ' Keyboard Shortcut: Ctrl+Shift+T
    '
    Dim WShts, sh
    Dim i As Long, j As Long
    Sheets("ESN Summary").Unprotect
    i = 4
    For j = 2 To Sheets.Count
    Sheets(j).Range("A2:G47").Copy Sheets("ESN Summary").Cells(i, 2)
    i = i + 46
    Next
    Sheets("ESN Summary").Activate
    Selection.AutoFilter Field:=1, Criteria1:="<>"
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub[/VBA]

    I am getting an error when I run it from the shortcut button I created for the previous ESN Summary Macro and the keyboard shortcut (Ctrl + Shft + T) does not work. I enter the code to debug it and the error line seems to be:

    [VBA]Sheets(j).Range("A2:G47").Copy Sheets("ESN Summary").Cells(i, 2)[/VBA]

    So I am not sure what I am doing wrong or if the code is not reading right.

    Any ideas?
    Reply With Quote Reply With Quote
  15. 11-15-2006, 09:53 AM #15
    Norie
    Norie is offline
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    What error are you getting?
    Reply With Quote Reply With Quote
  16. 11-15-2006, 10:22 AM #16
    ragamuffin
    ragamuffin is offline
    VBAX Regular
    Joined
    Oct 2006
    Posts
    54
    Location
    "Run-time error '1004': Cannot change part of a merged cell." I get the options to End, Debug, or Help. I choose debug to see where the problem is at and it I am referenced to this line of code:

    [VBA]Sheets(j).Range("A2:G47").Copy Sheets("ESN Summary").Cells(i, 2)[/VBA]

    The whole code from Sub to End Sub is a bit beyond my level, so I really do not know how to troubleshoot it, because I really don't know what it is doing...sorry.

    Thanks so much for the help!
    Reply With Quote Reply With Quote
  17. 11-15-2006, 10:31 AM #17
    Norie
    Norie is offline
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Ah, merged cells.

    Do you really need them?

    VBA and merged cells just don't get on together.
    Reply With Quote Reply With Quote
  18. 11-15-2006, 10:59 AM #18
    ragamuffin
    ragamuffin is offline
    VBAX Regular
    Joined
    Oct 2006
    Posts
    54
    Location
    The merged cells serve as a place for a title for the sheet, because it is intended to be printed out. The cells that are merged are A1:H2. They are not necessary, but nice to have. If it really gets in the way of the vba, then I could move the title to just a header (if that doesn't affect the vba...). But if there is a way to make the code work with the cells, that would be cool too.
    Reply With Quote Reply With Quote
  19. 11-15-2006, 02:06 PM #19
    lucas
    lucas is offline
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    There's always wordart
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln
    Reply With Quote Reply With Quote
  20. 11-15-2006, 02:28 PM #20
    Norie
    Norie is offline
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Or even Center Across Selection.

    Merged Cells
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules