Hi guys. I have been facing an error 1004 on my code to copy and paste a particular range everytime a specific cell is changed.
all "uf" sheets are hidden, and it is important to stay this way. can you guys please gently help me?
ps.: this is the debug
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo tratamento
' Application.ScreenUpdating = False
Dim KeyCells As Range
Set KeyCells = Worksheets("PAINEL").Range("B4")
Dim uf As String
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
uf = Application.WorksheetFunction.VLookup(Range("D4").Value, _
Worksheets("AUXILIAR").Range("B1:C27"), 2, 0)
Debug.Print uf
' Sheets(uf).Activate
Dim i
i = 1
Do While Worksheets(uf).Cells(7, i) <> Worksheets("PAINEL").Range("B4").Value
i = i + 1
Loop
Debug.Print i
Dim dadosaimportar As Range
dadosaimportar = ActiveWorkbook.Sheets(uf).Range(Cells(8, i), Cells(74, i + 11))
dadosaimportar.Copy
Worksheets("PAINEL").Activate
Range("C13").Select
Selection.PasteSpecial xlPasteAll
Application.CutCopyMode = False
Range("D4") = Application.WorksheetFunction.VLookup(uf, Worksheets("AUXILIAR").Range("A1:B27"), 2, 0)
End If
tratamento:
Debug.Print Err.Number
Application.ScreenUpdating = True
End Sub
all "uf" sheets are hidden, and it is important to stay this way. can you guys please gently help me?
ps.: this is the debug
PA (this is ok)
75 (this is ok)
1004