Como criar uma macro sem mencionar o nome da Guia?

[WILKER.ADM]

Criei uma macro para ordenar por cor da célula e depois por ordem alfabética, porém, nela é mencionado o nome da guia; sendo que ao fazer uma copia da guia e renomea-la, puxa as referencias da guia anterior (da original e não a copia recém criada), no caso do exemplo 1 “fev13”. Atente que neste contesto, nesta guia recém-criada a macro não funcionará, pois ela se refere apenas a “fev13”.

Exemplo 1: Preciso remover o nome da guia desta macro, para que ao copia-la/renomea-la funcione na copia recém criada normalmente:

' OrdemAlfabeticaListaDizimistas Macro

'

'

Range("E129:F135").Select

ActiveWindow.SmallScroll Down:=-3

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Add(Range("E129:E135"), _

xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _

255, 255)

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Add Key:=Range("E129:E135" _

), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Fev13").Sort

.SetRange Range("E129:F135")

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range("E137:F144").Select

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Add(Range("E137:E144"), _

xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _

255, 255)

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Add Key:=Range("E137:E144" _

), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Fev13").Sort

.SetRange Range("E137:F144")

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

ActiveWindow.SmallScroll Down:=15

Range("E146:F151").Select

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Add(Range("E146:E151"), _

xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _

255, 255)

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Add Key:=Range("E146:E151" _

), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Fev13").Sort

.SetRange Range("E146:F151")

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

ActiveWindow.SmallScroll Down:=9

Range("E153:F238").Select

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Add(Range("E153:E238"), _

xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _

255, 255)

ActiveWorkbook.Worksheets("Fev13").Sort.SortFields.Add Key:=Range("E153:E238" _

), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Fev13").Sort

.SetRange Range("E153:F238")

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

ActiveWindow.SmallScroll Down:=118

ActiveWindow.SmallScroll ToRight:=3

Range("E124:F124").Select

ActiveWindow.ScrollRow = 116

ActiveWindow.ScrollRow = 115

ActiveWindow.ScrollRow = 113

ActiveWindow.ScrollRow = 111

ActiveWindow.ScrollRow = 109

ActiveWindow.ScrollRow = 106

ActiveWindow.ScrollRow = 104

ActiveWindow.ScrollRow = 100

ActiveWindow.ScrollRow = 97

ActiveWindow.ScrollRow = 94

ActiveWindow.ScrollRow = 90

ActiveWindow.ScrollRow = 86

ActiveWindow.ScrollRow = 83

ActiveWindow.ScrollRow = 81

ActiveWindow.ScrollRow = 78

ActiveWindow.ScrollRow = 76

ActiveWindow.ScrollRow = 73

ActiveWindow.ScrollRow = 70

ActiveWindow.ScrollRow = 67

ActiveWindow.ScrollRow = 65

ActiveWindow.ScrollRow = 62

ActiveWindow.ScrollRow = 59

ActiveWindow.ScrollRow = 57

ActiveWindow.ScrollRow = 54

ActiveWindow.ScrollRow = 52

ActiveWindow.ScrollRow = 48

ActiveWindow.ScrollRow = 45

ActiveWindow.ScrollRow = 42

ActiveWindow.ScrollRow = 39

ActiveWindow.ScrollRow = 36

ActiveWindow.ScrollRow = 34

ActiveWindow.ScrollRow = 32

ActiveWindow.ScrollRow = 30

ActiveWindow.ScrollRow = 27

ActiveWindow.ScrollRow = 26

ActiveWindow.ScrollRow = 24

ActiveWindow.ScrollRow = 22

ActiveWindow.ScrollRow = 20

ActiveWindow.ScrollRow = 19

ActiveWindow.ScrollRow = 17

ActiveWindow.ScrollRow = 16

ActiveWindow.ScrollRow = 14

ActiveWindow.ScrollRow = 13

ActiveWindow.ScrollRow = 12

ActiveWindow.ScrollRow = 11

ActiveWindow.ScrollRow = 10

ActiveWindow.ScrollRow = 9

ActiveWindow.ScrollRow = 8

ActiveWindow.ScrollRow = 7

ActiveWindow.ScrollRow = 6

ActiveWindow.ScrollRow = 5

ActiveWindow.ScrollRow = 4

ActiveWindow.ScrollRow = 2

ActiveWindow.ScrollRow = 1

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 1

Range("B19").Select

ActiveWindow.SmallScroll Down:=45

ActiveWindow.SmallScroll ToRight:=3

ActiveWindow.SmallScroll Down:=73

Range("E124:F124").Select

End Sub

Exemplo 2: Neste exemplo consegui ordenar também 4 intervalos por ordem alfabética sem citar o nome da guia, porém, não consegui ordena-los também por cor da célula como o exemplo anterior:

' ORDEM Macro

'

'

Range("F1:G3").Select

Selection.Sort Key1:=Range("F1"), Order1:=xlAscending, Header:=xlNo, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("F5:G7").Select

Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("F9:G11").Select

Selection.Sort Key1:=Range("F9"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("F13:G15").Select

Selection.Sort Key1:=Range("F13"), Order1:=xlAscending, Header:=xlNo, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("A1").Select

End Sub

Concluindo, preciso usar os critérios do exemplo 1, mas, sem citar o nome da guia como no exemplo 2!!!

[zinhovba]

Boa noite!!

Use o comando.

ActiveSheets

Leia:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=494

[WILKER.ADM]

Boa noite!!

Use o comando.

ActiveSheets

Leia:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=494

Boa noite.

Agradeço pelo seu tempo gasto em responder. porém meu conhecimento é bem limitado, e não consigo aplicar sua resposta em minha macro. Grato.

[zinhovba]

Boa noite!!

Mande um arquivo modelo, com bastante detalhe!!

Deposite o arquivo em site gratuito, depois nos mande o link.

Att

[WILKER.ADM]

Boa noite!!

Mande um arquivo modelo, com bastante detalhe!!

Deposite o arquivo em site gratuito, depois nos mande o link.

Att

Boa noite, zinhovba.

Agradeço por seu tempo, mas consegui com a ajuda do Sr. Eduardo (http://www.orkut.com.br/Main#CommMsgs?cmm=96432&tid=5843608631852372741). Mas se puder me ajudar com o meu outro topico, é um assunto relacionado. Obrigado. Vou postar a resposta:

Sub OrdemAlfabeticaListaDizimistas()

Dim XPlan As String

XPlan = ActiveSheet.Name

Range("E129:F135").Select

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Clear

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Add(Range("E129:E135"), _

xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _

255, 255)

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Add Key:=Range("E129:E135" _

), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets(XPlan).Sort

.SetRange Range("E129:F135")

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range("E137:F144").Select

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Clear

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Add(Range("E137:E144"), _

xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _

255, 255)

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Add Key:=Range("E137:E144" _

), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets(XPlan).Sort

.SetRange Range("E137:F144")

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range("E146:F151").Select

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Clear

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Add(Range("E146:E151"), _

xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _

255, 255)

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Add Key:=Range("E146:E151" _

), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets(XPlan).Sort

.SetRange Range("E146:F151")

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range("E153:F238").Select

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Clear

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Add(Range("E153:E238"), _

xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _

255, 255)

ActiveWorkbook.Worksheets(XPlan).Sort.SortFields.Add Key:=Range("E153:E238" _

), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets(XPlan).Sort

.SetRange Range("E153:F238")

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range("A1").Select

ActiveWindow.SmallScroll Down:=118

ActiveWindow.SmallScroll ToRight:=3

Range("E124:F124").Select

End Sub

[Patropi]

Caso o autor do tópico necessite, o mesmo será reaberto, para isso deverá entrar em contato com a moderação solicitando o desbloqueio.