Build Paste Special Value Function

[GeoKoro13]

Hi there,

I'm new here and just recently try to use VBA to make my life a bit easier. But still I'm away in making a code by myself.

What I'm trying to do at the moment is to build a function to copy-paste special the value only. I've seen people posting how to copy and paste special from specific cells, but it would make my job much easier if I could just typing =pastespecial(...).

Any idea if that could happened?

Regards

 

[GeoKoro13]

What I wanted to have is somehow having a "handy" way of pasting permanently values from cell as the value in the cell will change and I want to keep record. However, I don't have just one cell and they will be from different sheets (~ 70) with dynamics names.
If anyone has any idea how I could make that work it would be fantastic to read it.

Thanks again everyone for your reply.

 

[My Aswer Is This]

I could write you a script to copy the selected cells and then paste them where you want. For example select a range on sheet "You" and then paste the range into the next empty row on sheet named "Storage" and it would paste only the values.

Would something like that work.

All you would have to do is select the range then use a shortcut key like Ctrl "L" and the selection would be pasted into the last row on sheet named "Storage"

 

[My Aswer Is This]

If your willing to do what I suggested try this:

Create a sheet named "Storage"
And then use this Module script.

Assign a shortcut key to this script by:
On the Excel Ribbon select "View"
Then select "Macros"
Choose "View Macro"
Select the Macro named "Paste_Selection"
Choose "Options" and assign a shortcut key

Now select any range on any sheet and then use the Shortcut key Example "Ctrl+"z" and the range you selected will be pasted into the sheet named "Storage" on the next empty row.

Sub Paste_Selection()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Storage").Cells(Rows.Count, "A").End(xlUp).Row + 1
Selection.Copy
Sheets("Storage").Range("A" & Lastrow).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[GeoKoro13]

Thank you so much mate for your time.

It sounds good!!! I'm at work at the moment so I get back to you when I'll try it.

Cheers

If your willing to do what I suggested try this:

Create a sheet named "Storage"
And then use this Module script.

Assign a shortcut key to this script by:
On the Excel Ribbon select "View"
Then select "Macros"
Choose "View Macro"
Select the Macro named "Paste_Selection"
Choose "Options" and assign a shortcut key

Now select any range on any sheet and then use the Shortcut key Example "Ctrl+"z" and the range you selected will be pasted into the sheet named "Storage" on the next empty row.

Sub Paste_Selection()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Storage").Cells(Rows.Count, "A").End(xlUp).Row + 1
Selection.Copy
Sheets("Storage").Range("A" & Lastrow).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[GeoKoro13]

Work like a charm My Aswer Is this!!
Very clever!

Thank you for your time

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Work like a charm My Aswer Is this!!
Very clever!

Thank you for your time