Criar macro ou formula para transferir dados de uma aba para outra

Osmarbg · 12 de setembro de 2014

Vou tentar explicar aqui e colocarei o modelo da planilha anexo para melhor entendimento.

Ao Abrir a planilha a primeira aba são os lançamentos bancários entrada e saída.

As outras 4 abas representam as filiais de uma Empresa.

Quando uma despesa é lançada a coluna G indica de qual filial foi a despesa.

O que eu preciso é que de forma automática toda vez que eu colocar o nome de determinada filial na coluna G as linhas das colunas abaixo crie uma cópia na aba correspondente a filial digitada.Meu office é o 2010.

Data

Discrição (nome do credor)

Histórico

Valor

NF N°

EMISSÃO

VENC.

OBS.

Modelo.xlsx

12 de setembro de 2014

O que eu preciso é que de forma automática toda vez que eu colocar o nome de determinada filial na coluna G as linhas das colunas abaixo crie uma cópia na aba correspondente a filial digitada.

O seu texto acima está meio difícil de entender, mas veja se isto te ajuda.

Preparação das planilhas:

1. na planilha Itaú, na coluna "G", coloque os nomes corretos/completos das demais planilhas (sugiro que você elabore em qualquer parte da planilha uma lista com os nomes das planilhas das filiais e utilize essa lista para Validação de Dados na coluna "G", isso evitará erros ao rodar o código, caso algum nome não seja digitado corretamente)

2. nas planilhas das filiais mude a palavra "TOTAL" que está na coluna "A" após a linha 170, para a coluna "B", mesma linha

Depois, instale o código abaixo no módulo da planilha "Itaú - Alimenta", assim:

1. copie o código daqui

2. clique com o direito na guia da planilha e escolha 'Exibir código'

3. cole o código na janela em branco que vai se abrir

4. feito! 'Alt+Q' para retornar para a planilha e testar

Private Sub Worksheet_Change(ByVal Target As Range) Dim LR As Long  If Target.Count > 1 Then Exit Sub  If Target.Column > 11 Or Application.CountA(Cells(Target.Row, 1).Resize(, 4), _   Cells(Target.Row, 7), Cells(Target.Row, 10), Cells(Target.Row, 11)) < 7 Then Exit Sub    With Sheets(Cells(Target.Row, 7).Value)     LR = .Cells(Rows.Count, 1).End(xlUp).Row     .Cells(LR + 1, 1).Resize(, 4).Value = Cells(Target.Row, 1).Resize(, 4).Value     .Cells(LR + 1, 5).Resize(, 4).Value = Cells(Target.Row, 8).Resize(, 4).Value    End WithEnd Sub

funcionamento - na planilha "Itaú", após preencher as colunas "A:D", "G", "J:K", na mesma linha, em qualquer ordem,o registro correspondente será lançado pelo código na planilha cujo nome estiver na coluna "G". É isso que você precisa?

Osmarbg · 12 de setembro de 2014

O seu texto acima está meio difícil de entender, mas veja se isto te ajuda.

Preparação das planilhas:

1. na planilha Itaú, na coluna "G", coloque os nomes corretos/completos das demais planilhas (sugiro que você elabore em qualquer parte da planilha uma lista com os nomes das planilhas das filiais e utilize essa lista para Validação de Dados na coluna "G", isso evitará erros ao rodar o código, caso algum nome não seja digitado corretamente)

2. nas planilhas das filiais mude a palavra "TOTAL" que está na coluna "A" após a linha 170, para a coluna "B", mesma linha

Depois, instale o código abaixo no módulo da planilha "Itaú - Alimenta", assim:

1. copie o código daqui

2. clique com o direito na guia da planilha e escolha 'Exibir código'

3. cole o código na janela em branco que vai se abrir

4. feito! 'Alt+Q' para retornar para a planilha e testar
Private Sub Worksheet_Change(ByVal Target As Range) Dim LR As Long  If Target.Count > 1 Then Exit Sub  If Target.Column > 11 Or Application.CountA(Cells(Target.Row, 1).Resize(, 4), _   Cells(Target.Row, 7), Cells(Target.Row, 10), Cells(Target.Row, 11)) < 7 Then Exit Sub    With Sheets(Cells(Target.Row, 7).Value)     LR = .Cells(Rows.Count, 1).End(xlUp).Row     .Cells(LR + 1, 1).Resize(, 4).Value = Cells(Target.Row, 1).Resize(, 4).Value     .Cells(LR + 1, 5).Resize(, 4).Value = Cells(Target.Row, 8).Resize(, 4).Value    End WithEnd Sub
funcionamento - na planilha "Itaú", após preencher as colunas "A:D", "G", "J:K", na mesma linha, em qualquer ordem,o registro correspondente será lançado pelo código na planilha cujo nome estiver na coluna "G". É isso que você precisa?

Olá amigo, acho que escrevendo aqui eu não estou conseguindo me fazer entender, se possível pode me ligar

ou se puder mande o seu telefone e eu explico melhor.

Grato.

13 de setembro de 2014

Olá, amigo.

Você não informou se testou a sugestão que passei.

Disponibilize novamente o seu arquivo e descreva nas próprias planilhas o que você deseja e coloque os resultados desejados.

Sugestão - remova do fórum o número do seu telefone.

Osmarbg · 13 de setembro de 2014

Olá Osvaldo,

Obrigado pela dica, já removi o Tel. eu estou neste momento trabalhando na palnilha, eu testei e deu certo sim, muito obrigado isto me ajudou muito. a planilha que passei era apenas teste na realidade i número de filiais ara bem maior eu fui aumentando as abas e deu certo exeto uma que aparece o seguinte erro - erro em tempo de execução 9 subscrito fora do intervalo - mas é apenas uma filial todos as outras deu certo, o que pode ser?

13 de setembro de 2014

... exceto uma que aparece o seguinte erro - erro em tempo de execução 9 subscrito fora do intervalo - mas é apenas uma filial todos as outras deu certo, o que pode ser?

Olá, Osmar.

A causa provável do erro é o nome da planilha escrito incorretamente na coluna "G" da planilha "Itaú" (veja o primeiro item do post #2).

Osmarbg · 13 de setembro de 2014

Olá, Osmar.

A causa provável do erro é o nome da planilha escrito incorretamente na coluna "G" da planilha "Itaú" (veja o primeiro item do post #2).

Então Osvaldo, eu sou bem leigo estou conseguindo mas começando do zero de novo ou seja eu apaguei a planilha que estava dando erro e fiz outra ai deu certo. mas ainda tem uma coisa que não consegui fazer, se puder explicar mais detalhado acho que consigo, é a parte que você disse para eu criar uma lista com os nomes das planilhas das filiais e utilize essa lista para Validação de Dados na coluna "G", isso evitará erros ao rodar o código, caso algum nome não seja digitado corretamente), como faço esta lista?

Patropi · 13 de setembro de 2014

Osmar

Para fazer a lista de validação:

1º Em um coluna --> crie uma lista com os nomes das planilhas ( Use por exemplo a coluna X da planilha Itau;

2º Selecione o intervalo que você usa da coluna G, ( no teu exemplo seria de G10 a G748);

3º Guia dados --> Validação de dados --> Em permitir --> selecione Lista;

4º Em fonte digite o intervalo da Lista ( por exemplo) =$X$1:$X$10 --> OK e está pronto.

Dê retorno

[]s

13 de setembro de 2014

Seguem 2 opções para implantar a Validação.

1. com intervalo fixo - para facilitar e evitar erros de digitação na elaboração da lista com os nomes das planilhas, você pode rodar o código abaixo para formar a lista na coluna "X", e depois seguir as dicas do Patropi para implantar a Validação.

O código irá listar inclusive as planilhas ocultas do seu arquivo.

Se depois de implantar a Validação você incluir ou excluir planilhas, vai precisar rodar o código novamente para atualizar a lista e, se necessário, alterar o intervalo ocupado pela lista na fórmula empregada na Validação. No exemplo do Patropi "X1:X10".
Rode o código também após renomear alguma planilha, com o fim de atualizar a lista. Neste caso não precisará alterar o intervalo na Validação.

Instale o código abaixo em um módulo comum, assim:
1. copie o código daqui
2. a partir da planilha em que estão os dados tecle 'Alt+F11' para acessar o editor de VBA
3. no menu do editor >> Inserir >> Módulo
4. cole o código na janela em branco que vai se abrir
5. feito! 'Alt+Q' para retornar para a planilha e testar

para rodar o código:
6. tecle 'Alt+F8' >> selecione a macro correspondente >> Executar, ou insira um botão na planilha e vincule-o à macro ou vincule-a a um atalho de teclado (Alt+F8 > Opções).

Sub ListaPlans() Dim ws As Worksheet, i As Long  With Sheets("Itaú - Alimenta")   [X:X].ClearContents    For Each ws In ActiveWorkbook.Worksheets     If ws.Name <> "Itaú - Alimenta" Then Cells(i + 1, "X") = ws.Name: i = i + 1    Next ws  End WithEnd Sub

2. com intervalo dinâmico - forme a lista rodando o código acima, porém utilize intervalo dinâmico nomeado para a Validação. A diferença do método anterior é que você não precisará alterar o intervalo na Validação em caso de inclusão/exclusão de planilhas. O Mestre Patropi é craque nisso e poderá nos orientar para nomear o intervalo dinâmico e utilizar o nome na Validação.

Também neste método será necessário rodar o código após renomear alguma planilha.

obs. corrija o nome da planilha "Itaú" pois há um espaço após o nome e isso vai travar o código: duplo clique na guia da planilha > aperte End > aperte Backspace para eliminar o espaço >> Enter.

sugestão - a menos que seja necessário, para responder clique em Responder e não em Citar