- 11 Mar 2022 às 09:27
#69574
Oi pessoal,
Com alguma ajuda e pesquisa consegui criar esta macro, o que ela faz é ir buscar dados em uma planilha que vem de ser aberta.
Copia e fecha essa mesma planilha, o que preciso é que assim que fecha os proximos dados sejam copiados na seguinte linha.
ou sera preciso criar um loop?
podem ajudar?
Sub CopyOutput()
Dim wb1 As Workbook, wb2 As Workbook
Dim Ret1, Ret2
Dim linha As Double, coluna As Double
Set wb1 = ActiveWorkbook
'~~> Clear CopyData tab
'~~> Get the File
Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "Please select file")
If Ret1 = False Then Exit Sub
Set wb2 = Workbooks.Open(Ret1)
If wb2.Sheets(1).Range("g7") <> "" Then
wb2.Sheets(1).Range("G6").Copy Destination:=wb1.Worksheets(1).Range("A4").Cells
wb2.Sheets(1).Range("s10").Copy Destination:=wb1.Worksheets(1).Range("b4").Cells
wb2.Sheets("final flight report").Range("i36").Copy Destination:=wb1.Worksheets(1).Range("c4").Cells
wb2.Sheets(1).Range("e41").Copy Destination:=wb1.Worksheets(1).Range("d4").Cells
wb2.Sheets("final flight report").Range("i66").Copy Destination:=wb1.Worksheets(1).Range("e4").Cells
wb2.Sheets(1).Range("e43").Copy Destination:=wb1.Worksheets(1).Range("h4").Cells
wb2.Sheets("final flight report").Range("j72").Copy Destination:=wb1.Worksheets(1).Range("m4").Cells
'wb2.Sheets(1).Range("D1:D13").Copy Destination:=wb1.Worksheets("Control").Range("B12:B27").Cells
wb2.Close SaveChanges:=False
Else
wb2.Close SaveChanges:=False
MsgBox "No data.", vbExclamation, "CIS"
End If
Set wb2 = Nothing
Set wb1 = Nothing
End Sub
Com alguma ajuda e pesquisa consegui criar esta macro, o que ela faz é ir buscar dados em uma planilha que vem de ser aberta.
Copia e fecha essa mesma planilha, o que preciso é que assim que fecha os proximos dados sejam copiados na seguinte linha.
ou sera preciso criar um loop?
podem ajudar?
Sub CopyOutput()
Dim wb1 As Workbook, wb2 As Workbook
Dim Ret1, Ret2
Dim linha As Double, coluna As Double
Set wb1 = ActiveWorkbook
'~~> Clear CopyData tab
'~~> Get the File
Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "Please select file")
If Ret1 = False Then Exit Sub
Set wb2 = Workbooks.Open(Ret1)
If wb2.Sheets(1).Range("g7") <> "" Then
wb2.Sheets(1).Range("G6").Copy Destination:=wb1.Worksheets(1).Range("A4").Cells
wb2.Sheets(1).Range("s10").Copy Destination:=wb1.Worksheets(1).Range("b4").Cells
wb2.Sheets("final flight report").Range("i36").Copy Destination:=wb1.Worksheets(1).Range("c4").Cells
wb2.Sheets(1).Range("e41").Copy Destination:=wb1.Worksheets(1).Range("d4").Cells
wb2.Sheets("final flight report").Range("i66").Copy Destination:=wb1.Worksheets(1).Range("e4").Cells
wb2.Sheets(1).Range("e43").Copy Destination:=wb1.Worksheets(1).Range("h4").Cells
wb2.Sheets("final flight report").Range("j72").Copy Destination:=wb1.Worksheets(1).Range("m4").Cells
'wb2.Sheets(1).Range("D1:D13").Copy Destination:=wb1.Worksheets("Control").Range("B12:B27").Cells
wb2.Close SaveChanges:=False
Else
wb2.Close SaveChanges:=False
MsgBox "No data.", vbExclamation, "CIS"
End If
Set wb2 = Nothing
Set wb1 = Nothing
End Sub