Macro para criar planilha e transferir dados excel

[mauro.lanagro]

Ja andei lendo alguns tópicos neste forum mas não consegui algo semelhante ao que preciso. Tenho uma planilha de cálculo de dados com algumas formulas automatizadas e toda vez que tenho um relatório de resultados, tenho que jogar os dados de cada uma das colunas desta planilha relatório em uma copia da minha planilha de cálculo renomeada com o nome da coluna.

Exemplo, se a tabela de resultados tiver 250 colunas (de coluna B:IZ), tenho que criar 250 copias da minha planilha de cálculo renomear como os nomes no cabeçalho de cada uma das 250 colunas e transferir os dados das linhas 2a53 da coluna na respectiva planilha de cálculo correspondente, entrada de dados (plan1) e campos C2:C53.

Alguém tem uma ideia de como fazer isso e pode me ajudar. um código visual basic que criasse as planilhas com base nos cabeçalhos das 250 colunas e transferir as colunas uma a uma nos respectivos arquivos. Isso leva um tempo enorme e ha muito tempo tento criar esta macro no VB. Peço ajuda pois tenho pouca experiência em visual basic. Alguns indicaram usar o power pivot, mas também não consegui. Estou acostumado com macros VB pequenas. não sei se dá para fazer tudo em uma unica macro. Aguardo! Obrigado a que se dispor ajudar!

[Wendell Menezes]

Sim, dá pra fazer, mas poste uma cópia da sua planilha de cálculo já com dados para facilitar a nossa vida.

[mauro.lanagro]

Posso sim. obrigado pela ajuda. Na planilha relatorio as colunas transportadas são a partir da coluna C e todas as linhas a partir da linha 2. Na planilha de cálculo os dados entram no primeiro dia células E10:E63 (1dia).

 

planilha_de_calculos.xls

planilha_relatorio.xlsx

[Wendell Menezes]

Obrigado, agora pode explicar passo a passo o que a macro deve fazer? A Planilha relatório possui muito mais colunas do que a planilha de cálculo possui de dias. Explique mais ou menos assim:

 

1. Copiar C2:C55 da planilha de relatório e colar em E10 da aba "ENTRADA DE DADOS" da planilha de cálculos.

2. Copiar...

3. Copiar...

4. Salvar a planilha "x" com o nome da coluna "y" na pasta "z" do meu computador.

[mauro.lanagro]

1- salvar a planilha de cálculo com o nome da coluna C (C1) da planilha relatório

2- transferir os dados da coluna C (C2:C55) da planilha de relatório e colar em E10 da aba "ENTRADA DE DADOS" da planilha de cálculos criada (correspondente ao nome em C1)

3- salvar a planilha de cálculo com o nome da coluna D (D1) da planilha relatório

4- transferir os dados da coluna DC (D2:D55) da planilha de relatório e colar em E10 da aba "ENTRADA DE DADOS" da planilha de cálculos criada (correspondente ao nome em D1)

obs. acho que seria um loop para ir salvando a planilha de cálculo com o nome das colunas e tranferindo os dados.

o numero de colunas a tranferir varia de 100 a 300. Pensei em criar uma lista com o nome das colunas, salvar todas as planilhas de cálculo primeiro e so depois tranferir os dados em um loop. O que você acha?

Se cria a lista de nomes, faz-se um loop de salvamento de planilhas. Aí a planilha salva na pasta, roda um loop para tranferir os dados.

 

[Wendell Menezes]

1. Coloque o código abaixo na planilha_relatorio.xls

2. Ajuste os dois caminhos conforme a sua necessidade, não se esqueça da "\" no final do caminho da pasta.

3. Rode a marco e espere até que uma mensagem avise que o código terminou de rodar, vai levar entre 1-2 minutos.

4. Reconcilie alguns arquivos para confirmar que é isso q você precisa.

Sub Split()

Dim Folder As String
Dim Template As Workbook
Dim LC As Integer, c As Integer

Folder = "C:\Users\wende\Desktop\Relatórios\" 'Coloque o caminho da pasta onde os arquivos serão salvos
Set Template = Workbooks.Open("C:\Users\wende\Desktop\planilha_de_calculos.xls") ' Coloque o caminho da planilha de cálculos em branco
LC = Cells(1, Columns.Count).End(xlToLeft).Column

Application.ScreenUpdating = False
Application.DisplayAlerts = False
    For c = 3 To LC
        ThisWorkbook.Sheets(1).Range(Cells(2, c), Cells(55, c)).Copy Template.Sheets(1).Range("E10")
        Template.SaveAs Folder & ThisWorkbook.Sheets(1).Cells(1, c) & ".xlsx", 51
    Next
Template.Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox LC & " arquivos criados em:" & vbLf & Folder, vbInformation, "# Informação"

End Sub

 

[mauro.lanagro]

Wendel, obrigado pelo script! Ficou maravilhoso!

Você viu a planilha, tenho que transferir os dados dia a dia. Tentei mudar o script que criou para transferir os outros dias sem criar a planilha novamente e não consegui.

Seu script ficou melhor do que eu pensava, ele cria o arquivo, transfere os dados e depois salva. Sei que na parte do loop ele transfere os dados e se eu quiser transferir o segundo dia tenho que mudar no loop de E10 para K10.

Sei que já estou abusando, mas teria como criar uma adaptação que não cria-se nova planilha e sim somente transfere os dados para as planilhas já criadas na linha K10. Para os demais dias eu adaptaria este segundo script.

Muito obrigado pela ajuda.

 

 

 

[Wendell Menezes]

Sub Update()

Dim Folder As String
Dim Template As Workbook
Dim LC As Integer, c As Integer

Folder = "C:\Users\wende\Desktop\Relatórios\" 'Coloque o caminho da pasta onde os arquivos serão salvos
LC = Cells(1, Columns.Count).End(xlToLeft).Column

Application.ScreenUpdating = False
Application.DisplayAlerts = False
    For c = 3 To LC
        Set Template = Workbooks.Open(Folder & ThisWorkbook.Sheets(1).Cells(1, c) & ".xlsx")
        ThisWorkbook.Sheets(1).Range(Cells(2, c), Cells(55, c)).Copy Template.Sheets(1).Range("K10")
        Template.Close True
    Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox LC & " arquivos modificados em:" & vbLf & Folder, vbInformation, "# Informação"

End Sub

 

[mauro.lanagro]

Wendell, bom dia!

Obrigado de novo! Olha apareceu um erro de execução 1004 "erro de definição do aplicativo ou definição do objeto"

Eu troquei o caminho para o caminho onde estão as planilhas como sugeriu. Apareceu este erro aí no seu teste também? Será que alguma coisa que fiz errado?

[mauro.lanagro]

Consegui, coloquei a definição do template dentro do loop e deu certo. Gostaria de agradecer imensamente ao Wendell e a equipe do forum aqui do Clube do Hardware pela ajuda.  Deu tudo certo, Obrigado!

[mauro.lanagro]

Ace_1.xlsx

Ace_2.xlsx

Ace_3.xlsx

adicionado 13 minutos depois

Preciso criar um relatório final exportando a linha 4 da planilha de calculos para uma planilha relatório. pensei em Criar um novo arquivo Excel, colocar o cabeçalho igual ao da planilha "1" do arquivo planilha de calculos. Em seguida colocar na ordem os nomes de cada planilha que foi criada com o script "sub split()". 

tentei o script abaixo:

Sub Update_relatorio()

Dim Folder As String
Dim LC As Integer, c As Integer

Folder = "C:\validacao\" 'Coloque o caminho da pasta onde os arquivos serão salvos
LC = Cells(Rows.Count, 1).End(xlDown)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    For c = 3 To LC
        Dim Template As Workbook
        Set Template = Workbooks.Open(Folder & ThisWorkbook.Sheets(1).Cells(1, c) & ".xlsx")
        Template.Sheets(9).Range("C4:BH4").Copy ThisWorkbook.Sheets(1).Range(Cells(c, 2), Cells(c, 60))
        Template.Close True
    Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox LC & " arquivos modificados em:" & vbLf & Folder, vbInformation, "# Informação"
 

mas parece que tem erros que não da para finalizar. o modelo de relatório esta en anexo.

Relatorio_fim.xlsx