CODEWITHSUNDEEP
excelvbarangematch
VBAWARD
VBAWARD

Reputation: 71

Match Ranges between worksheets and hidden rows and paste data into another worksheet's last row

I'm hoping I could get some assistance with the following. Thank you in advance for any and all suggestions.

Description: I have 3 worksheets. I'm trying to copy the values in Column A Sheet 1 (IRA) to Sheet 2 (XVD) if the number of values in Sheet 1 match the total number of values in Sheet 3 (POV)

Sheet 3 (POV) has a filter applied which is needed. The goal is to match the Values in IRA to POV's filtered rows, and if they match, then simply append the total # of values in Column A of IRA to XVD's last row in Column A as well.

Issues: The copy paste doesn't work correctly and doesn't run since the IF statement doesn't work out to true.

Also, the lastrow for XVD (checking for visible cells only) returns a value of only 3 when there are over 1800 filtered rows. 

Sub copycontactsiratopov()

'activate source sheet
ActiveWorkbook.Worksheets("IRA").Activate

'define last rows for all three sheets
LastrowIRA = ActiveSheet.Range("A1").CurrentRegion.Rows.count

'there are over 1800 values but it keeps showing only 3?!
LastRowXVD = ActiveWorkbook.Sheets("RevereseVlookup").Range("A1").SpecialCells(xlCellTypeVisible).Rows.count
LastRowPOV = ActiveWorkbook.Sheets("POV").Range("A1").CurrentRegion.Rows.count

'copy the data needed, values are generally less than 10000 rows
ActiveWorkbook.ActiveSheet.Range("A2:A10000").Copy

'if the number of lastrow in source sheet is equal to total VISIBLE last row within reference sheet then
If LastrowIRA = LastRowXVD Then

'copy the range from source to destination
Worksheets("POV").Range("A" & LastRowPOV + 1).Value = Worksheets("IRA").Range("A2:A10000").Value


'else display msg for error handling
Else: MsgBox "Row Count between IRA and XVD tab is off! *CHECK*"

End If

End Sub

Upvotes: 0

Views: 47

Answers (1)

DanM
DanM

Reputation: 195

where is your paste command, don't see it.

ActiveWorkbook.ActiveSheet.Range("A2:A10000").Copy
Workbooks("put your wb name here").sheets("POV").Range("A2").paste

something like that should work

Upvotes: 0

Related Questions