• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Autofill into new column (in table) not changing references

T

Temma

New Member
Hiya -
I'm stuck. I've been working on this macro to help update some project information for my boss. I have mulitple tables on multiple tabs which all report out to many things. I am attempting to make a macro to help me add new columns when I get a new invoice in and autofill in the formulas from the previous column.

I've been able to add in the new columns no problem. My issue arises when I attempt to autofill the formulas over. When I do it by hand (selecting the column to the right and dragging the formulas over), excel recognizes what I want and changes the "Invoice #" to "Invoice #+1" in the formulas. When I attempt to do this with vba (typed myself or even with running recorded macros), the formulas stay stuck on the previous invoice.

Does anyone know how I can get it to automatically do this?

My code is below. in_num is the invoice number and tableName is the table name passed into the sub. (I have also tried using xlFillDefault, xlFillSeries, and xlLinearTrend to no avail.)

Code: 
  a = "[Invoice " & in_num - 1 & "]"
  b = "[Invoice " & in_num & "]"
  d = tableName & "[" & a & ":" & b & "]"
  Set SourceRange = ActiveSheet.Range(tableName & a)
  Set FillRange = ActiveSheet.Range(d)
  SourceRange.AutoFill Destination:=FillRange
 
@Temma - Welcome to Chandoo.org forums and thanks for posting a question.

Try this code instead.

Code: 
    Set SourceRange = ActiveSheet.Range(tableName & a)
    Set FillRange = Range(SourceRange.Cells(1, 1), SourceRange.End(xlDown).Offset(, 1))
    
    SourceRange.AutoFill Destination:=rng2, Type:=xlFillDefault
 
r1c1 said:
@Temma - Welcome to Chandoo.org forums and thanks for posting a question.

Try this code instead.

Code: 
    Set SourceRange = ActiveSheet.Range(tableName & a)
    Set FillRange = Range(SourceRange.Cells(1, 1), SourceRange.End(xlDown).Offset(, 1))
   
    SourceRange.AutoFill Destination:=rng2, Type:=xlFillDefault
Click to expand...
umm...did you mean "Destination:=FillRange"?

And if so, it still isn't working. I don't understand why it won't continue the pattern.
 
Temma said:
umm...did you mean "Destination:=FillRange"?

And if so, it still isn't working. I don't understand why it won't continue the pattern.
Click to expand...

Yes, that. I tested and it worked ok. What error are you getting?
 
You must log in or register to reply here.
Back
Top