insert formula to cell then copy it all the way down

[bgonen]

I try to insert a formula "=TEXT(RC[-23],""mm"")&""-""&TEXT(RC[-23],""yy"")"
to cell (col AC row 4) and then copy it all the way down (of Col AC) to the last row that is used

Thanks
bg

 

[VoG]

Try

Sub test()
Dim LR As Long
LR = ActiveSheet.UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("AC4:AC" & LR).FormulaR1C1 = "=TEXT(RC[-23],""mm"")&""-""&TEXT(RC[-23],""yy"")"
End Sub

 

[bgonen]

Hi Peter
Of course it works; thanks a lot
I will try to struggle myself with the rest of data I try to pull and if I get stuck I will bug again,,,

 

[bgonen]

I am trying to insert a command between the Blue shaded and the Green shaded: copy the whole column (AB), pastespecial> value
Sub MatchCopyPaste100()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False
With Sheets("JDE") 'select JDE
LR = ActiveSheet.UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'define LR
Range("AC4:AC" & LR).FormulaR1C1 = "=TEXT(RC[-23],""mm"")&""-""&TEXT(RC[-23],""yy"")" 'Change date format
Range("Ab4:Ab" & LR).FormulaR1C1 = "=IF(RC[-24]>0,RC[-25]&"".""&RC[-24],RC[-25])"
Range("Ad4:Ad" & LR).FormulaR1C1 = "=VLOOKUP(RC[-2],Index!C[-29]:C[-26],3,FALSE)"
End With
End Sub

 

[VoG]

If I understand correctly try

Sub MatchCopyPaste100()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False
With Sheets("JDE") 'NB. This does not select JDE
    LR = .UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'define LR
    .Range("AC4:AC" & LR).FormulaR1C1 = "=TEXT(RC[-23],""mm"")&""-""&TEXT(RC[-23],""yy"")" 'Change date format
    With .Range("Ab4:Ab" & LR)
        .FormulaR1C1 = "=IF(RC[-24]>0,RC[-25]&"".""&RC[-24],RC[-25])"
        .Value = .Value
    End With
    .Range("Ad4:Ad" & LR).FormulaR1C1 = "=VLOOKUP(RC[-2],Index!C[-29]:C[-26],3,FALSE)"
End With
End Sub

 

[bgonen]

Peter
Thanks again; it works fine now
I added one more column (col AE ,,shaded blue) but this time I get a compile/reference error
Sub MatchCopyPaste100()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False
With Sheets("JDE") 'NB. This does not select JDE
LR = .UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'define LR
.Range("AC4:AC" & LR).FormulaR1C1 = "=TEXT(RC[-23],""mm"")&""-""&TEXT(RC[-23],""yy"")" 'Change date format Col AC
With .Range("Ab4:Ab" & LR)
.FormulaR1C1 = "=IF(RC[-24]>0,RC[-25]&"".""&RC[-24],RC[-25])" 'Combine two columns (GL acct and Obj acct)
.Value = .Value
End With
.Range("Ad4:Ad" & LR).FormulaR1C1 = "=VLOOKUP(RC[-2],Index!C[-29]:C[-26],3,FALSE)" 'Col AD =type
End With
.Range("Ae4:Ae" & LR).FormulaR1C1 = "=IF(RC[-3]>39999,""P&L"",""Balance-Sheet"")"
End With
End Sub

 

[VoG]

This compiles

Sub MatchCopyPaste100()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False
With Sheets("JDE") 'NB. This does not select JDE
    LR = .UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'define LR
    .Range("AC4:AC" & LR).FormulaR1C1 = "=TEXT(RC[-23],""mm"")&""-""&TEXT(RC[-23],""yy"")" 'Change date format Col AC
    With .Range("Ab4:Ab" & LR)
        .FormulaR1C1 = "=IF(RC[-24]>0,RC[-25]&"".""&RC[-24],RC[-25])" 'Combine two columns (GL acct and Obj acct)
        .Value = .Value
    End With
    .Range("Ad4:Ad" & LR).FormulaR1C1 = "=VLOOKUP(RC[-2],Index!C[-29]:C[-26],3,FALSE)" 'Col AD =type
    .Range("Ae4:Ae" & LR).FormulaR1C1 = "=IF(RC[-3]>39999,""P&L"",""Balance-Sheet"")"
End With
End Sub

 

[bgonen]

Hi Peter
Of course it works now,,Thanks again

Hopefully one last question;
I have a pivot table (ws call Pivot) that I am trying to link to the JDE data tab but I need to change the range of the data that the pivot captures (for example right now the last cell is in R7296c31 but each time that I add new data to the JDE sheet the range will increase)
Manual Macro looks like this;

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"JDE!R3C1:R7296C31"
ActiveWorkbook.ShowPivotTableFieldList = True
End Sub

 

[VoG]

Hi.

I suggest that you start a new thread for this. It is a different subject and I'm no expert on PTs.

 

[bgonen]

Just posted it
Thanks again for all your help
Boaz