I am new to VBA and I am having trouble with copying a specific row according to its first cell value, and paste it in another workbook into a sheet named as the same of this row.
Example:
The sheets on the another workbook are:
Entregas, Demandas, Cliente, Regulatório, Auditoria/Controle Interno, COP
I need to copy row 2 and paste non-empty columns (C, D, E, F, I, J, K and L) on the "Entregas" sheet in another workbook on the first empty row.
Do the same with row 3 with the columns C, D, E, F, I, J and K on the "Auditoria/Controle Interno" sheet on the first empty row, and so on...
The code i have is this, but it copies and pastes the entire row while I need it to paste just the non-empty cells.
Sub Botão2_Clique()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim copyFrom As Range
Dim lRow As Long '<~~ Not Integer. Might give you error in higher versions of excel
Dim strSearch As String
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Planilha1")
strSearch = "Entregas"
With ws1
'~~> Remove any filters
.AutoFilterMode = False
'~~> I am assuming that the names are in Col A
'~~> if not then change A below to whatever column letter
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
With .Range("A1:A" & lRow)
.AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
End With
'~~> Remove any filters
.AutoFilterMode = False
End With
'~~> Destination File
Set wb2 = Application.Workbooks.Open("\\BBAFSWCORP\dpt\DWS\SPLC\GerProc_Der_RF_RV\Renda Fixa\Equipe\Metas\Atividades_RF_2019.xlsm")
Set ws2 = wb2.Worksheets(strSearch)
With ws2
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lRow = 1
End If
copyFrom.Copy .Rows(lRow)
End With
wb2.Save
wb2.Close