Macro to insert date and data into next available column

andyd92

New Member
Joined
Oct 20, 2013
Messages
24
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Welcome to MrExcel.

To return the number of the next column:

Code:
Dim NextCol As Long
NextCol = Cells(1, Columns.Count).End(xlToleft).Column + 1
 
Upvote 0
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
 
Upvote 0
Try (untested):

Code:
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
 
Upvote 0
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.
 
Upvote 0
Silly me:

Rich (BB code):
Cells(2, NextCol).Resize(16, 1).FormulaR1C1 = _
        "=VLOOKUP(RC1,'[Current stock.xlsx]Sheet1'!C1:C2,2,FALSE)"
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top