Copy invoice data in cells from 2 different sheets in 1 invoice record sheet

[pa3ewc]

Have 3 worksheets in my invoice program:

1 sheet with the name "Invoice CT ENG Blanco"
1 sheet with the name "Invoice CT ENG"
1 sheet with the name "Invoice Records"

Sheets "Invoice CT ENG Blanco" and "Invoice CT ENG" have different cells what I want to save to my sheet "Invoice Records"

The layout of my sheet "Invoice Records" have standard collums where I will put the data in from one of the invoice-sheets.

When I make an invoice I choose in which sheet I will make my invoice. ("Invoice CT ENG Blanco" OR "Invoice CT ENG")

So in my example I have fill in both sheets "Invoice CT ENG Blanco" and "Invoice CT ENG" with some data, but when I save this with a VB code it go's wrong...
The code must save the cells of the worksheet where I standing on. So I first choose which worksheet I want to use and the program looks which worksheet is filled in and save the cells of that worksheet in my "Invoice Records" worksheet.

But the sheets have different cell numbers what I want to save so thats also a problem how I can arranged that.

This my code what I have found on this forum and have try to work out for mine question so far. The first loop for the sheet "Invoice CT ENG Blanco" works fine, but the loop of the sheet "Invoice CT ENG" go's wrong.


Sub Invoice_Records_from_all_worksheets()

Dim i As Integer
Dim c As Range
'Dim a As Integer
Dim a As Range
With Sheets("Invoice Records")
Set c = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)

End With

With Sheets("Invoice CT ENG Blanco")
For i = 1 To 7 'Loops 7 times. Change the 7 to match the number of your cells to copy
'Take the following cells from worksheet "Invoice CT ENG Blanco" and put it on worksheet "Invoice_Records"
c.Offset(, i - 1).Value = .Range(Choose(i, "X15", "W14", "X17", "U47", "B13", "W16", "B5")).Value
Next i

With Sheets("Invoice CT ENG")
For i = 1 To 6 'Loops 6 times. Change the 6 to match the number of your cells to copy
'Take the following cells from worksheet "Invoice CT ENG" and put it on worksheet "Invoice_Records"
c.Offset(, i - 1).Value = .Range(Choose(i, "", "X15", "W14", "W17", "U40", "B5")).Value

Next i

End With

End With

End Sub


So thats what I have. I don't know where I can upload my exel example.?
Waiting of help with my question.

Thanks.

 

[oldbrewer]

pretend invoice sheet eng blanco

pretend inv sheet eng

pretend invoice records sheet

date

invoice No

amount £

date

invoice No

amount £

date

invoice No

amount £

04/01/2018

eb1

110

03/01/2018

e1

2000

04/01/2018

eb1

110

09/01/2018

eb2

500

08/01/2018

e2

2100

09/01/2018

eb2

500

14/01/2018

eb3

450

13/01/2018

e3

1500

14/01/2018

eb3

450

19/01/2018

eb4

200

18/01/2018

e4

7000

19/01/2018

eb4

200

24/01/2018

eb5

220

23/01/2018

e5

12000

24/01/2018

eb5

220

29/01/2018

eb6

80

28/01/2018

e6

9000

03/01/2018

e1

2000

08/01/2018

e2

2100

13/01/2018

e3

1500

18/01/2018

e4

7000

23/01/2018

e5

12000

I assume you want to enter the latest invoice into records

as soon as you enter it

steps in words are

select the 3 cells and copy

switch to records tab

find the first empty row

paste

go back to inv sheet eng

see if any new entries are there

if so repeat sequence

go back to first sheet

is this what you want to do ?

<colgroup><col><col><col span="4"><col><col span="6"><col><col span="3"></colgroup><tbody>
</tbody>

 

[pa3ewc]

Hello oldbrewer,
Almost...correct. but you have in the two sheets the same cells what will be automatically copy to the "Invoice Records" sheet when I decide to finisch the invoice.

And the next invoice to the first empty row on the "Invoice Records" sheet. that's ok.

But on the other sheet "Invoice CT ENG" I want to pick up different cells what I want to save in the next empty row of my "Invoice Records" sheet.
Thats what I have write in te second part of the code.

With Sheets("Invoice CT ENG")
For i = 1 To 6 'Loops 6 times. Change the 6 to match the number of your cells to copy
'Take the following cells from worksheet "Invoice CT ENG" and put it on worksheet "Invoice_Records"
c.Offset(, i - 1).Value = .Range(Choose(i, "", "X15", "W14", "W17", "U40", "B5")).Value

Here I pick my cells that I want to save when my invoice is finished on the "Invoice CT ENG" worksheet. That's the difference what I want when I collect the data from these cells.
It is not a standard procedure. When I make a code with only the first loop that works fine but the second loop go's wrong.

Otherwise I must for every invoice sheet I make, make a different vba code that will past the cells that I want to my "Invoice records" sheet when I finished with my invoice. But I want 1 code where I can put every worksheet name in what I now have or make in the future. Than in every loop of that worksheet that I can chose which cell I want to put on my "Invoice Records".

So the program looks in which worksheet I stay and will than make a copy of the cells to my "Invoice Records" sheet. So there will be always 1 sheet filled in with data of the invoice what must be copied.
You make never 2 invoices in both sheets

Hope you understand what I look for qua loops in a vba code...

Thanks...

 

[oldbrewer]

I do not understand what you want. You enter invoice details in EITHER the first or second sheets, and then copy it to the records sheet. Maybe you enter more than 1 before copying to records. Why not enter all invoices on a single sheet and analyse that. You could then sort by invoice type, date, amount or whatever.

 

[pa3ewc]

Hello oldbrewer,

Yes, I copy EITHER details in the first or second sheet. Thats correct.

But the first sheet has other cells with data as the cells in the second sheet. Most of the cells in both sheets are the same but the second sheet has other cells what I want to copy to the "Invoice Records" sheet.

You can see this in the vba code in mine first mail that I was trying to make 2 loops to get it work. The first loop is working for the sheet "Invoice CT ENG Blanco".

The second loop for the sheet "Invoice CT ENG" is not working. So I was looking for a code that looks when it is running that the program sees that there is no data in cell B5 in the first sheet. When there is no data in cell B5 of the first sheet that he go's further to the second sheet at cell B5. Is there something filled in than that will be the invoice with the cells what must copied to the "Invoice Records"-sheet.

Something like that, because all my invoices must have cell B5 (that's the Company name where I send this invoice to) filled in, otherwise I don't know how I can do it another way.
But I still need the option in the code that I can put in the second sheet my own extra cells in what then will copied in the "Invoice Records" sheet also.

So you see that in away the most of the cells from both sheets are the same what I want to copie but I want the option per sheet that I can get extra data from other cells in that sheet to be copied also to the "Invoice Records" sheet.

That was my idea about how it should work...but I don't know if this is possible to make in excel.? Hope oldbrewer that I have explain what I want so you can think of it possible or that I must do it another way what also can work...

Thanks for your time.

 

[oldbrewer]

so the second sheet has a few extra cells you want to copy - why is that an issue - whether they are "constant" or pertinent only to one invoice it does not matter. Please paste some pretend data in second sheet that you want copying to the third sheet - it can be made up data - to maintain confidentiality - it might say be data in A10 to G10 plus Z3 and AA9

 

[pa3ewc]

Hello oldbrewer,

I will try to explain how my sheets look like:

"Invoice CT ENG Blanco" that's sheet 1

Sheet 1:
To company = cell B5
Customer nr = cell W16
Customer = cell B13
Amount = cell U47
Due date = cell X17
Invoice date = cell W14
Invoice nr = cell X15


"Invoice CT ENG" that's sheet 2

Sheet 2:
To company = cell B5
Amount = cell U40
Due date = cell W17
Invoice date = cell W14
Invoice nr = cell X15

And the data in these cells must be copied to the sheet "Invoice Records". On this "Invoice Records" sheet I have the following collums with the text fill in:

A1 = INVOICE NR
B1 = INVOICE DATE
C1 = DUE DATE
D1 = AMOUNT
E1 = CUSTOMER
F1 = CUST.NR
G1 = TO COMPANY

So under these collums must the data be copied when I finnisch the invoice on sheet 1 OR on sheet 2. Than the invoice data's from these cells would I have on this "Invoice Records"-sheet for a good overview.
You see that some cells are missing in sheet 2 e.g. cust.nr and customer. And the "amount" cell numbers on both sheets are different. So it is not easy for my to how I get things copied the right way in my "Invoice Records"-sheet.

Hopefully you know a little how my invoice sheets look like and how I like to get an overview of my invoices what I make...

Thanks..

 

[oldbrewer]

you are making this very hard. Why should I copy this info into a spreadsheet ? Make up a dummy example please, highlight the cells, copy them, and paste into the reply box here.....

 

[pa3ewc]

Hello oldbrewer... yes is difficult to explain when you have no visual of what I have now and what I want that the program must do. Try to make screendump but it is not really easy to do. I have send you earlier the cell names. Both sheets 1 and 2 have an 1 page A4 invoice what I fill in as I make the invoice for the customer.
Hopefully you can now see how it is per sheet but otherwise is it possible to send my excel prog by email.?
But I see in this forum not that I can send you an personal email.? I want to put an image in but I do not know how I can upload it to this reply. That's also not very easy to upload.... So I try it with a copy and paste action.

INVOICE

sheet 1 "Invoice CT ENG Blanco"

To:

Customer:

INVOICE

Customer Companie name

Invoice date:

08-02-2018

Invoice number:

CT

2018000001

Our reference:

103

Due date:

23-02-2018

WK

TRUCK/TRAILER NR

DRIVEN KM'S

PRICE/KM

AMOUNT

<tbody>
</tbody>

INVOICE

sheet 2 "Invoice CT ENG"

To:

New company

INVOICE

Invoice date:

13-02-2018

Invoice number:

CT

2018000002

Our reference:

Due date:

28-02-2018

Information…

500,00

15,00

Total:

€ 515,00

<tbody>
</tbody>

"Invoice Records" sheet with the following cells.

INVOICE NR	INVOICE DATE	DUE DATE	AMOUNT	CUSTOMER	CUST.NR	TO COMPANY
2018000001	8-2-2018	23-2-2018	€ 0,00	Customer Companie name	103

<tbody>
</tbody>

Hope that gives you a better understanding and overview of the 2 sheets with there cells what I like to put in my "Invoice Records" sheet as an overview of the invoice what i have make.
You can see that it only takes the data from the cells of sheet 1 with invoice nr 2018000001. So that works in de vba code, but when I make an invoice on sheet 2 and I want that the data of that sheet with invoice nr 201800002 comes on the second row of the sheet "Invoice Records". Thats all what I like to be happen...

Thanks oldbrewer...

 

[oldbrewer]

say your data is just in A1:E5

you would highlight those cells, edit, copy, come to this forum, click in reply box and control + V (to paste)

it is very simple