copy and paste

[Afarag]

Dears,


Please i have some data that changed daily, and i want to preserve this data to be a reference

mean: i have tow sheets, the first one i get a results and i update it daily, so that the data change every day, i want to group all what i update it automatically

to be pasted as Value and number formats

 

[iferror]

Not tested, so you'd better back up your file before hitting F8

Sub Reference()

Dim Dsht As Worksheet
Dim Rsht As Worksheet
Dim Drng As Range
Dim LCln As Long

Set Dsht = Worksheets("Data")
Set Drng = Dsht.Range(Range("D5"), Range("D" & Rows.Count).End(xlUp).End(xlToRight))
Set Rsht = Worksheets("Reference")
LCln = Rsht.Cells(3, Columns.Count).End(xlToLeft).Column

DT = Dsht.[D3]
Drng.Copy

Rsht.Range("A1").Offset(0, LCln + 1).Value = DT
Rsht.Range("A1").Offset(2, LCln + 1).PasteSpecial xlPasteValuesAndNumberFormats

End Sub

Link the macro to the shape and everytime you click on the shape you'll save the values in sheets Reference.

 

[iferror]

Atually you can merge this two
Set Dsht = Worksheets("Data")
Set Drng = Dsht.Range(Range("D5"), Range("D" & Rows.Count).End(xlUp).End(xlToRight))
into
Set Drng = Worksheets("Data").Range(Range("D5"), Range("D" & Rows.Count).End(xlUp).End(xlToRight))

and drop Dsht def

 

[Smallman]

Afarag

It is advisable that you do not merged cells. Your file loses flexibility and this is highlighted when you come to using vba on a sheet. Use Centre across selection. Looks the same with no loss of fidelity. Both, it is a good idea to use sheet code names and ensure you declare all your variables (DT?). The following does the same thing however I think what you ment in your post is that you want the values number formats and cell formatting.

Sub Moveit()
  Sheet1.Range("D3", Sheet1.Range("G" & Rows.Count).End(xlUp)).Copy
  Sheet2.Range("IV3").End(xlToLeft).Offset(-2, 2).PasteSpecial xlPasteValuesAndNumberFormats
End Sub

I have included a file. You might want to consider increasing the Column size on the second row if you intend your data to be greater than 256 columns.

Take care

Smallman

 

[Afarag]

Hi @Smallman

Great idea it's really gets what i looking for

Thanks a lot,

 

[Smallman]

No worries Afarag - thanks for posting back.

Smallman