+ Reply to Thread
Results 1 to 8 of 8

Issue regarding recorded Macro to transfer data from Input sheet to Storage sheet

  1. #1
    Registered User
    Join Date
    07-04-2012
    Location
    crewe, england
    MS-Off Ver
    2013
    Posts
    36

    Issue regarding recorded Macro to transfer data from Input sheet to Storage sheet

    Dear All,

    I have attached my working spread sheet with three sheets, a list, a data input and a storage sheet from which I will use to report. I have created an input sheet to mirror a manual job card that are completed by operatives on a daily basis and updated to a spread sheet weekly. On entering the data, by operative I was hoping to copy the input to dataStorage sheet before processing the next. I have created a recorded Macro: CopyPasteDelete to achieve this. However while it copies over the data, I am struggling to prevent the data from overwriting the first set added.

    I would also like help to prevent rows being copied to dataStorage sheet the don't contain any data in cells D14:D18 on the Input sheet. The recorded Macro below clears the input data, but I was hoping that there was a way to retain the formulas in A14:C18

    CopyPasteDelete
    [Sub CopyPasteDelete()
    Sheets("InputForm").Select
    Range("A14:S18").Select
    Selection.Copy
    Sheets("DataStorage").Select
    Range("A3:S7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("InputForm").Select
    Range("A14:S18").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    End Sub]

    Any help or guidance would be great

    Many thanks

    Mark
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Issue regarding recorded Macro to transfer data from Input sheet to Storage sheet

    Hi Mark. Try:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    07-04-2012
    Location
    crewe, england
    MS-Off Ver
    2013
    Posts
    36

    Re: Issue regarding recorded Macro to transfer data from Input sheet to Storage sheet

    Hi Mumps1

    That works brilliantly, I even made a small tweak which am pleasantly shocked at. I have changes out the Line [Sheets("InputForm").Range("C14:C18").ClearContents] to [Sheets("InputForm").Range("D14:S18").ClearContents]

    Many thanks for help

    Regards
    Mark

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Issue regarding recorded Macro to transfer data from Input sheet to Storage sheet

    Glad it worked out.

  5. #5
    Registered User
    Join Date
    07-04-2012
    Location
    crewe, england
    MS-Off Ver
    2013
    Posts
    36

    Re: Issue regarding recorded Macro to transfer data from Input sheet to Storage sheet

    Mumps1

    Just a quick thought is there any way to code in a way of preventing Duplicate entries being posted to the DataStorage sheet during Copy/paste i.e if a date/s were selected in error for an operative, for which data already resided in the DataStorage sheet, that would prevent the copying /pasting of the data and request the inputter to review and correct their work?

    Regards
    Mark

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Issue regarding recorded Macro to transfer data from Input sheet to Storage sheet

    Hi Mark. Sorry for the delay. Try this macro. It will pick up the duplicates and then select the cell where the data needs to be modified (date). Make the corrections and run the macro again. Please note that no data will be copied over until all the corrections are made.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-04-2012
    Location
    crewe, england
    MS-Off Ver
    2013
    Posts
    36

    Re: Issue regarding recorded Macro to transfer data from Input sheet to Storage sheet

    Mumps1

    That Macro works perfectly thank you so much for that, it will make a huge difference . I have used the first Macro and extracted the data from the DataStorage sheet via a pivot table and then used this to populate a summary sheet for measuring performance, I have hit a few snags which I have posted to a New Thread :Having used GetPivotData to populate cell C3 can I extract Item1 to populate cell B3

    Many thanks once again
    Regards
    Mark

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Issue regarding recorded Macro to transfer data from Input sheet to Storage sheet

    My pleasure.

+ 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. Transfer data from input sheet to data sheet by using command button
    By vinh1985 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-17-2014, 12:17 AM
  2. How to transfer data from a daily input sheet to a separate monthly total sheet
    By Jcooper71 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2014, 02:37 PM
  3. I need macros for input data and to be recorded on another sheet
    By ronka in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-25-2013, 09:30 AM
  4. Macro to transfer data from Input sheet to summary form
    By CA_needing_help in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2013, 03:14 AM
  5. Transfer cell value from sheet to sheet based on user input (SOLVED)
    By Terrydorset in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-18-2013, 01:28 PM

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