Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Apr 2006
    Location
    Dublin, Ireland, Republic of
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Breakout data (2003)

    I have a source spreadsheet with data in colums. col A=city, B=temp,C=humidity,D=cloudy. etc.... What I want to do on a daily basis is extract each city, and create a sheet with a corresponding name and copy cols B,C,D to the sheet along with the date. The source data will change daily and when thecode is run I want to append/update the appropiate sheet with the new data, if the city is new start a new sheet.

    Something similar was done in article "Automate break-out of data (2003) ", but it did not append data.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Breakout data (2003)

    It's probably best to delete the existing city sheets and create them anew each time, otherwise you'd have to keep track of which rows have been added since the previous time the macro was run.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Breakout data (2003)

    Why bother doing this?

    Just keep 1 sheet with all the data. You can filter on the city to see just that one city, which would be like having each in a separate sheet.

    With one sheet, you can use a pivot table/chart to even summarize and plot the data.

    Steve

  4. #4
    Lounger
    Join Date
    Apr 2006
    Location
    Dublin, Ireland, Republic of
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breakout data (2003)

    I was proposing to run the script once per day max, thus avoiding duplicate data.
    There will only be one row per sheet to be appended, I was planning to insert this after the "last row". This avoids having to keep track of the rows across the macro runs.

    Keeping everything is the same sheet is not an option.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Breakout data (2003)

    Inserting after the last row is not the problem. Determining what has changed on the original data sheet is.

  6. #6
    Lounger
    Join Date
    Apr 2006
    Location
    Dublin, Ireland, Republic of
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breakout data (2003)

    The original sheet is recreated every day. So it could be deleted at the successful completion of the macro.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Breakout data (2003)

    OK, could you attach a small sample workbook with dummy data? That would help with writing the code.

  8. #8
    Lounger
    Join Date
    Apr 2006
    Location
    Dublin, Ireland, Republic of
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breakout data (2003)

    Attached is sample source sheet.
    As I say the thread "Automate break-out of data (2003) <post#=619,835>post 619,835</post#>" appears to do most of waht I require except

    1) appending data to existing sheets
    2) create new sheet when a new city is identified.


    Thanks for your assistance
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Breakout data (2003)

    The code from <post:=471,071>post 471,071</post:> does exactly what you want after adapting it to your situation. See attached workbook.
    Attached Files Attached Files

  10. #10
    Lounger
    Join Date
    Apr 2006
    Location
    Dublin, Ireland, Republic of
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breakout data (2003)

    exactly what I require.

    Thanks for the help.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breakout data (2003)

    How can you add additional columns to sheet 1 and have the data transfer to the new sheets?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Breakout data (2003)

    The range to be copies is set in the instruction

    Set oCpyRange = Range(oCpyStart, oNxtCell.Offset(-1, 4))

    The 4 in the Offset function means that 4 columns to the right of the first column are included, i.e. the first 5 columns are included. If you want to transfer 27 columns, use 26 instead of 4 in the Offset function.

    You should also adjust the line

    oSrc.Range("A1:E1").Copy Destination:=oTgt.Range("A1")

  13. #13
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breakout data (2003)

    Thankyou very much

Posting Permissions

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