0
Hello, the situation that I am trying to automate in excel, through VBA is the following: I have two spreadsheets (different excel files), where the two have the same header (with information like ID, Name, Description...), but with columns in different order (in sheet 1, Name is column C, and in sheet 2, Name is column F, for example). In one of these worksheets, the content is filled and in the other there is only the header, so I created a VBA button in the worksheet where there is only the header, so that it looks for the information corresponding to each column of the header and imports the data (copy and paste)automatically. The code I created is the following, and this giving error, referring to object :
Sub Botão4_Clique()
Dim contador, col As Integer
Dim valor, PastaAtual, NomeDoArquivo, NomeCompletoDoArquivo As String
Dim Busca As Range
contador = 0
col = 1
ThisWorkbook.Worksheets("Plan1").Activate
Do While Cells(1, col).Value <> ""
Cells(1, col).Select
valor = Cells(1, col).Value
PastaAtual = Application.ActiveWorkbook.Path
NomeDoArquivo = "teste.xlsx"
NomeCompletoDoArquivo = PastaAtual + "\" + NomeDoArquivo
Workbooks.Open (NomeCompletoDoArquivo)
Set Busca = Cells.Find(What:=valor, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ThisWorkbook.Worksheets("Plan1").Activate
Cells(1, col).Activate
Set tbl = ActiveCell
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Select
Selection.Copy
Workbooks(NomeCompletoDoArquivo).Select
ActiveSheet.Paste
Application.CutCopyMode = False
contador = contador + 1
col = col + 1
Loop
MsgBox contador
End Sub
What could be wrong? Thank you
– user65951
"[...] and is giving error, referring to object". In which line?
– Luiz Vieira
In the "Find" command, in Set Search = Cells.Find(What:=value, ...
– user65951