VBA Autofill question (changing row)

**Swen** · 08-27-2009, 05:36 AM

Hey guys (and girls),

Just a question from my side, I hope some of you will know it because I am breaking my head over this stuff for a long time.

I am creating an excel database which uploads data from one sheet to another for reporting purposes.

The following code I have created till (partially done by recording a macro).

Private Sub 
    Worksheets("Week").Select
    Range("C2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C,'[Weekly Report.xls]Sheet1'!C3:C10,2,0)"
    Windows("Weekly Report.xls").Activate
    ActiveWindow.Close
    Windows("counting report new.xls").Activate
    Sheets("Front").Select
End Sub

Now the thing I want to do is the following.:

When creating the formula "vlookup", that specific cell is selected. From this cell I want to do an autofill till the end (in this case from column C till AE included), the thing is, is that the row number is changing and not constantly the same.

I was looking through different forms and tested several things, but nothing has worked till now. Maybe you will be able to help me out here.

Thanks already!

Swen

**royUK** · 08-27-2009, 05:42 AM

You don't need to do all that selecting, it is unnecessary & will slow your code. I don't really see why you need VBa, why not create a template with all the formulas in?

**Swen** · 08-27-2009, 06:51 AM

Hi RoyUK,

Thanks for your quick reply.

The problem is, and sorry I didn't mention this earlier when posting my thread, is that multiple people are using this workbook. In other words the workbook is set on shared. The other thing is, is that some of my co-workers are working from home.

When placing in every cell a vlookup code it will slow down the opening of the workbook. I could use a specific cell reference, but the problem is, is that the virgin data is placed as random.

Hopefully this will clear out some things.

Cheers!

Swen