Automatic archiving of data

[jonnygoldsbrough]

Hello,

I am a long time forum user but have never had to post before (lucky me!). I am reasonably good with excel but have zero macro/programming knowledge.

I have a excel sheet that details pricing information for some raw materials. These are updated daily.

I would like to schedule this data to export on a daily basis into either another tab or another sheet as an archive. I would intend to make the first column a date column with =today() to differentiate the information. I would want the archive to appear in a single sheet & update the next available row.

I would really appreciate any advice?

Thank you!!

Jonny
UK

 

[jonnygoldsbrough]

I think I have almost got there. But my current formula doesn't like one line:

Sub ArchiveUpdate()
'
' ArchiveUpdate Macro
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Archive daily data")
Set pasteSheet = Worksheets("Archive")

copySheet.Range("A:E").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Stridhan]

Your copying whole columns which wont fit when your pasting.
You want to copy all, even row 1?

 

[jonnygoldsbrough]

Hi,

I would like to copy everything apart from row one.

Thanks,

Jonathan

 

[Stridhan]

try in a copy

Sub test()

Dim lr As Integer
Dim wsFrom As Worksheet, wsTo As Worksheet
With Application
    .ScreenUpdating = False
End With

Set wsFrom = ThisWorkbook.Sheets("Archive daily data")
Set wsTo = ThisWorkbook.Sheets("Archive")

'Depending on what column containing the highest rownumber with data, change "A" if needed
lr = wsFrom.Cells(Rows.Count, "[COLOR=#ff0000]A[/COLOR]").End(xlUp).Row
wsFrom.Range("A2:E" & lr).Copy
wsTo.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues

With Application
    .CutCopyMode = False
    .ScreenUpdating = True
End With

End Sub

 

[jonnygoldsbrough]

Works great - thank you so much!

 

[Stridhan]

you're welcome