Como refatorar código VBA para iterar em planilhas que aplicam um conjunto de operações de célula?

Question

Este é o meu código:

'copy cells
Sub start()
'variable for all worksheets in the workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim sh4 As Worksheet
Dim sh5 As Worksheet
Dim sh6 As Worksheet
Dim sh7 As Worksheet
Dim sh8 As Worksheet
Dim sh9 As Worksheet
Dim sh10 As Worksheet
Dim sh11 As Worksheet
Dim sh12 As Worksheet
Dim sh13 As Worksheet
Dim sh14 As Worksheet
Dim sh15 As Worksheet
Dim sh16 As Worksheet
Dim sh17 As Worksheet
Dim sh18 As Worksheet
Dim sh19 As Worksheet
Dim sh20 As Worksheet
Dim sh21 As Worksheet
Dim sh22 As Worksheet
Dim sh23 As Worksheet
Dim sh24 As Worksheet
Dim sh25 As Worksheet
Dim sh26 As Worksheet
Dim sh27 As Worksheet
Dim sh28 As Worksheet
Dim sh29 As Worksheet
Dim sh30 As Worksheet
Dim sh31 As Worksheet
Dim sh32 As Worksheet
Dim sh33 As Worksheet
Dim sh34 As Worksheet
Dim sh35 As Worksheet
Dim sh36 As Worksheet
Dim sh37 As Worksheet
Dim sh38 As Worksheet
Dim sh39 As Worksheet
Dim sh40 As Worksheet
'variable for the workbook itself
Dim wkb As Workbook
'set workbook
Set wkb = Workbooks("macro2.xlsm")
With wkb 'create with statement to call on worksheets

Set sh1 = .Sheets("1")
Set sh2 = .Sheets("2")
Set sh3 = .Sheets("3")
Set sh4 = .Sheets("4")
Set sh5 = .Sheets("5")
Set sh6 = .Sheets("6")
Set sh7 = .Sheets("7")
Set sh8 = .Sheets("8")
Set sh9 = .Sheets("9")
Set sh10 = .Sheets("10")
Set sh11 = .Sheets("11")
Set sh12 = .Sheets("12")
Set sh13 = .Sheets("13")
Set sh14 = .Sheets("14")
Set sh15 = .Sheets("15")
Set sh16 = .Sheets("16")
Set sh17 = .Sheets("17")
Set sh18 = .Sheets("18")
Set sh19 = .Sheets("19")
Set sh20 = .Sheets("20")
Set sh21 = .Sheets("21")
Set sh22 = .Sheets("22")
Set sh23 = .Sheets("23")
Set sh24 = .Sheets("24")
Set sh25 = .Sheets("25")
Set sh26 = .Sheets("26")
Set sh27 = .Sheets("27")
Set sh28 = .Sheets("28")
Set sh29 = .Sheets("29")
Set sh30 = .Sheets("30")
Set sh31 = .Sheets("31")
Set sh32 = .Sheets("32")
Set sh33 = .Sheets("33")
Set sh34 = .Sheets("34")
Set sh35 = .Sheets("35")
Set sh36 = .Sheets("36")
Set sh37 = .Sheets("37")
Set sh38 = .Sheets("38")
Set sh39 = .Sheets("39")
Set sh40 = .Sheets("40")
Set shsum = .Sheets("sum") 'name sheet to copy to
'add more if there are more sheets example
'set sh41 = .sheets("41")

'name first range to copy
sh1.Range("b8").copy shsum.Range("a2")
sh1.Range("b5").copy shsum.Range("b2")
sh1.Range("b4").copy shsum.Range("c2")
sh1.Range("G13:G30").copy shsum.Range("d2:d18")
sh1.Range("h13:h30").copy shsum.Range("e2:e18")
sh1.Range("i13:i30").copy shsum.Range("f2:f18")
sh1.Range("j13:j30").copy shsum.Range("g2:g18")
sh1.Range("k13:k30").copy shsum.Range("h2:h18")

End With
End Sub

Para obter os dados nas 40 folhas, eu precisaria inserir cada folha com as mesmas informações que você vê e isso é uma tonelada de digitação.

• Existe uma maneira de fazer algum tipo de array para as folhas?
• Eu preciso pular 18 linhas para baixo para cada folha adicional para que não haja sobreposição de dados na planilha sum . Isso ocorre porque g13 a k30 tem 18 linhas de dados que precisam ser preenchidas. B8, b5 e b4 são o nome do item ( X ), número ( Y ) e um número ( Z ) associado ao itens que ocupam de g13 a k30 em cada planilha. Preciso ter certeza de que os dados de cada planilha copiados na planilha sum contenham X , Y e Z itens na primeira linha, seguidos pelos itens associados com 18 linhas de comprimento.

Answer 1

Option Explicit

'copy cells
Sub start()
Dim ws As Worksheet, wsum As Worksheet
Dim wb As Workbook
Dim vws As Variant 'Need to use a Variant for iterator
Dim i As Integer, j As String, k As String

i = 0
Set wb = Workbooks("macro2.xlsm")
Set wsum = wb.Sheets("sum")

'Iterate through the sheets
For Each vws In wb.Sheets
If vws.Name <> "sum" Then 
j = CStr(i + 2)
k = CStr(i + 18)
vws.Range("b8").Copy wsum.Range("a" & j)
vws.Range("b5").Copy wsum.Range("b" & j)
vws.Range("b4").Copy wsum.Range("c" & j)
vws.Range("G13:G30").Copy wsum.Range("d" & j & ":d" & k)
vws.Range("h13:h30").Copy wsum.Range("e" & j & ":e" & k)
vws.Range("i13:i30").Copy wsum.Range("f" & j & ":f" & k)
vws.Range("j13:j30").Copy wsum.Range("g" & j & ":g" & k)
vws.Range("k13:k30").Copy wsum.Range("h" & j & ":h" & k)
i = i + 18
End If
Next
End Sub

Reorientado para usar loops e concatenação de strings. Apenas compile testado, então pode não funcionar; quaisquer defeitos são seus e se você estiver insatisfeito, reembolsarei o preço total da compra. Obrigado por comprar na McSoftware's.

Isso é muito básico (sem trocadilhos) e deve ser algo que você possa aprender. Alguns pontos explicativos para explicar como eu descobri isso:

• For Each vws In wb.Sheets itera em cada folha da pasta de trabalho. "Iterar" significa "percorrer um por um e manipular cada elemento separadamente em um loop".
• Se você precisar de mais de uma dúzia de variáveis em uma função, você está fazendo errado e deve parar imediatamente e tentar outra coisa. Fazer uma pergunta no SuperUser não é a melhor coisa que você poderia ter feito, mas é melhor do que copiar e colar seu código mais 48 vezes, então eu aplaudo pelo menos ao perceber que houve um problema.
• String concatenação significa pegar duas strings menores e juntá-las. Por exemplo, "ele" & "llo" renderia a string concatenada "olá". O operador & é usado para concatenar strings no Visual Basic.
• Você provavelmente pode reduzir ainda mais a repetitividade desse código adicionando um loop e criando uma matriz constante de letras para "d", "e", "f", "g", "h" - mas como havia Apenas cinco deles, o esforço de escrever um loop é quase o mesmo que fazer um por um. Se você acabar com muito mais colunas, definitivamente escreva um loop com uma matriz ou dicionário de letras.
• Eu usei conversão de números em strings com a função CStr() para converter o inteiro i mais uma constante derivada do seu código original. Portanto, na primeira iteração do loop, colocará vws.Range("b8") no intervalo da soma da soma A2 porque 0 + 2 = 2 . No final do loop, observe o i = i + 18 para incrementar o contador, para que, na segunda iteração, a mesma célula na segunda planilha seja copiada no intervalo A20 da planilha de soma. Você pode ter que ajustar os números porque eu não posso dizer a partir de seus dados de origem se isso vai substituir / atropelar acidentalmente as linhas ou não.

Referências

Antes de tentar algo mais exótico no Visual Basic (para Aplicativos), leia um livro sobre programação.

• Um livro muito básico que leva você passo a passo (provavelmente o que você precisa) aqui - Excel VBA in Easy Steps por Ed Robinson.
• Um oldie mas bom , VBA Developer's Handbook 2nd Edition de Ken Getz.

Você também pode obter uma perspectiva e um insight adicionais para ser um programador proficiente se tentar uma linguagem de programação mais moderna e genérica, como Python, Java ou C ++, apenas para aprender conceitos que ajudarão você a escrever um bom código em < em> qualquer idioma.