Copy entire row - but values not formula

[nzkazza]

I have the code below which copies input from a sales input form into the sales worksheet. The sales input form uses vlookup to return value such as category, item name and price from and inventory worksheet.

Problem is if I then later change the price of an item it cahnges the "Sales" history. as it is the formula that is pasted and not the values. I am thinking that if only the value is pasted to the "Sales" history worksheet then any changes to inventory will not affect the history. I have tried playing with the code but keep getting errors.

Please Login or Register  to view this content.

[rollis13]

Splitting the row and adding a PasteSpecial should do the trick.
From:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

[BuckoAk]

An additional Enumeration to rollis13 post.
If you have Special Formatting on those Cells you can also use xlPasteValuesAndNumberFormats to preserve the formatting.

xlPasteValuesAndNumberFormats
vs.
xlPasteValues

[nzkazza]

Thankyou
I was trying to put the PasteSpecial at the beginning rather than the end. I have no idea how you guys know all this stuff - 3 weeks ago I had never used a macro now I have (wth help from this forum) created a mini program with reasonable reporting and functionality - just file a little big at the moment working on that one.. I am astounded what excel can actually do. But I am learning quickly. One other question have I duplicated code in the above? seems like I have repeated my If statement?

Also i understand I am meant to go back to my orginal post and post and edit to post solved - but I am being a little dim and cannot manage to do it. Can someone explain to a real idiot how to actually do this please.
Thanks
Kazza

[zbor]

It's like using Chinese for 3 months and be amazed how people in China know all those letters


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[rollis13]

One other question have I duplicated code in the above? seems like I have repeated my If statement?

Yes, (I think) your code does duplicate more than necessary. You should attach a file with a small sample of your data to allow other to understand what's going on. Showing how it is before and then after would be usefull.

[nzkazza]

Changed the code to the below and thought it was working great but I have now discovered that it is only copying the last row to the destination sheet not all populated rows as it was before> Any ideas?

Please Login or Register  to view this content.