Need help with VBA code to pull from separate sheet

[clau818]

Hi all,

Novice at VBA here and need some help adding an extra element to my current VBA code.

So, I created some formatting code that I have stored in a separate file and whenever we need to make use of the VBA code we open the file and use the shortcut I associated with it, in this case Ctrl+Shift+F. The code is used to format invoice data that we get from the internet into a usable form in excel. I need to add some Vlookups to this code so that it references another tab in the working file. I'm not sure how to add this code to my separate VBA file so that it pulls from the correct tab every time.

The formula's I would like to add to my code are as follows;
In cell J1 there should be a formula that reads
=VLOOKUP(E1,SHEET1!$C$1:$K$182,9,FALSE)
Then in Cell J2 there should be a formula that reads
=VLOOKUP(E2,'SHEET2'!$A$2:$C$128,3,FALSE)
Then in cell I3 there should be a formula that reads
=VLOOKUP(H3,'SHEET2'!AN:AQ,4,FALSE)-----this formula should loop until the cell in column H is blank
Then in cell K3 there should be a formula that reads
=I3*C3 ------this formula also should loop until the cell in column C is blank
Then in cell L3 there should be a formula that reads
=I3-E3 -------again this formula should loop until the cell in column E is blank

Again, I would like this code to live in a separate file where the macro is stored rather than in the working file itself.

Thanks for your help in advance!!!

 

[DragonSoft]

Try adding this to your macro (probably near the top after and declarations)
You did not specify what sheet the Cells to contain the formula are on so make sure you change the SHEET?? to the proper sheet title.

Worksheets("SHEET??").Range("J1").Formula = "=VLOOKUP(E1,SHEET1!$C$1:$K$182,9,FALSE)"
Worksheets("SHEET??").Range("J2").Formula = "=VLOOKUP(E2,'SHEET2'!$A$2:$C$128,3,FALSE)"
Worksheets("SHEET??").Range("I3").Formula = "=VLOOKUP(H3,'SHEET2'!AN:AQ,4,FALSE)"
Worksheets("SHEET??").Range("K3").Formula = "=I3*C3"
Worksheets("SHEET??").Range("L3").Formula = "=I3-E3"