AutoFill Column With Relative Variables

MrJoshua · Dec 17th 2007

Hi,

This is a continuation from a previous thread but best belongs in its own domain. I have a cell which has a reference to another worksheet. This cell is automatically through a series of other macros and so its position is relative to others. Eg, Employees as headers, their skills going down, all referencing to the employee sheets with all their details on. With employees being added and deleted as necessary.

Currently I have this code

Code

Sheets("Data Table").Select
        Sheets("Data Table").Range("IV4").End(xlToLeft)(1).Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = resp


        Sheets("Data Table").Select
        Sheets("Data Table").Range("IV6").End(xlToLeft)(1).Offset(0, 1).Select
     ActiveCell = "=" & "'" & resp & "'" & "!P12"

Where 'resp' equals the variable for the employee name and thus the newly inserted sheet. The active cell correctly references the 'resp' worksheet showing (eg) "Sam!P12", but at the moment my only option is to have one of these lines for each cell which is an incredibly slow process because I would need 200+ of them.

Any ideas on the formula i would need to to fill this in?

My best guess is this, but I get an error

Code

Selection.AutoFill Destination:=Range("IV6:IV200").End(xlToLeft)(1).Offset(0, 0), Type:=xlFillDefault

Any help very much appreciated.

Thanks

Sam

GrahamB · Dec 18th 2007

Re: AutoFill Column With Relative Variables

hi Sam,

This is an issue I face fairly regularly and the way I have overcome it is to right a simple looping code that goes through every worksheet, identified that it is a relavent sheet, and then copies, creates or adjusts whatever.

it goes something like this

Code

Sub add_formula()
Dim i As Integer
Dim j
For i = 1 To 250
On Error Resume Next


Sheets(i).Select
j = Sheets(i).Name


'add an if statement to eliminate worksheets that don't have personnel recordes for example


If [a1] <> "" Then 'where cell A1 would have the persons name and on other sheets it is blank




' at this point add your code to add the formula on to your main sheet.


' eg Sheets("Data Table").Select
'Sheets("Data Table").Range("IV6").End(xlToLeft)(1).Offset(0, 1).Select
'ActiveCell = "=" & "'" & j & "'" & "!P12"
Else
End If


Next i
End Sub

Display More

Using this method, you can add, edit etc on both a mainsheet or all of the individual sheets.

HTH,

GB

jindon · Dec 18th 2007

Re: AutoFill Column With Relative Variables

Not sure but like this?

Code

With Range("IV6").End(xlToLeft).Offset(,1)
    .Formula = "='" & resp & "'!p$12"
    .AutoFill .Resize(195)
End With

MrJoshua · Dec 18th 2007

Re: AutoFill Column With Relative Variables

Thankyou for your help, very much appreciated, all is now working. Its a wonderfully helpful website and you have all been very kind, have a jolly nice xmas.

Sam

GrahamB · Dec 18th 2007

Re: AutoFill Column With Relative Variables

Sam,

It is a great site, I learn more by looking at what others ask than I could ever think of.

Enjoy your Christmas, may 2008 be a record peaceful year!

GB

AutoFill Column With Relative Variables

Participate now!

Share