Copy Code Needed

M

MWH

I'm exporting data from Access to Excel
On one worksheet I will have the data from my database
I need to copy that data to a column on another worksheet in the workbook
I will be tracking the data daily, so I need it to copy the data to a new
column every day (with a date stamp) so I can compare the changes.

Any help is appreciated!
 
J

JMB

Here is one possibility. This copies Column A from Sheet2 to the next
available column in Row1 of Sheet3 (inserting the date in the first row and
the data in the second+ rows).

You will need to change the copy from/to sheets as well as the copy from
column and the copy to row numbers if they are different.

Sub CopyColumn()
Dim SourceWkSht As Worksheet
Const SourceFirstRow As Long = 1
Dim SourceLastRow As Long
Const SourceCol As Integer = 1
Dim DestWkSht As Worksheet
Const DestRow As Long = 1
Dim DestCol As Integer

Set SourceWkSht = Sheet2
Set DestWkSht = Sheet3
SourceLastRow = SourceWkSht.Cells(Rows.Count, 1).End(xlUp).Row

If IsEmpty(DestWkSht.UsedRange) Then
DestCol = 1
Else: DestCol = DestWkSht.Cells(DestRow,
Columns.Count).End(xlToLeft).Column + 1
End If

DestWkSht.Cells(DestRow, DestCol) = Date
SourceWkSht.Range(SourceWkSht.Cells(SourceFirstRow, SourceCol), _
SourceWkSht.Cells(SourceLastRow, SourceCol)).Copy _
DestWkSht.Cells(DestRow + 1, DestCol)

End Sub


The "Else" statement is supposed to all be on one line. The post will
likely wrap it to a second line, so you will have to fix.
 
Top