+ Reply to Thread
Results 1 to 12 of 12

I would like to automate the daily insertion of information into a worksheet

  1. #1
    Registered User
    Join Date
    10-16-2016
    Location
    Cripple Creek, Colorado
    MS-Off Ver
    2007
    Posts
    15

    I would like to automate the daily insertion of information into a worksheet

    I have a log to log remote access to some of our system modules. Our GM logs in daily and I need to make an entry each time he logs in. I need a macro to automate this process and enter his information on the next available row. I have attached a sample log with the information I need to enter each day. I am new to VBA and have no idea where to begin.
    Attached Files Attached Files
    Last edited by krhoover; 08-17-2017 at 03:34 PM.

  2. #2
    Registered User
    Join Date
    07-19-2017
    Location
    Holland
    MS-Off Ver
    2013
    Posts
    10

    Re: Need Macro for repetitive task

    The easiest thing to begin with is to record the macro. Go to the developer tab (not visible? visit https://msdn.microsoft.com/nl-nl/library/bb608625.aspx) and press the "Record Macro" button. This Macro is probably very messy and maybe not working on other files, but it will provide some starting point

  3. #3
    Registered User
    Join Date
    10-16-2016
    Location
    Cripple Creek, Colorado
    MS-Off Ver
    2007
    Posts
    15

    Re: Need Macro for repetitive task

    Thanks. I tried that and recorded the information being entered, but when I ran it it must have reentered the information on the same line. I need to enter the same info on the next row each day.

  4. #4
    Registered User
    Join Date
    10-16-2016
    Location
    Cripple Creek, Colorado
    MS-Off Ver
    2007
    Posts
    15

    Re: Need Macro for repetitive task

    OK, I have recorded a macro and have it working to insert the information. However, I used the copy and Insert Copied Cells by means of a button. It enters the information , but it puts it on the same row every time(shifting any further entries down by 1 row). How do I make it go to the next empty row to insert the information? Here is the Macro code so far:

    Sub Insert()
    '
    ' Insert Macro
    '

    '
    Rows("1:1").Select
    Selection.Copy
    Sheets("RAMP Log").Select
    Rows("138:138").Select
    Range("B138").Activate
    Selection.Insert Shift:=xlDown
    Sheets("Davids Macro").Select
    Range("A3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("A1").Select
    End Sub

  5. #5
    Registered User
    Join Date
    07-19-2017
    Location
    Holland
    MS-Off Ver
    2013
    Posts
    10

    Re: Need Macro for repetitive task

    Oke, if i interpret your Macro correct, you want to Cut row 1 from a certain active sheet and copy it to sheet "RAMP Log". Is the original data is on sheet "Davids Macro"?

    Try this:


    Please Login or Register  to view this content.
    What is does:

    First determine the last row in the sheet where the data needs to be cut into (RAMP Log) and add 1 to it so the last row is an empty row.
    Note: it searches for the last row in which column A has a value. If A is not always filled, you will get into trouble (you can alter the "A" into "B" ofcourse when colum B is always filled)
    Then it cuts row 1 from Davids Macro (source sheet) to the RAMP Log sheet.

    I'm not sure if this is all what you intended to do, it looked like the only functional code in your macro. I guess you 'accidentally' activated some other sheet again without doing something.

    If the source is not always on the first row, but also on a last row or whatever, the macro will change Range("1:1") will be dynamic, just like I've done with the LastRow variable.

  6. #6
    Registered User
    Join Date
    10-16-2016
    Location
    Cripple Creek, Colorado
    MS-Off Ver
    2007
    Posts
    15

    Re: Need Macro for repetitive task

    You are correct except that I don't want to cut the data from Davids Macro, I want to copy it to the next empty row on the RAMP Log. Question, is this a macro or is this VB code that I could attach to a button?

  7. #7
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: Need Macro for repetitive task

    If you don't want to cut it, just change .Cut to .Copy.
    Also, those are one and the same. Macros are a name for VB code procedures, and you can attach them to a button if you wish to do so.

  8. #8
    Registered User
    Join Date
    07-19-2017
    Location
    Holland
    MS-Off Ver
    2013
    Posts
    10
    Quote Originally Posted by krhoover View Post
    You are correct except that I don't want to cut the data from Davids Macro, I want to copy it to the next empty row on the RAMP Log. Question, is this a macro or is this VB code that I could attach to a button?
    A macro is vb code. If you want to attach the code to a button, just follow this guide https://support.office.com/en-us/article/Add-a-button-and-assign-a-macro-to-it-0d07db65-6e97-41a0-8396-4db6aef7b3bb

  9. #9
    Registered User
    Join Date
    10-16-2016
    Location
    Cripple Creek, Colorado
    MS-Off Ver
    2007
    Posts
    15

    Re: Need Macro for repetitive task

    I am getting a runtime error 1004. the debugger opens to this:

    Please Login or Register  to view this content.
    The last line (above End Sub) is highlighted. I changed the (x1Up) to (x1Down) because the code worked, but put the information in the first line of the destination sheet instead of the last.
    Last edited by krhoover; 08-17-2017 at 03:35 PM.

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,230

    Re: Need Macro for repetitive task

    code tags added...
    Last edited by protonLeah; 08-17-2017 at 03:42 PM.
    Ben Van Johnson

  11. #11
    Registered User
    Join Date
    10-16-2016
    Location
    Cripple Creek, Colorado
    MS-Off Ver
    2007
    Posts
    15

    Re: Need Macro for repetitive task

    I think I have corrected the errors pointed to. Please let me know if I need to make additional changes.

  12. #12
    Registered User
    Join Date
    10-16-2016
    Location
    Cripple Creek, Colorado
    MS-Off Ver
    2007
    Posts
    15

    Re: Need Macro for repetitive task

    I figured it out and it was on my end. Thanks for all your help!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Help for repetitive task pls help
    By kjhawkes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-06-2016, 12:44 AM
  2. Automate boring repetitive task
    By SpenceBasics in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-05-2012, 10:34 PM
  3. [SOLVED] Convert column of apparent dates to actual date values
    By Neolex in forum Excel General
    Replies: 6
    Last Post: 08-22-2012, 08:29 PM
  4. problem with macro in a repetitive task
    By mahmoodn in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-03-2011, 09:20 AM
  5. Using Loops for Repetitive Task
    By fa888 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-20-2010, 10:40 AM
  6. [SOLVED] Macro -- repetitive tasks
    By OTS in forum Excel General
    Replies: 1
    Last Post: 10-07-2005, 12:05 PM
  7. Need a simple VBA code to automate a repetitive task.
    By madhu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2005, 07:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1