+ Reply to Thread
Results 1 to 12 of 12

automatically copy input data to different sheets

  1. #1
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Indonesia
    MS-Off Ver
    2010
    Posts
    177

    automatically copy input data to different sheets

    Hi,

    I am trying to create input sheet, where the data can automatically add to another sheet based on date(month),
    Basically, i input all data ( date, category, value ) in one sheet,
    then the data inputted can automatically copy to each month sheet.

    I wonder if this can be done by vba/formula?

    attach is example of the data.

    Thank you for any help/suggestion.
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: automatically copy input data to different sheets

    In each month sheet type the name of the sheet (month) in cell A1. Then array enter this formula in A3. Fill down and across until you get blanks. Copy that range and paste into each of the other month sheets.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  3. #3
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Indonesia
    MS-Off Ver
    2010
    Posts
    177

    Re: automatically copy input data to different sheets

    Hi,

    FlameRetired, thank you for your help.
    This work great.
    However, when the input data has blank cell, it result 0 in each month, how do i make the 0 disappear?

    Also how do i change the formula or A1, if i have data example: Jan 2016, and Jan 2017

    Thank you.
    Last edited by qiyusi; 06-01-2017 at 02:23 AM.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: automatically copy input data to different sheets

    Since your sheets are only named by months you would need to change the sheet naming scheme to include the year, replace the sheet names in A1 of each sheet and then modify the original formula. I would also recommend using 4 digit years.

    Do you have a workbook with those renamed sheets?

    However, when the input data has blank cell, it result 0 in each month, how do i make the 0 disappear?
    I don't understand this part. The ISNUMBER function and the IFERROR function should cover that. What am I missing?

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: automatically copy input data to different sheets

    @FlameRetired: If you enter a date on "InputData" without any other data so will get an entry in the "Month" sheet with 0s (my interpretation of the problem).

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: automatically copy input data to different sheets

    Ahh. That might be it.

    qiyusi if JohnTopley has interpreted correctly try this array entered formula to return blanks.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Indonesia
    MS-Off Ver
    2010
    Posts
    177

    Re: automatically copy input data to different sheets

    Thank you for both responds,

    Yes, John was right, and i think above formula should be solve the problem.
    how ever, i am not sure how to copy them to column B, C, D , which part of the formula need to change ?

    would you please attach with example of sheet? really appreciate your help.

    Thank you.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: automatically copy input data to different sheets

    @qiyusi,
    You would array enter the formula into cell A3 of 'jan' sheet. Copy across and down until you get blanks. As you copy across the formula automatically changes the column references for you. When finished copy that whole range and paste into cell A3 of each of the other month sheets.

  9. #9
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Indonesia
    MS-Off Ver
    2010
    Posts
    177

    Re: automatically copy input data to different sheets

    Hello,

    Apologies for late reply.

    I am trying to copy paste new formula, but its not work properly.

    Would you review attached file?

    Thank you.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: automatically copy input data to different sheets

    My bad.

    Try array entering this formula in A3 of sheet 'jan'. Fill down and across. Then copy that range and paste into A3 of 'feb' and 'mar'.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You may have to re format ID and Value to General.

  11. #11
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Indonesia
    MS-Off Ver
    2010
    Posts
    177

    Re: automatically copy input data to different sheets

    Thank you for quick reply.
    This is great,
    it work for first line, when copy below, i got blank result?

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: automatically copy input data to different sheets

    I don't know. It works at my end.

    How/what are you copying?

+ 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. Automatically Copy Data From Multiple Sheets To A Master
    By oliverhj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-16-2016, 10:53 AM
  2. string macro for a user input button, copy and paste data from all sheets to summary
    By Lawleypops in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2015, 11:17 AM
  3. string macro for a user input button, copy and paste data from all sheets to summary
    By Lawleypops in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-14-2015, 11:09 PM
  4. Copy and Paste data automatically from master sheet to other sheets
    By wschleis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-18-2014, 04:29 PM
  5. [SOLVED] Automatically Copy Data from Multiple Sheets to a Master Sheet
    By Ryan_Brandt in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-10-2014, 06:45 AM
  6. Replies: 2
    Last Post: 07-17-2014, 09:46 PM
  7. Copy data from 1 sheet to other sheets automatically
    By mattyb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-24-2008, 03:43 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