Results 1 to 4 of 4
  1. #1
    Junior Member
    Join Date
    May 2008

    Default Creating Macro to copy and paste data into the next empty column

    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!


  2. #2
    Senior Member
    Join Date
    Dec 2004
    Wellington, NZ

    Default Re: Creating Macro to copy and paste data into the next empty column

    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
    Last edited by Parry; 25-05-2008 at 06:27 PM.

  3. #3
    Senior Member
    Join Date
    Dec 2004
    Wellington, NZ

    Default Re: Creating Macro to copy and paste data into the next empty column

    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.


  4. #4
    Junior Member
    Join Date
    Nov 2008

    Default Re: Creating Macro to copy and paste data into the next empty column

    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


Similar Threads

  1. Copy paste not working
    By Skiltz in forum PressF1
    Replies: 1
    Last Post: 25-06-2006, 04:15 PM
  2. MS Excel formatting with paste macro
    By Eorr in forum PressF1
    Replies: 2
    Last Post: 16-06-2003, 09:56 AM
  3. copy & paste
    By mark c in forum PressF1
    Replies: 2
    Last Post: 17-12-2002, 02:12 PM
  4. Where has my "cut, copy, paste" in OE6 gone to ?
    By Bill Lester in forum PressF1
    Replies: 21
    Last Post: 03-07-2002, 06:08 PM
  5. Copy & Paste Pictures
    By in forum PressF1
    Replies: 0
    Last Post: 01-12-2000, 04:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts