nidenikolev
08-29-2018, 07:45 AM
I have a script that is supposed to combine two sheets (legal_ws) and (cib_ws) into (comp_ws).
Here's a step by step process of what I'm trying to achieve.
copy and paste (skipping header) legal_ws's cell A2-to-last row and last column of data into comp_ws (+1 row after header).
filter cib_ws to column 61's "regional presidents" and copy + paste only the visible (special cells) cells.
copy and paste those cells into comp_ws after the legal_ws data (so a +1 offset).
Here is my script so far, and it works fine up until the cib_ws selection. It transfers the legal_ws data just fine, it filters the cib_ws data just fine, but it doesn't select the visible cells and transfers it to the comp_ws after the last row of the legal data.
Script:
'File Paths
Dim Legal As String, CIB As String, Comp_TCR As String
Legal = "M:\Legal-TCR-Template.xlsx"
CIB = "M:\CIB-TCR-Template.xlsx"
Comp_TCR = "M:\Total_TCR.xlsx"
Dim legal_wb As Workbook, cib_wb As Workbook, comp_wb As Workbook
Set legal_wb = Workbooks.Open(Filename:=Legal)
Set cib_wb = Workbooks.Open(Filename:=CIB)
Set comp_wb = Workbooks.Open(Filename:=Comp_TCR)
Dim legal_ws As Worksheet, cib_ws As Worksheet, comp_ws As Worksheet
Set legal_ws = legal_wb.Sheets("ps")
Set cib_ws = cib_wb.Sheets("ps")
Set comp_ws = comp_wb.Sheets("Sheet1")
Dim lrow As Long, lcol As Long, lrow2 As Long, lcol2 As Long, lrow3 As Long
Dim legal_rng As Range, cib_rng As Range
lrow = legal_ws.Range("A" & Rows.Count).End(xlUp).Row
lcol = legal_ws.Cells(1, legal_ws.Columns.Count).End(xlToLeft).Column
lrow2 = cib_ws.Range("A" & Rows.Count).End(xlUp).Row
lcol2 = cib_ws.Cells(1, cib_ws.Columns.Count).End(xlToLeft).Column
lrow3 = comp_ws.Range("A" & Rows.Count).End(xlUp).Row
Set legal_rng = legal_ws.Range(legal_ws.Cells(2, 1), legal_ws.Cells(lrow, lcol))
legal_rng.Copy Destination:=comp_ws.Range("A2")
With cib_ws.Range("A1" & lcol2)
.AutoFilter
.AutoFilter Field:=61, Criteria1:="Regional Presidents"
End With
Set cib_rng = cib_ws.Range(cib_ws.Cells(2, 1), cib_ws.Cells(lrow, lcol)).SpecialCells(xlcellvisible)
cib_rng.specialcells.copy destination:=comp_ws.range("A" & lrow3 +1)
End Sub
Here's a step by step process of what I'm trying to achieve.
copy and paste (skipping header) legal_ws's cell A2-to-last row and last column of data into comp_ws (+1 row after header).
filter cib_ws to column 61's "regional presidents" and copy + paste only the visible (special cells) cells.
copy and paste those cells into comp_ws after the legal_ws data (so a +1 offset).
Here is my script so far, and it works fine up until the cib_ws selection. It transfers the legal_ws data just fine, it filters the cib_ws data just fine, but it doesn't select the visible cells and transfers it to the comp_ws after the last row of the legal data.
Script:
'File Paths
Dim Legal As String, CIB As String, Comp_TCR As String
Legal = "M:\Legal-TCR-Template.xlsx"
CIB = "M:\CIB-TCR-Template.xlsx"
Comp_TCR = "M:\Total_TCR.xlsx"
Dim legal_wb As Workbook, cib_wb As Workbook, comp_wb As Workbook
Set legal_wb = Workbooks.Open(Filename:=Legal)
Set cib_wb = Workbooks.Open(Filename:=CIB)
Set comp_wb = Workbooks.Open(Filename:=Comp_TCR)
Dim legal_ws As Worksheet, cib_ws As Worksheet, comp_ws As Worksheet
Set legal_ws = legal_wb.Sheets("ps")
Set cib_ws = cib_wb.Sheets("ps")
Set comp_ws = comp_wb.Sheets("Sheet1")
Dim lrow As Long, lcol As Long, lrow2 As Long, lcol2 As Long, lrow3 As Long
Dim legal_rng As Range, cib_rng As Range
lrow = legal_ws.Range("A" & Rows.Count).End(xlUp).Row
lcol = legal_ws.Cells(1, legal_ws.Columns.Count).End(xlToLeft).Column
lrow2 = cib_ws.Range("A" & Rows.Count).End(xlUp).Row
lcol2 = cib_ws.Cells(1, cib_ws.Columns.Count).End(xlToLeft).Column
lrow3 = comp_ws.Range("A" & Rows.Count).End(xlUp).Row
Set legal_rng = legal_ws.Range(legal_ws.Cells(2, 1), legal_ws.Cells(lrow, lcol))
legal_rng.Copy Destination:=comp_ws.Range("A2")
With cib_ws.Range("A1" & lcol2)
.AutoFilter
.AutoFilter Field:=61, Criteria1:="Regional Presidents"
End With
Set cib_rng = cib_ws.Range(cib_ws.Cells(2, 1), cib_ws.Cells(lrow, lcol)).SpecialCells(xlcellvisible)
cib_rng.specialcells.copy destination:=comp_ws.range("A" & lrow3 +1)
End Sub