Macro to Paste special any values into a worksheet

[Milos]

Hi all,

Does anybody know how to design a macro that will paste special any values that I put into a worksheet (Sheet1)? My copied values will change in size and could be from multiple other spreadsheets. So the code probably needs to remember the last action performed rather than a spreadsheet name.

I had started working on some simple code for this but this is far beyond my noob capabilities..

Any help is greatly appreciated!

Thanks,

 

[L. Howard]

Perhaps you could use a change event macro in each sheet module you want to copy the values from.

You would change Range("A1:Z10")) to suit the range on each sheet as necessary (the range you want to copy FROM).

The code here copies to Sheet1 to the next empty cell in column M.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:Z10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    
     Target.Copy
     Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
    
    Application.EnableEvents = True
    Application.CutCopyMode = False
    
End Sub

 

[Milos]

Thanks. I was wanting to design a macro for the sheet that I will paste values into. This is what I have so far:

Sub Paste_Values_Any_Sheet ()

With Selection
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteColumn With
End With

End Sub

Macro only works if you attach it a command button to it though as clipboard deletes the copy for some reason.

 

[L. Howard]

Okay, what or how do you determine what gets copied from a sheet and what determines which sheets are to be copied from.

Can you give a step by step procedure of how you want the macro to determine the sheet/s to copy from and how to determine what is to be copied from the sheet/s.

And where on the destination sheet is the data to be copied to.

Howard