0

I'm having trouble executing my code on multiple sheets at once and I'm hoping someone can shed some light. I run my below code on 4 sheets at once and it works great except for the last line of code that is supposed to drag a formula. The whole code will run on all 4 pages except the last line will only run on the sheet I'm currently viewing. Does anyone know why this is, or how I can force the code to execute the last line on all selected sheets? Below is the code I'm working with. Thank you!

Sub ChargeRent()
'selects first empty cell in tenant ledger
'keyboard shortcut = ctrl+q
ActiveSheet.Range("a8").End(xlDown).Offset(1, 0).Select
Dim todays_date As Date
todays_date = Range("F1")
If todays_date >= "1 / 1 / 17" And todays_date <= "1 / 31 / 17" Then
  'inputs date as first of the month and charges rent in appropriate cell
    ActiveCell.FormulaR1C1 = "='Jan-17'!R100C[3]-DAY('Jan-17'!R100C[3])+1"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "Rent"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R6C[-1],'Jan-17'!R3C[8]:R6C[10],2,FALSE)"
    ActiveSheet.Range("e8").End(xlDown).Select
    Selection.Resize(2).FillDown
A.B.
  • 1
  • 1
  • 2
    Your code will **not** run on four worksheets in one run. It will only *try* to run on whatever worksheet you are currently 'viewing'. It won't actually run properly because your condition involving dates is malformed. –  Nov 27 '17 at 22:30
  • [Avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BruceWayne Nov 27 '17 at 22:30
  • What is the `ActiveCell` refering to? is it a static cell (same everytime you run the code)? or do you want to select a particular cell on the first sheet to process for all sheets? – Calico Nov 27 '17 at 23:02
  • @Jeeped the code actually does run on all 4 pages at once - the only line of the code that won't run is the last line - "Selection.Resize(2).Filldown" Other than that line, the code works perfectly on all pages that are selected in one go. – A.B. Nov 27 '17 at 23:40
  • @Calico basically what the code is doing is gathering rental payment info from different tenants and then organizing that info on separate sheets -each tenant has its own sheet. After it has compiled the information, the code is meant to calculate a running balance for each tenant and pull the formula down to include the new info. The cell(s) the code is referencing are generally always the same cell with certain exceptions if it was a busy month. – A.B. Nov 27 '17 at 23:44
  • @Jeeped how can I better format my condition so the code will run on multiple selected pages? – A.B. Nov 27 '17 at 23:49
  • @Jeeped, it actually works ... the code edits all the selected worksheets .... the date cells are probably formatted as string .... it's all soooo twisted – jsotola Nov 28 '17 at 06:22
  • @jsotola lol, yes I realize the code is not exactly orthodox.. I'm somewhat new to all this and completely self taught. But yes, the code will run on multiple pages at once. Any ideas on why the 'filldown' command won't stick though? – A.B. Nov 28 '17 at 16:14
  • @A.B. When you say generally always the same cell... can you define a condition for that? eg. 'last used row in column C' or something like that. I have some draft code that will compare the dates slightly more robustly and have sorted the loop. Just need to know where the formulas need to be applied. Calico – Calico Nov 28 '17 at 21:24
  • @Calico the range of cells being referenced is most always C100:E103. This is where my tenants' payment info is kept (amount paid, date paid and unit #). It references this info then organizes it appropriately into separate sheets (tenant ledgers). Hope that helps! – A.B. Nov 29 '17 at 00:00

0 Answers0