2

I have a bit of code that auto fills formulas down to the last row. I want this code to work on two sheets (with different ranges) and to have a button on a separate control sheet.

The problem is, when i run the macro from the control sheet, it affects the control sheet, even though i specified the target sheets in the code. Is there a way around this?

Sub FormulaFill()


    Dim DataLR As Long
    DataLR = Worksheets("Data").UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("I2:M2").AutoFill Destination:=Range("I2:M" & DataLR)

    Dim DataKeywordLR As Long
    DataKeywordLR = Worksheets("data keyword").UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("I2").AutoFill Destination:=Range("I2:" & DataKeywordLR)
jcbermu
  • 17,526
Corrado_
  • 25
  • Add the reference for the sheet to your Range call? My guess would be something like Worksheets("Data").Range("I2:M2"). – Seth Nov 25 '16 at 11:10
  • But he only edited the question? What was the actual solution? – Seth Nov 25 '16 at 13:06
  • my bad;
    that did the trick. Thanks, Seth     – Corrado_ Nov 25 '16 at 13:14
  • I added it as an answer. IF that is how your solution looks consider marking it as the answer or if it doesn't edit it to make it fit/supply your own. – Seth Nov 25 '16 at 13:20

1 Answers1

1

Your original code references the other worksheets for the assignment of DataLR and DataKeywordLR but doesn't do that for the follow up line that executes the auto fill. As such you need to add a reference to both lines that execute the auto fill. A solution could look like this:

Sub FormulaFill()
    Dim DataLR As Long
    DataLR = Worksheets("Data").UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Worksheets("Data").Range("I2:M2").AutoFill Destination:=Worksheets("Data").Range("I2:M" & DataLR)

    Dim DataKeywordLR As Long
    DataKeywordLR = Worksheets("data keyword").UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Worksheets("data keyword").Range("I2").AutoFill Destination:=Worksheets("data keyword").Range("I2:" & DataKeywordLR)

Notice the added Worksheets before Range in each block.

Corrado_
  • 25
Seth
  • 9,165
  • the destination range needs to be changed too

    Worksheets("Data").Range("I2:M2").AutoFill Destination:=Worksheets("Data").Range("I2:M" & DataLR)

     – Corrado_ Nov 25 '16 at 13:30
  • Oh I missed that bit. As you're still a new user why not click the edit button below the post and add it as an edit. You could gain some reputation that way. If you don't want to I'll do it some time later today. – Seth Nov 25 '16 at 13:41