Excel Gurus help required

  recap 15:49 14 Jan 03
Locked
  recap 15:49 14 Jan 03

I have a workbook with 3 sheets on it. Information stored on sheet 3 is linked to sheet 2 (personell details). In sheet 2 the price per week is entered, I then want this information to be transfered to sheet 1 for printing as an invoice.

Could somebody please help me with the formula for this query?

thanks in advance

recap

  cherria 15:52 14 Jan 03

Can you be a bit more specific.

Do you have multiple entries on sheet 2 and you want to produce 1 invoice per entry or is it just 1 entry on sheet 2 and you need the value linked onto sheet 1 printing purposes?

  recap 16:16 14 Jan 03

Yes sorry cherria it was very general.

I have 50 lines on sheet 2. Each line contains up to 10 cells with infromation that is required to produce an invoice on sheet 1 for printing.

example: sheet 2 cell A1 to be linked to sheet 1 cel reference D15, Sheet 2 cell A2 to sheet 1 d16 and so forth.
I want to be able to carry out this function for each of the 50 lines printing each one individually.

  MalcSP 16:19 14 Jan 03

Highlight the cell which you want to be printed in sheet 1. Enter ='Sheet 2'!A1. Where A1 is the cell you have the required data in sheet 2. You can play with the cell contents as you wish.

  VoG™ 16:24 14 Jan 03

Do you mean that you want it to loop through each of the 50 rows on Sheet2, transpose the data to the relevant cells in Sheet1, print Sheet1, get data for next row etc.

If so please confirm the start and end row numbers.

  MalcSP 16:28 14 Jan 03

Having read your next entry.
It looks like mail merge on Word might be a better bet using the spreadsheet as a database.

  recap 16:29 14 Jan 03

Thanks VoG™ glad one of use speaks the language, lol.

start = A6:K6,

end = A100:K100

  VoG™ 16:37 14 Jan 03

ALT-F11 to open the Visual Basic Editor. Insert/Module.

Paste in the following code

Sub PrintInvoice()

Dim iRow As Integer, iCol As Integer

Dim InSheet As Worksheet, OutSheet As Worksheet

Set InSheet = Sheets("Sheet2")

Set OutSheet = Sheets("Sheet1")

For iRow = 6 To 100

For iCol = 1 To 10

OutSheet.Cells(iCol + 3, iRow + 14).Value = InSheet.Cells(iRow, iCol).Value

Next iCol

OutSheet.PrintOut

Next iRow

End Sub


Close the VBE. Tools/Macros/Macro, select PrintInvoice and Run.

HOWEVER, to save wasting an awful lot of paper if I've got it wrong, change one line above to this:

For iRow = 6 To 10


Once you're happy that its working, change the value back to 100.

  VoG™ 16:39 14 Jan 03

A:K = 11 columns. Change this line accordingly

For iCol = 1 To 11

  cherria 16:55 14 Jan 03

Darn...

A curse on you VoG, may your Macros run slow and your sheets fail to recalculate ;-)

I go away for 45 mins to do what I paid to do and you beat me to the solution.

I'd have placed an INDIRECT formula within the invoice itself and then simply updated a counter on the invoice sheet which was referenced in the INDIRECT to pick up a new set of values from the next row.

I'll beat you next time VoG Ah Ha Ha Ha!!!

This thread is now locked and can not be replied to.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Why ecommerce hasn't taken off on social media

New MacBook Pro 2016 review | MacBook Pro with Touch Bar review: Apple's expensive and powerful…