Offset referencing only working for formulas, not copying? [VBA]

[nidenikolev]

I have two 'with' statements that select first visible cell and it's not selecting visible cells in column in order to copy+pastevalue.

I just want it to: select first visible cell in column AR=>select down to last visible cell in AR=>copy=>pastevalues

I used this exact structure for RC[-1] formula implementation,but instead of putting in ...(xlCellTypeVisible).R1C1Formula I need to add something like .Copy

Why isn't this copying, isn't it the same logic?

Can anyone help?

Code:

With ActiveSheet.Range("AR2").CurrentRegion
.AutoFilter Field:=5, Criteria1:="<>"
End With

With .Columns(44)
    .Offset(1).SpecialCells(xlCellTypeVisible).Copy
    .PasteSpecial xlPasteValues
End With

 

[nidenikolev]

Ok, thanks that worked. The reasoning behind the offset is because it populated a formula in my header, is there a way around that? If not, then simply renaming the cell is an easy workaround.

Sorry about the miscommunications, I just want to finish this script and am getting annoyed at these minor errors going on. I really appreciate yours and Joe4's help

 

[Fluff]

The reasoning behind the offset is because it populated a formula in my header, is there a way around that?

Yup, use the code Joe4 supplied in post#10

 

[nidenikolev]

I can't thank you two enough, this makes my script run so much quicker. If either of you have a second, could you review my code and see if there are areas for improvement? I had two areas where I needed to copy+paste columns as values, so I just did basic macro-recording like VBA coding. ANy areas for critique would be greatly appreciated, but right now, it's running flawlessly and quick enough. Thank you two again!!

Sub Update()

'File Paths
Dim Preplan As String
Dim PS_Export As String
Preplan = "M:\Template.xlsm"
PS_Export = "M:\PS_Export.xlsx"
'Open WB's
Dim PP_WB As Workbook
Dim PS_WB As Workbook
Set PP_WB = Workbooks.Open(Filename:=Preplan, Password:="")
Set PS_WB = Workbooks.Open(Filename:=PS_Export)

Dim PP_WS As Worksheet
Set PP_WS = PP_WB.Sheets("2017 Pre-Planning Emp Detail")
Dim PS_WS As Worksheet
Set PS_WS = PS_WB.Sheets("ps")
Dim rng As Range
Dim rng2 As Range
Dim lrAR As Long
Dim lrAS As Long
Dim lrAX As Long
 
lastrow = PP_WS.Range("A" & Rows.Count).End(xlUp).Row
lastrow2 = PS_WS.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False

PP_WB.Activate
With PP_WS

With .Range("AE2")
.Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$K,11,FALSE)"
.AutoFill Destination:=Range("AE2:AE" & lastrow)
End With
With .Range("AF2")
.Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$H,8,FALSE)"
.AutoFill Destination:=Range("AF2:AF" & lastrow)
End With
With .Range("AG2")
.Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AY,50,FALSE)"
.AutoFill Destination:=Range("AG2:AG" & lastrow)
End With
With .Range("AH2")
.Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$O,15,FALSE)"
.AutoFill Destination:=Range("AH2:AH" & lastrow)
End With
With .Range("AI2")
.Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$P,16,FALSE)"
.AutoFill Destination:=Range("AI2:AI" & lastrow)
End With
With .Range("AE:AI")
.Copy
.PasteSpecial xlPasteValues
End With

End With
PS_WB.Activate
With PS_WS
.Columns("AH:AH").Insert Shift:=xlToRight

'fills to last row in PS report'
With .Range("AH2")
.Formula = "=AD2+AG2"
.AutoFill Destination:=Range("AH2:AH" & lastrow2)
.Range("AH1") = "Variable Comp"
End With

End With

PS_WB.Close savechanges:=False

PP_WB.Activate

With PP_WS
'if there is an "X" in column F (sr. manager), then do a VLOOKUP, if not then do the calc'
With .Range("AR2")
.Formula = "=IF(F2=""X"",VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AH,34,FALSE),(AS2+AU2+AX2))"
.AutoFill Destination:=Range("AR2:AR" & lastrow)
End With
'if there isn't an "X" in column F, then do a VLOOKUP, if not keep the calc'
With .Range("AS2")
.Formula = "=IF(F2="""",VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AD,30,FALSE),(AR2-AX2))"
.AutoFill Destination:=Range("AS2:AS" & lastrow)
End With

With .Range("AX2")
     .Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AG,33,FALSE)"
     .AutoFill Destination:=Range("AX2:AX" & lastrow)
End With
With .Range("AX:AX")
     .Copy
     .PasteSpecial xlPasteValues
End With
'Filter section for Sr. Leaders'
With ActiveSheet.Range("AX2").CurrentRegion
.AutoFilter Field:=5, Criteria1:="<>"

lrAX = Cells(Rows.Count, 50).End(xlUp).Row
    With Range(Cells(2, 50), Cells(lrAX, 50))
        .Offset(1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=IF(RC[-6]<=300000,RC[-6]*0.3,IF(AND(RC[-6]>300000,RC[-6]<=500000),((RC[-6]-300000)*0.35)+90000,IF(AND(RC[-6]>500000,RC[-6]<=1000000),((RC[-6]-500000)*0.4)+160000,IF(RC[-6]>1000000,((RC[-6]-1000000)*0.45)+360000,))))"
    End With
'Second filter section'

'selects cell AS2, designates that as current region, autofilters column F'
With ActiveSheet.Range("AS2").CurrentRegion
.AutoFilter Field:=5, Criteria1:="<>"
End With
lrAS = Cells(Rows.Count, 45).End(xlUp).Row
    With Range(Cells(2, 45), Cells(lrAS, 45))
    .Offset(1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[-1]-RC[5]"
End With
.AutoFilter Field:=5
End With
ActiveWindow.ScrollRow = 1
With PP_WS
    Range("AR2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
End With
With ActiveSheet.Range("AR2").CurrentRegion
.AutoFilter Field:=5, Criteria1:=""
End With
'   Find last row with data in column 44
    lrAR = Cells(Rows.Count, 44).End(xlUp).Row
    With Range(Cells(2, 44), Cells(lrAR, 44))
        .Offset(1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[1]+RC[3]+RC[6]"
    End With
Cells.AutoFilter
With PP_WS
    Range("AS2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
End With
With ActiveSheet.Range("AS2").CurrentRegion
.AutoFilter Field:=6, Criteria1:="<>"
End With
    With Range(Cells(2, 45), Cells(lrAS, 45))
    .Offset(1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[-1]-RC[5]"
End With

Application.ScreenUpdating = True
End With
End Sub