+ Reply to Thread
Results 1 to 10 of 10

How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 repor

  1. #1
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 repor

    Hello Friends..

    Title seems weird but to be honest I don't know exactly how to explain my problem and ask for help about it.
    Okay, there i go..
    The code was working good for one report, but i was asked to build 4 new report using same date data with different ranges (and eventually up to 14 reports), because by experience i saw this code was working good, then i copied the code 4 times, replaced the necessary range on each one and add a macro to call multiples macro at once (the 4 reports). But now the macros only build the first 4 reports at once, but when the data is updated and able to build the other 4 reports it just wont work,It pretend that work when is called (with no errors) but report values state the same.
    i'm in to this problem for over 7 days and i don't have clear idea what can be the problem because i tried many ways to solve it.
    So, i decide to ask you : Can i merge the 4 (or 6+ reports Ranges ) to manage only 1 VBA code and not many code (1 code for each report)??

    please see my good code copy for One Report and also please see my ranges for each report it might help you to help me,
    Note:
    1-.Report are named Report1, Report2, Report3, Report4, etc,etc
    2-.All data source and reports are located on same worksheet
    Spec of each ranges/value to be applied on each Line Code (ill High Light )

    For j = 4 To 51 <--- Report 1
    For j = 326 To 331 <--- Report 2
    For j = 326 To 332 <--- Report 3
    For j = 326 To 333 <--- Report 4

    =====

    If Cells(j, 359).Value > 1 And Cells(j, 359).Value < 136 Then _ '<--- Same Value for each report

    =====
    Cells(Rows.Count, 147).End(xlUp).Offset(1).Resize(, 50).Value = Cells(j, 359).Resize(, 50).Value <-- Report 1
    Cells(Rows.Count, 478).End(xlUp).Offset(1).Resize(, 22).Value = Cells(j, 359).Resize(, 22).Value <-- Report 2
    Cells(Rows.Count, 566).End(xlUp).Offset(1).Resize(, 22).Value = Cells(j, 359).Resize(, 22).Value <-- Report 3
    Cells(Rows.Count, 654).End(xlUp).Offset(1).Resize(, 22).Value = Cells(j, 359).Resize(, 22).Value <-- Report 4

    Report 1
    =====
    lr = Range("EV" & Rows.Count).End(xlUp).Row '----------> "EV" = DATE Column
    With Range("EQ2:GN" & lr)'---------------------------------> "EQ2:GN" = First/Last Column Range where Report is Going to be copy
    .Sort key1:=[EV3], order1:=xlDescending, Header:=xlYes '-> When add Report with new date, it copied at top. older descending

    Report 2
    ======
    lr = Range("RO" & Rows.Count).End(xlUp).Row '-----------> "RO" = DATE Column
    With Range("RJ2:SE" & lr)'-----------------------------------> "RJ2:SE" = First/Last Column Range where Report is Going to be copy
    .Sort key1:=[RO3], order1:=xlDescending, Header:=xlYes '-> When add Report with new date, it copied at top older descending


    Report 3
    =====
    lr = Range("UY" & Rows.Count).End(xlUp).Row '------------> "UY" = DATE Column
    With Range("UT2:VO" & lr)''-----------------------------------> "UT2:VO" = First/Last Column Range where Report is Going to be copy
    .Sort key1:=[UY3], order1:=xlDescending, Header:=xlYes '<-When add Report with new date, it copied at top older descending



    Report 4
    ======
    lr = Range("YI" & Rows.Count).End(xlUp).Row '-----------> "YI" = DATE Column
    With Range("YD2:YY" & lr)''---------------------------------> "YD2:YY" = First/Last Column Range where Report is Going to be Copy
    Sort key1:=[YI3], order1:=xlDescending, Header:=xlYes '<-When add Report with new date, it copied at top older descending

    NOTE : This is the Main code I'm using for one report at the time

    Please Login or Register  to view this content.

    Thank you so much, Appreciate any help!
    Last edited by Franky alta; 04-15-2016 at 04:29 AM. Reason: edit to be clear on text

  2. #2
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 re

    "BUMP" - Bump - anybody, please?

  3. #3
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 re

    "BUMP" - - anybody, please?

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 re

    Hi Franky alta

    Frankly Franky, your description is a bit confusing.

    Please attach a sample workbook the Structure of which is the same as your actual workbook.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    INCLUDE ANY CODE YOU'RE USING OR HAVE TRIED.

    Remember to desensitize the data.

    Click on GO ADVANCED, scroll down to "Manage Attachments" to open the upload window.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 re

    Hi jaslake,

    I can't send the worksheet because is huge and i's data dependable, and code is working fine now,
    The description of the code is : copy from one range and paste in another Range, But data is sorted by Date =xlDescending order,(where is copied) It means that in order to make 4 reports i have to have 4 times the same code to copy 4 differents reports and paste on 4 different ranges.

    The way i'm calling the 4 code is with Macro over macro.

    i just wish to get one code to handle to copy and paste the 4 ranges.

    I did come with the brilliant idea to merge the 4 codes into one with it ranges (lol ) But
    i'm getting "Next control variable reference" error

    This is the merged crazy code with the real Ranges :

    Please Login or Register  to view this content.
    Thank you in advance jaslake.

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 re

    Hi Franky

    Try zipping the File then attach.

  7. #7
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 re

    Hi Jaslake,

    File is saved as Binary and Zipped is over 16.3 mb


    Thanks.
    Last edited by Franky alta; 04-17-2016 at 04:28 PM. Reason: corrected wrong info

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 re

    Hi Franky

    What you're trying to do is too much for this old and feeble brain to comprehend by looking at the Code...I need to "see" what's happening. If you're willing and able you may Email the Workbooks to me and I'll see if I can sort it out.

    Actually, I'd like to see two Files...
    • This File with Code
      The code was working good for one report
    • The File with the Code that does not work.

    If this is amenable let me know and I'll PM my Email address.

  9. #9
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 re

    Hi jaslake,

    i'm going to ask to the owner file and do that.

    BTW, I have had many error with forum site, i'm receiving server error often.

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 re

    Hi Franky

    Yes, well, we've all been experiencing issues with the Forum...
    BTW, I have had many error with forum site, i'm receiving server error often.
    Hopefully they'll be fixed soon.

+ 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. Agging report, openingbalance report,customer report vab code was not working
    By rana19 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-28-2015, 03:38 AM
  2. Replies: 4
    Last Post: 08-18-2015, 10:02 AM
  3. Replies: 21
    Last Post: 03-04-2015, 07:19 PM
  4. [SOLVED] produce JSON code and POST to website
    By jefjef in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-10-2014, 01:33 PM
  5. [SOLVED] VBA code to search for #REF, #VALUE, #N/A within multiple excel sheets and produce report
    By nadiar in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-02-2013, 01:56 PM
  6. [SOLVED] VBA Code to Produce Multiple Pie Charts (data in columns)
    By Janc in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-30-2012, 06:48 AM
  7. manipulating code and adding code to produce pdf
    By stevesunfold in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2008, 01:47 PM

Tags for this Thread

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