Vlookup Macro to fill several cells

[EdGilmore]

Hi,

I'm trying to write a macro that will automatically fill several cells with information located on a different sheet of a workbook. I could do this manually using the vlookup function, however I want to do it via a macro so it can be done using a click button.

The principal is as follows:

Sheets labelled "Submission" and "Data"

A reference number is typed into cell C2 of "Submission"

I want this to Vlookup "Data" and return values on that sheet into cells C4-C8 of "Submission". The vlookup would return the following values:

C4 - "Data" column B

C5 - "Data" column C

C6 - "Data" column D

C7 - "Data" column E

C8 - "Data" column F

The column to lookup in "Data" is column A.

The Vlookup I am currently using for cell C4 is:

=vlookup(C2,Data!A:B,2,FALSE)

And so on.

Any ideas?

 

[cjcobra]

In your macro, that in put the formula you want in each of the top cells, then copy and paste it down. You could copy/paste special/values afterwards so the numbers can't change.

 

[EdGilmore]

Thanks cjcobra. VB code isn't exactly my strong suit, any chance you could show me how it would look in VB?