View Full Version : Creating Macro to copy and paste data into the next empty column

24-05-2008, 10:01 AM
Hi all,
I am very new to VBS, and have a question.
I am creating a report that will be upated with new data daily, that shows a rolling average of volume for the week.

The approach that I am taking is to manually enter 1 column worth of data into sheet 1: A1 to A16 contains category (will always remain the same), and B1 to B16 contains the manually entered volume for each category.

I would like a macro that would automatically copy the volume data and paste it into a new column with today's date (5/23), on a new sheet. This macro would be run daily, so that new columns would need to be created each day (with the updated date), to paste the manually entered data above. This is one of my problems as i am not sure how to write the macro so that it finds the next column to paste the data... i can only figure out how to overwrite existing data.

After the data is pasted, i would need a formula or line that calculates a rolling average. So on Sunday, i would have divide by 1, monday i would add Sun and Mon data and divide by 2... i cant find a way to have the average rolling, so i dont have to manually update the formula each day.

Any help will be greatly appreciated!


25-05-2008, 06:23 PM
Hi, hopefully this is what you want. You will need to change the references to SourceSht & TargetSht to the names of the source and target sheets. All the code is commented to tell you what is happening. In the VBEditor (Alt + F11 or Tools|Macros|Vb Editor from the menu) select Insert|Module then paste this code in Module1. If you create a button to run the macro simply reference HistoricalData such as this...

Code for a button on a sheet

Private Sub CommandButton1_Click()
End Sub

Code to be pasted into Module1

Sub HistoricalData()
Dim TargetSht As Worksheet, SourceSht As Worksheet, SourceCol As Integer, SourceCells As Range

'If an error occurs skip code to the Err-Hanlder line and the display the error message.
On Error GoTo Err_Handler

'This is the sheet where your copy information from. Change "Sheet1" to the name of your soure sheet
Set SourceSht = ThisWorkbook.Sheets("Sheet1")

'Name of the sheet where data is to be copied to. Rename Sheet2 to the name of your target sheet
Set TargetSht = ThisWorkbook.Sheets("Sheet2")

'This is the cells you will copy data from. This is targeting cells B1 to the last used cell in column B
Set SourceCells = SourceSht.Range("B1:B" & SourceSht.Range("B65536").End(xlUp).Row)

'This is finding the next column available in the target sheet. It assumes dates will be in row 1 and data in row 2 down
If TargetSht.Range("A1").Value = "" Then
'Cell A1 is blank so the column to put data in will be column #1 (ie A)
SourceCol = 1
ElseIf TargetSht.Range("IV1").Value <> "" Then
'Cell IV1 has something in it so we have reached the maximum number of columns we can use in this sheet.
'Dont paste the data but advise the user.
MsgBox "There are no more columns available in the sheet " & TargetSht.Name, vbCritical, "No More Data Can Be Copied"
'stop the macro at this point
Exit Sub
'cell A1 does have data and we havent reached the last column yet so find the next available column
SourceCol = TargetSht.Range("IV1").End(xlToLeft).Column + 1
End If

'Put in the date in the appropriate column in row 1 of the target sheet
TargetSht.Cells(1, SourceCol).Value = Format(Date, "DD/MM/YYYY")

'We can now start copying data. This will copy the cells in column B from the source sheet to row 2+ in the target sheet
SourceCells.Copy TargetSht.Cells(2, SourceCol)

'Advise the user that the process was successful
MsgBox "Data copied successfully!", vbInformation, "Process Complete"

Exit Sub 'This is to stop the procedure so we dont display the error message every time.
MsgBox "The following error occured:" & vbLf & "Error #: " & Err.Number & vbLf & "Description: " & Err.Description, _
vbCritical, "An Error Has Occured", Err.HelpFile, Err.HelpContext

End Sub


26-05-2008, 07:56 AM
Didn't notice the 2nd part of your question until now. In the target sheet you can enter in a sum total for the column and then on the next row an average. If you use the absolute symbol of $ this will progressively provide a cumulative total when you copy cell formula along the row.

Cell A20 has the formula =SUM(A1:A19)
Cell A21 has the formula =AVERAGE($A$20:A20)

When you drag cell A21 to the right to populate columns B on wards you will get the 2nd half of the formula increasing the column reference but the first part remain static.


06-11-2008, 06:57 PM
Hello I'm new user here.

Perry I'd like ask You about Your macro - what i should change to have as a source data
(sheet1) 4(or more) diferent cells C3; C5; C9 ; C12; and as a target(sheet 2) cells B33:B37, then if i will transfer next data from sources C3,C5,C9,C12 next target should be C33:C37. Can You help me in this macro??

Best Regards