Avoiding copy/paste, selection to transfer data in VBA

[ItalianPlatinum]

Looking for a way to transfer a set of data over to another sheet after i set a filter without copy, selecting or pasting.

1. Sheet 1: filtered to not show blanks
   1. Want to transfer the data left showing
2. Sheet 2: Where I want to transfer the data to
3. Columns match up like this:
   1. Column A to Column A13 down
   2. Column I to Column B13 down
   3. Column J to Column C13 down
   4. so on and so on.....

 

[Peter_SSs]

ZVI has asked about how you have filtered for blanks (see post 5). I would also like to know that.
For example, have you just filtered column A of the source data to not show blanks?
Has the filtering been done manually or by code? If by code, could we see that code?

 

[ItalianPlatinum]

ZVI has asked about how you have filtered for blanks (see post 5). I would also like to know that.
For example, have you just filtered column A of the source data to not show blanks?
Has the filtering been done manually or by code? If by code, could we see that code?

Column I or field 9 on the source file not on the destination

'Fomulas for Review (File)
With Workbooks("Compare").Sheets("Periodic")
lr = Cells(rows.count, "A").End(xlUp).row
.Range("I3:I" & lr).Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,4,FALSE),"""")"
.Range("J3:J" & lr).Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,5,FALSE),"""")"
.Range("L3:L" & lr).Formula = "=IF(I3<>"""",B3,"""")"
.Range("M3:M" & lr).Formula = "=IF(I3<>"""",C3,"""")"
.Range("N3:N" & lr).Formula = "=IF(I3<>"""",D3,"""")"
.Range("O3:O" & lr).Formula = "=IF(I3<>"""",E3,"""")"
.Range("P3:P" & lr).Formula = "=IF(I3<>"""",F3,"""")"
.Range("Q3:Q" & lr).Formula = "=IF(I3<>"""",G3,"""")"
.Range("R3:R" & lr).Formula = "=IF(I3<>"""",H3,"""")"
.Columns("A:R").EntireColumn.AutoFit
.Range("I3:R" & lr).Value = .Range("I3:R" & lr).Value
.Range("2:2").AutoFilter Field:=9, Criteria1:="<>"
End With

  With Workbooks("Compare").Sheets("Periodic")
    .Range("A3", .Cells(.rows.count, "A").End(xlUp)).Copy Sheets("Compare").Range("A13")
    .Range("I3", .Cells(.rows.count, "I").End(xlUp)).Copy Sheets( Compare").Range("B13")
    .Range("J3", .Cells(.rows.count, "J").End(xlUp)).Copy Sheets("Compare").Range("C13")
    .Range("K3", .Cells(.rows.count, "K").End(xlUp)).Copy Sheets("Compare").Range("D13")
    .Range("B3", .Cells(.rows.count, "B").End(xlUp)).Copy Sheets("Compare").Range("E13")
    .Range("C3", .Cells(.rows.count, "C").End(xlUp)).Copy Sheets("Compare").Range("F13")
    .Range("D3", .Cells(.rows.count, "D").End(xlUp)).Copy Sheets("Compare").Range("G13")
    .Range("E3", .Cells(.rows.count, "E").End(xlUp)).Copy Sheets("Compare").Range("H13")
    .Range("F3", .Cells(.rows.count, "F").End(xlUp)).Copy Sheets("Compare").Range("I13")
    .Range("G3", .Cells(.rows.count, "G").End(xlUp)).Copy Sheets("Compare").Range("J13")
    .Range("H3", .Cells(.rows.count, "H").End(xlUp)).Copy Sheets("Compare").Range("K13")
  End With

'Fomulas for Review (N)
With Workbooks("Compare").Sheets("Compare")
lr = Cells(rows.count, "D").End(xlUp).row
.Range("L13:L" & lr).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,IF(F13=""INC"",'D'!$A:$Y,'DS'!$A:$Y),2,0),""YYYYMMDD"")),"""")"
.Range("M13:M" & lr).Formula = "=IFERROR(VALUE(VLOOKUP(D13,IF(F13=""INC"",'D'!$A:$Y,' DS'!$A:$Y),3,0)),"""")"
.Range("N13:N" & lr).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,IF(F13=""INC"",' D'!$A:$Y,' DS'!$A:$Y),19,0),""YYYYMMDD"")),"""")"
.Range("O13:O" & lr).Formula = "=IFERROR(VLOOKUP(D13,IF(F13=""INC"",' D'!$A:$Y,' DS'!$A:$Y),22,0),"""")"
.Range("P13:P" & lr).Formula = "=IF(ISERROR(VLOOKUP(D13,IF(F13=""INC"",' D'!$A:$Y,' DS'!$A:$Y),23,0)),,VLOOKUP(D13,IF(F13=""INC"",' D'!$A:$Y,' DS'!$A:$Y),23,0))"
.Range("Q13:Q" & lr).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,IF(F13=""INC"",' D'!$A:$Y,' DS'!$A:$Y),17,0),""YYYYMMDD"")),"""")"
.Range("R13:R" & lr).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,IF(F13=""INC"",' D'!$A:$Y,' DS'!$A:$Y),18,0),""YYYYMMDD"")),"""")"
.Range("S13:S" & lr).Formula = "=IFERROR(VLOOKUP(D13,IF(F13=""INC"",' D'!$A:$Y,' DS'!$A:$Y),8,0),"""")"
.Range("T13:T" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(E13=N13,""OK"",""CHECK""))"
.Range("U13:U" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(CONCATENATE(F13,O13)=""INCD"",""OK"",""CHECK""))"
.Range("V13:V" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",(G13-P13))"
.Range("W13:W" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(H13=Q13,""OK"",""CHECK""))"
.Range("X13:X" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(I13=R13,""OK"",""CHECK""))"
.Range("Y13:Y" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(K13=S13,""OK"",""CHECK""))"
.Range("L13:Y" & lr).Value = .Range("L13:Y" & lr).Value
End With

End Sub

 

[Peter_SSs]

What workbook is that code in?

 

[Peter_SSs]

so on and so on.....

BTW, what does this actually mean in post 1?

 

[ItalianPlatinum]

What workbook is that code in?

Same workbook I bring all the data into one sheet then move to another sheet

 

[ItalianPlatinum]

BTW, what does this actually mean in post 1?

Kind of a moot point now seeing I provided the full code in #12, but another term for etc. Or it continues.

 

[Peter_SSs]

BTW, what does this actually mean in post 1?

OK, I think I can see this in the code now. Sorry.

 

[ZVI]

What is the 'Table' in this formula: VLOOKUP(A3,'Table'!$F:$K,4,FALSE) ?

 

[ItalianPlatinum]

What is the 'Table' in this formula: VLOOKUP(A3,'Table'!$F:$K,4,FALSE) ?

Another sheet in the workbook with static information

 

[ZVI]

Avoid referencing full columns 'Table'!$F:$K because it dramatically slows down such a formula:
VLOOKUP(A3,'Table'!$F:$K,4,FALSE)