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

Threaded View

  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

    Sub Report1_WS1()                                                                                                             
                                                                                                                                  
    Sheets("Sheet1").Select                                                                                                       
    Sheets("Sheet1").Activate                                                                                                     
                                                                                                                                  
    Dim j As Long, lr As Long                                                                                                     
    For j = 4 To 51                                                                                                               
        If Cells(j, 359).Value > 1 And Cells(j, 359).Value < 136 Then _                                                           
           Cells(Rows.Count, 147).End(xlUp).Offset(1).Resize(, 50).Value = Cells(j, 359).Resize(, 50).Value                       
                                                                                                                                  
        Next j                                                                                                                    
        lr = Range("EV" & Rows.Count).End(xlUp).Row '-> "FT" = 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
                                                                                                                                  
        End With
    end sub

    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

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