PDA

View Full Version : Selecting Sheets in VBA



ragamuffin
11-11-2006, 03:06 PM
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

Norie
11-11-2006, 03:21 PM
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.

ragamuffin
11-11-2006, 03:48 PM
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.



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

Thanks again!

ragamuffin
11-11-2006, 03:52 PM
Also, now that you see the code, if you happen to know of some ways to clean it up, please let me know.

Thanks!

lucas
11-11-2006, 03:57 PM
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.

ragamuffin
11-11-2006, 04:09 PM
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!

ragamuffin
11-14-2006, 03:31 PM
I tried to clean up my code a bit and reorganize it. I hope this is a little bit easier to read.

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

Thanks again for any and all help.

mdmackillop
11-14-2006, 04:03 PM
Is the spacing between pasted data for specific cells or just to create some approximate spacing.

mdmackillop
11-14-2006, 04:14 PM
The spacing may not be correct, especia;;y if you're pasting to fit other data but try

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

ragamuffin
11-14-2006, 04:24 PM
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!!!

mdmackillop
11-14-2006, 04:35 PM
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

Sheets(sh).Range("G47").Copy Destination:=Sheets("ESN Summary").Cells(i, 2)


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.

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

ragamuffin
11-14-2006, 04:54 PM
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:

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

Is that correct?

mdmackillop
11-14-2006, 04:58 PM
Apologies, I was misreading your code
Try

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

ragamuffin
11-15-2006, 07:59 AM
Ok, I tried the revised code which looks like this when I add it into my sheet:

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

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:

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

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

Any ideas?

Norie
11-15-2006, 09:53 AM
What error are you getting?

ragamuffin
11-15-2006, 10:22 AM
"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:

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

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!

Norie
11-15-2006, 10:31 AM
Ah, merged cells.:devil:

Do you really need them?

VBA and merged cells just don't get on together.

ragamuffin
11-15-2006, 10:59 AM
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.

lucas
11-15-2006, 02:06 PM
There's always wordart

Norie
11-15-2006, 02:28 PM
Or even Center Across Selection.

:fighting:Merged Cells:viking:

ragamuffin
11-15-2006, 03:44 PM
I tried the Center Across Selection option and re-ran the macro. The macro ran completely, but it did not copy everything as it should - some rows were copied that should not have been, but other rows were not copied that should have been...?

The original vba that I recorded works, it just takes up a lot of space and is not a very clean look - but it works, so...The one thing that I need is to be able to change the name of the sheets in the code from including the worksheet name (ex. "Erika") to the vba a code name (ex. "Sheet3"). When I type Sheet3.Select it does not work and I get a code error:

"Run-time error '424' Object required"

I am really not sure what this means, so I do not know how to fix it.

I would like to clean the whole code up eventually, but the sheet name thing has to be done first (that part is kind of out my hands - I'd love to have the code cleaned up first, but because of my job it'll have to wait). I really appreciate everyone's help in trying to clean up the code and make it more effecient! You have all been great! If anybody is still willing to help me with the sheet name part, I would be GREATLY appreciative.

Thanks again!

ragamuffin
11-15-2006, 03:45 PM
I tried the Center Across Selection option and re-ran the macro. The macro ran completely, but it did not copy everything as it should - some rows were copied that should not have been, but other rows were not copied that should have been...?

The original vba that I recorded works, it just takes up a lot of space and is not a very clean look - but it works, so...The one thing that I need is to be able to change the name of the sheets in the code from including the worksheet name (ex. "Erika") to the vba a code name (ex. "Sheet3"). When I type Sheet3.Select it does not work and I get a code error:

"Run-time error '424' Object required"

I am really not sure what this means, so I do not know how to fix it.

I would like to clean the whole code up eventually, but the sheet name thing has to be done first (that part is kind of out my hands - I'd love to have the code cleaned up first, but because of my job it'll have to wait). I really appreciate everyone's help in trying to clean up the code and make it more effecient! You have all been great! If anybody is still willing to help me with the sheet name part, I would be GREATLY appreciative.

Thanks again!

mdmackillop
11-15-2006, 04:35 PM
A1:H2 are not merged, as far as I can see, in the sample you posted.
Regards
MD

mdmackillop
11-16-2006, 01:41 AM
If you posted your sample with data, we could test it properly.
"Sheet3" is a name, as is "Erika", and needs to be used in the form Sheets("Sheet3"). If you don't want to use the sheet name, you can use the sheet index as in Sheets(3), or as in my code Sheets(j).

ragamuffin
11-22-2006, 08:11 AM
Sorry about the delayed response, I've been terribly busy at work and took a couple of days off.

I've posted an example of my workbook with some information populated on all of the rep sheets. If you click on the smilie face button at the top, the transfer should run so that all the information from all of the sheets is copied and organized on the ESN Summary sheet. Clicking on the frowning face will clear out the ESN Summary sheet and reset it.

The VBA still utilizes sheet names, e.g. Sheets("Rep 1").Select, but I cannot get the code to work with the index names. That is my ultimate goal, because I need the code to work if the sheet names change (which they will).

Thanks again for you help!

mdmackillop
11-22-2006, 01:22 PM
There's no code in module1, or buttons.

ragamuffin
11-22-2006, 03:53 PM
My apologies, I edited the macro in Personal.xls. Here is the correct version. Also, I am not sure why the buttons would not appear, but Ctrl + Shft + T should run the macro. If that does not work, then you can probably just run it normally from selecting it from the menu.

mdmackillop
11-22-2006, 04:32 PM
This should copy your data and put the sheet names in Column A where there is a value in Column B

Sub ESN_Summary()
'
' ESN_Summary Macro
' Macro recorded 10/30/2006 by pos1
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Dim Tgt As Range, Rng As Range
ActiveSheet.Unprotect
For i = 2 To Sheets.Count
With Sheets("ESN Summary")
Set Tgt = .Cells(4 + (i - 2) * 47, 2)
Sheets(i).Range("A2:G47").Copy Tgt
Set Rng = Tgt.Resize(46).SpecialCells(xlCellTypeConstants)
Rng.Offset(, -1).Formula = Sheets(i).Name
End With
Next
Sheets("ESN Summary").Activate
Range("A3:H3000").AutoFilter Field:=1, Criteria1:="<>"
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
End Sub

ragamuffin
11-22-2006, 05:25 PM
I can't seem to get it to run. Ctrl + Shft + T does not work and I cannot link it to a button. What can I do to try your code?