VB Code to copy certain data based on header

[rmomin]

Hello:

Please refer to attached file.
Sheet "Temp" has credit card transcation.
In Sheet "Sheet1", i have header as Date,Reference,Description,Card Member,Amount

I need VB Code to copy all transaction from "Temp" sheet to "Sheet1" for defined Header.
Sometime, Cardmember Data may not be there in "Temp" sheet.
In this case I will need to enter as "Joe Doe"
I have manually copied the data in "Sheet1"

Let me know if you have any questions.
Thanks.

R

[nankw83]

Hi rmomin,

Try below code ...

Please Login or Register  to view this content.

[rmomin]

Hello nankw83:

Thanks a lot, works

R

[nankw83]

Glad to help

You can thank those who have helped by clicking the star icon under their usernames to * Add Reputation

[rmomin]

Hello:

Please refer attached...
Its not doing as it suppose to.
I deleted Card Member column and tried the code.
But its not copying and pasting to the correct column.
Please check and let me know.
Thanks

R

[nankw83]

I have already shutdown my PC heading to bed & I can’t open .xlsb files from my mobile. However, if you changed the order of the columns, you need to adjust the same from my previous code - the below part of code is referring the columns needed

Array(1, 13, 3, 4, 6)

[rmomin]

Hello:

I think instead of Column #, Code need to look for Header Date, reference, Description, Card Member and Amount.

R

[jindon]

Please Login or Register  to view this content.

[nankw83]

jindon's code is the way to go ... I have just added few lines to loose the formatting of the original sheet

Please Login or Register  to view this content.

[rmomin]

Hello:
Please refer to attached file..
Both of the above code is erroring out @

Please Login or Register  to view this content.

Would appreciate if you can put the code in the file and try..and send the working file.

Thanks

R

[nankw83]

The error you're getting is due to that you removed the column with header 'Card Member' from sheet Temp. Sheet Temp looks like a statement provided by a bank/credit card company so does it keep changing ? The format & columns order ?

[rmomin]

Hello:

Yes Header will keep changing.
Some cards statement would not have certain header.
That is the reason, i named Header in Sheet1.
ONly those header data needs to be copied.
If Card member column is not in Temp sheet then as mentioned add "Joe Doe".

Also, the data header in the Temp sheet is not likely to be fixed at certain column #.
That is why, i had said to look for Header in Temp sheet and copy that data in Sheet1 for that particlar column.

I hope this is clear explanation.
Please let me know if you have any questions.
Thanks.

R

[nankw83]

Ok, this is another option looping through your 5 columns in Sheet1 & assuming header will be found in row #7 in sheet Temp

Please Login or Register  to view this content.

[jindon]

Please Login or Register  to view this content.

[rmomin]

Hello nankw83:

Yes it works now...
I like to add Star Reputation but its not allowing me.

But , Thanks a lot..

R

[nankw83]

Hello nankw83: Yes it works now...
I like to add Star Reputation but its not allowing me. But , Thanks a lot..

Noted, glad to help