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.

 

[pa3ewc]

Hello oldbrewer,
That's what I have done in the above reply. but it is not good for you to analyse..I have also make a image of the excel-sheets and want to upload this above icoon "insert image" but than in de popup box I must type the url to.??? I thougt I go to my directory and upload so the images but that's not easy to do....send an email to pa3ewc@amsat.org if you wish. then i can send you the excel-file....otherwise I don't know how I can do this...

Thanks oldbrewer.

 

[oldbrewer]

It is forbidden by forum rules to go offline - sorry.

I will try to figure out what you want from post #9

 

[pa3ewc]

Hello oldbrewer,

Ok no problem. I will copy the earlier post with the cellnames:

"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


When you make this 3 sheets and make there the cells on with the exact cellnumbers than that is ok. Where these cells are on the sheet is no problem. Than if is possible to write the code that these cells from sheet 1 is copied to sheet 3 the "Invoice Records"sheet. If i stand on sheet 2 and make there my invoice ready to print or whatever after that it must be possible to put these cells in the "Invoice Records" sheet also. More I don't want to be happen.
Thanks for your help...

 

[oldbrewer]

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

INVOICE NR

INVOICE DATE

DUE DATE

AMOUNT

CUSTOMER

CUST.NR

TO COMPANY

29

2018000001

08/02/2018

23/02/2018

Customer Companie name

103

this macro has populated these cells by pulling the data from the top table

I know it will be in sheet3 eventually but this is the tesying phase

not sure which cell contains the amount

ub Macro4()

'

' Macro4 Macro

' Macro recorded 08/02/2018 by bob

'

'

nextblankrownum = Cells(27, 9)

Cells(nextblankrownum, 1) = Cells(15, 24)

Cells(nextblankrownum, 2) = Cells(14, 23)

Cells(nextblankrownum, 3) = Cells(17, 24)

Cells(nextblankrownum, 4) = ""

Cells(nextblankrownum, 5) = Cells(13, 2)

Cells(nextblankrownum, 6) = Cells(16, 23)

End Sub

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

 

[pa3ewc]

Dear oldbrewer,
I have try the code but is not working for my program.


The total amount of the invoice is in sheet 1 in cell U47.
The total amount of the invoice is in sheet 2 in cell U40

This is in both sheets different. When you look at the macro code what I have send earlier and again hereafter you can see that it is in cell "U47".
This code works because in cell "U47" stands the total invoice AMOUNT of the invoice. So this loop of collecting all the data from these cells works fine and are put in the "Invoice Records"-sheet ok.

Only now the second sheet is allmost the same but the total invoice amount is now in cell "U40". And some cells in this sheet aren't there and can not be copied to the "Invoice Records"-sheet. Thats s I want it to be and is so correct. I want from this sheet almost the exact cells as from sheet 1 but some cells do not exist and the total amount invoice cell is different as in sheet 1.
Now I must have an new loop that will collect these cells what I want like the cells: "X15" + "W14" + "W17" + "U40" + "B5" and put only this cells in the "Invoice Records"-sheet.
But the cells "customer" and "cust.nr" are here not filled in because they do not exist on sheet 2, and therefor they can not be copied to the "Invoice Records"-sheet. That's ok and that's how it ment to be.
So in that code I must tell what cells I want to copie to the "Invoice Records"-sheet and that it will be copied in the the row and under the right collum.



Dim i As Integer
Dim c 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 8 times. Change the 8 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
End With
End Sub

This code works perfect for sheet 1.

Wait to see if you can help me with a code for sheet 2 that the cells are copied also to my "Invoice Records"-sheet.

Thanks oldbrewer...

 

[oldbrewer]

note that for my code to work I used cell I27 to calculate the row number of the next blank row - did You do this ?

my code is a simpler way of getting the values...

 

[pa3ewc]

oldbrewer,

Your code gives a error 1004 at running or says a object fault... I think my problem is to complex or to simpel to make it work....It take all the blame that I also not can explain what I want this way.
I go to see for other possibilities because it cost you and me to much time to solve this what I want. It also not very conveniënt that it is not possible to send you an example of mine excel-file, because when you see what I have make in the sheets I know 100% sure that you can fix it in a snap...but now we mailing and mailing....but it is not going anywhere.

Thanks for your time but I must look to get it solved another way, ok.

 

[oldbrewer]

ok, but if you could make my macro work (it works for me) you are 90% there. Time is no problem for me as I am retired.