How can I get my second range to transfer as an offset?

[nidenikolev]

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

[p45cal]

try:
'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

Set legal_rng = legal_ws.Range(legal_ws.Cells(2, 1), legal_ws.Cells(lrow, lcol))
legal_rng.Copy Destination:=comp_ws.Range("A2")
lrow3 = comp_ws.Range("A" & Rows.Count).End(xlUp).Row 'this line moved down to after comp_ws has had data added to it.

'With cib_ws.Range("A1" & lcol2) ' this is not quite right.
With cib_ws.Range(cib_ws.Range("A1"), cib_ws.Cells(lrow2, lcol2)) ' this might be better, if not try:
'With cib_ws.Range("A1:A" & 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)
Set cib_rng = cib_ws.Range(cib_ws.Cells(2, 1), cib_ws.Cells(lrow2, lcol2)).SpecialCells(xlcellvisible) 'change from line above

cib_rng.SpecialCells.Copy Destination:=comp_ws.Range("A" & lrow3 + 1)

[nidenikolev]

It says there's a syntax error on this line:


cib_rng.SpecialCells(xlCellTypeVisible.Copy Destination:=comp_ws.Range("A" & lrow3 + 1)

[nidenikolev]

nevermind****

YOU DID IT!!!!!!

I CAN'T THANK YOU ENOUGH!!!!!

what was preventing my previous code from working?

[p45cal]

mainly because of:
lrow3 = comp_ws.Range("A" & Rows.Count).End(xlUp).Row 'this line moved down to after comp_ws has had data added to it.

[nidenikolev]

that makes sense. well, I really appreciate that call out, I'm glad it was a matter of ordering. How do I mark this as solved?