Copying some informations

Hello dear Sir,

Please see attached copy spreadsheet which contains - sheet1 and sheet2.

Sheet1 - all information.
Sheet2 - destination.

There are some invoice details (sheet1) which should be copy to Sheet2 as appropriate ranges.

There are 3 steps:

STEP 1

1. Invoice number - (sheet1, from column C) should be copy to sheet2 (column J).
2. Date - (sheet1, from column D) should be copy to sheet2 (column H).
3. Amount - (sheet1, from column K) should be copy to sheet2 (column E)
4. Currency - (sheet1, from column J) should be copy to sheet2 (column C)
5. Acc. - (sheet1, from column A) should be copy to sheet 2 (column D)
6. put "SH" word to column A in sheet2 and column I from sheet2 should be +30days from rage H in sheet2.

STEP 2

1. put "SV" word to the second row after "SH" in column A (sheet2)
2. Currency(sheet2) should be copy to column C from sheet2 (previous row)
3. Amount(sheet2) should be copy from column E to column D sheet2 (previous row)

STEP 3

1. put "SN" word to the next row after "SV" in column A sheet2
2. Same as currency
3. Go to sheet1 and take nominal code from column B and copy to column D in sheet2.
4. Go to sheet1 and take ACC. from column A and copy to column A (only letters) and copy to column F (only digits) - for example: AAB(column E) 005(column F)
5. Go to sheet1 and take amount(appropriate row) from column K and copy to column G in sheet2.
6. Go to sheet1 and take Description(appropriate row) from column G and copy to column G in sheet2
7. Go to sheet1 and take Name(appropriate row) from column H and copy to column I in sheet2
8. Go to sheet1 and take Placement(appropriate row) from column I and copy to column J in sheet2.
9. Go to sheet1 and take Nominal code from column B and copy to column K in sheet2

You will see example copy of information from sheet1 to sheet2 in attached file.

Many Thanks in Advance

Regards

Halid

Hi RudiS,

Sheet2 is an example. You can see how informations copied from sheet1 to sheet2. These numbers and amount change each time and I need to enter manually from sheet1 to sheet2 format. The base informations are on sheet1 which change every day. But I see your macros didn't put correct amounts and descriptions to the correct ranges in sheet2.

Hope this will help you to write new macros. Just see sheet2 and you will get how and where it should be copy informations.

Best Regards

Khalid

STEP 1

3. Amount - (sheet1, from column K) should be copy to sheet2 (column E) - I meant ONLY total amount should be copied but you copied all amounts. Row 5(sheet2), A5 should be SV as per "copy example" sheet. Then Total amount (37169.08) should be copied next row (D5)(sheet2). Then next row (A6) should be "SN" and B3:B9(sheet1) should be copied to D6:D12(sheet2) and amounts K3:K9(sheet1) should be copied to G6:G12(sheet2) and names H3:H9(sheet1) should be copied to I6:I12(sheet2) and I3:I9(sheet1) should be copied to J6:J12(sheet2) as per example sheet. This is example for one invoice (BXP7183).

Could you please write macro for one invoice and we will use this macros for other invoices? I know I didn't explain exactly, sorry.

Red highlighted ranges are correct in your macros. You need to copy as per blue highlighted ranges.
Regards

Khalid

Super, it is working and everything is OK Just I need a little bit change..

1. Start to put number "26" from starting B4 in sheet2 to the end.
2. Please see sheet2 after running macros: Range E4 and D5 should be shown as a simple digit, like this 37169.08 (not AZN 37069.08).
3. Please put each "SH" starting row number "4" (range G4), for example, it should be put range G13, G16, G19 etc..
4. The font should not be bold, just simple and Arial 6.
5. Dates in column H (sheet2) should be as 07/02/12 format (not 07.02.12)

Please check below and after this will be last request and then work will be done.

I was in Cape Town in February 19-24. I visited to "Cape of good Hope" and Langa place where poor people live. Then we went to wine yard. Very nice and great places. I enjoyed.

Thank you that you help me.

Best Regards
Halid

Hi,

We visited Langa place for seeing how people live there. I loved Cape Town. We stayed in 12 Apostles hotel.

Macros working very good and thank you!

Sorry for being a head pain but this is the last to add some commands:

1. As you see macros take BXP prefix number (BXP7183) from sheet1 and copy to range J4 in sheet2. We have some invoices which is concerning "C/N". For example, BXP7188 C/N, C/N-it means minus in Accounting. So, Normal invoice will be as BXP7183 (7183 as example) but Credit note will show BXP7188 C/N (we put "C/N" at the end of BXP numbers). So, If we see "C/N" word in J column (sheet2), then put number "5" in column J (sheet2) instead of "4". It means if we see invoice like BXP1234 without "C/N" word, then put number "4" to appropriate range in column G (sheet2). Otherwise please put "5".

2. As you see invoice number starts from BXP7183(from the first invoice). But if you want to start from the BXP7186? Could you please add button that I enter BXP start number and then macros should start from this number and go down at the last row?

I know this is too long but if you help me, I will be very glad

Best Regards

Hi,

We visited Langa place for seeing how people live there. I loved Cape Town. We stayed in 12 Apostles hotel.

Macros working very good and thank you!

Sorry for being a head pain but this is the last to add some commands:

1. As you see macros take BXP prefix number (BXP7183) from sheet1 and copy to range J4 in sheet2. We have some invoices which is concerning "C/N". For example, BXP7188 C/N, C/N-it means minus in Accounting. So, Normal invoice will be as BXP7183 (7183 as example) but Credit note will show BXP7188 C/N (we put "C/N" at the end of BXP numbers). So, If we see "C/N" word in J column (sheet2), then put number "5" in column J (sheet2) instead of "4". It means if we see invoice like BXP1234 without "C/N" word, then put number "4" to appropriate range in column G (sheet2). Otherwise please put "5".

2. As you see invoice number starts from BXP7183(from the first invoice). But if you want to start from the BXP7186? Could you please add button that I enter BXP start number and then macros should start from this number and go down at the last row?

I know this is too long but if you help me, I will be very glad

Best Regards

Sorry, please see attached error when I put BXP number to start copying...

Sorry, it is working good! I put invoice number manually)) But it should be selected by mouse)

Many Thanks RudiS. Thanks for your help!

Hello Rudi,

Hope you are well. What about weather in Cape Town?

Please see attached excel file and please play macros. Macros thinking over 15 minutes after playing macros. Could you please reduce time or any additional code which will be 1-2 minutes?

Best Regards

Khalid

Thanks Rudis, I will sent it.

Hi Rudis,

Hope you are well.

Please see attached spreadsheet and macros which copying informations from Registration sheet to Cashbook sheet.

As you see if column F+H(Registration sheet) equal to C+S(Cashbook sheet) then go to sheet Registration and take appropriate range from column C(BXP numbers) and copy to column V(Cashbook sheet), then take amount from column K(Registration sheet) and copy to column X(Cashbook sheet).

The first problem is you see yellow highlighted rows in Registration sheet, there are two "inv" numbers match with two rows from Cashbook sheet. But macros took only 635.13 amount and copy to column X (Cashbook sheet). It should be copy also 5461.23. First row in Cashbook sheet should be 5461.23 and second row 635.13.

Please help in this and then we will continue second step.


Many Thanks RudisCash book indi numune.xlsm

Thanks Rudis.