Macro to insert date and data into next available column

[andyd92]

Hi all

I've tried searching the forums but with no success.

I'm working on a macro which will basically pull in stock levels based upon the product code.

I will use windows scheduler to open and run the macro on a daily basis.

But I want the stock levels pulled into a new column and the macro to insert the current date at the top of that column.

So for example, A2 to A100 will have unique product codes. The macro will then run a vlookup formula to search stock quantity from another workbook, and insert the corresponding stock levels into B2 to B100.

The next day when the macro runs, I want it to insert the date at the top of the column, run the vlookup and insert the stock levels below that date.

So for each day, I'm recording stock levels for that day for each product.

Hope that makes sense, any help greatly appreciated.

Thanks

Andy

 

[Andrew Poulsom]

Welcome to MrExcel.

To return the number of the next column:

Dim NextCol As Long
NextCol = Cells(1, Columns.Count).End(xlToleft).Column + 1

 

[andyd92]

Thanks for the quick reply.

I tried inserting the code into various places, but not having any luck. Here is the macro so far, where exactly do I insert it??

Sub UpdateStock()
'
' UpdateStock Macro
'


'
Workbooks.Open Filename:="C:\Users\H36231\Desktop\M\Current stock.xlsx"
Windows("Stock Alert.xlsm").Activate
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Current stock.xlsx]Sheet1'!C1:C2,2,FALSE)"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B17"), Type:=xlFillDefault
Range("B3:B17").Select
End Sub

 

[Andrew Poulsom]

Try (untested):

Sub UpdateStock()
    Dim NextCol As Long
    Workbooks.Open Filename:="C:\Users\H36231\Desktop\M\Current stock.xlsx"
    Windows("Stock Alert.xlsm").Activate
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
    Cells(1, NextCol).Value = Date
    Cells(2, NextCol).Resize(, 16).FormulaR1C1 = _
        "=VLOOKUP(RC1,'[Current stock.xlsx]Sheet1'!C1:C2,2,FALSE)"
End Sub

 

[andyd92]

Hey thanks for the reply again.

That doesn't quite work. It inserts the data into columns B1, C1, D1. E1 etc

When would I would like is to insert data into B2, B3, B4, B5 then the next time i run the macro it will be C2, C3, C4, C5, then next E2, E3, E4, E5 etc.

 

[Andrew Poulsom]

Silly me:

Cells(2, NextCol).Resize(16, 1).FormulaR1C1 = _
        "=VLOOKUP(RC1,'[Current stock.xlsx]Sheet1'!C1:C2,2,FALSE)"

 

[andyd92]

That's it, works perfectly thank you very much.

Is there anyway for the macro to insert the date at the top of that column?

 

[Andrew Poulsom]

Isn't this putting the date at the top of the column?

Cells(1, NextCol).Value = Date

 

[andyd92]

Yes it is sorry, my bad.

Thanks again.

Kind regards

Andy