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

**Franky alta** · 04-15-2016, 04:19 AM

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!

**Franky alta** · 04-15-2016, 02:04 PM

"BUMP" - Bump - anybody, please?

**Franky alta** · 04-16-2016, 08:43 PM

"BUMP" - - anybody, please?

**jaslake** · 04-17-2016, 01:43 PM

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.

**Franky alta** · 04-17-2016, 02:56 PM

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 :

Sub AllTrackRpt_1_M2L()
  Dim st1a As Long, a As Long  '<-Dim j As Long, lr As Long
  Dim st1b As Long, b As Long
  Dim st1c As Long, c As Long
  Dim st1d As Long, d As Long
  Dim st1e As Long, e As Long
  Dim st1f As Long, f As Long


  For st1a = 4 To 51           '<-For j = 4 To 51
  For st1b = 326 To 331
  For st1c = 326 To 332
  For st1d = 326 To 333
  For st1e = 326 To 329
  For st1f = 326 To 328

    If Cells(st1a, 359).Value > 1 And Cells(st1a, 359).Value < 36 Then _
       Cells(Rows.Count, 147).End(xlUp).Offset(1).Resize(, 50).Value = Cells(st1a, 359).Resize(, 50).Value
    Next st1a
       
     a = Range("EV" & Rows.Count).End(xlUp).Row
    With Range("EQ2:GN" & a)                   
   .Sort key1:=[EV3], order1:=xlDescending, Header:=xlYes
    
    End with 
     
    Next
    
     If Cells(st1b, 359).Value > 1 And Cells(st1b, 359).Value < 36 Then _
       Cells(Rows.Count, 478).End(xlUp).Offset(1).Resize(, 22).Value = Cells(st1b, 359).Resize(, 22).Value
    Next st1b        
   
     b = Range("RO" & Rows.Count).End(xlUp).Row
    With Range("RJ2:SE" & b)                   
   .Sort key1:=[RO3], order1:=xlDescending, Header:=xlYes     
    End with 
     
    Next
    
    End with 
     
    Next
    
     If Cells(st1c, 359).Value > 1 And Cells(st1c, 359).Value < 36 Then _
       Cells(Rows.Count, 566).End(xlUp).Offset(1).Resize(, 22).Value = Cells(st1c, 359).Resize(, 22).Value
    Next st1c        
   
     c = Range("UY" & Rows.Count).End(xlUp).Row
    With Range("UT2:VO" & c)                   
   .Sort key1:=[UY3], order1:=xlDescending, Header:=xlYes     
    End with 
     
    Next
    
    End with 
     
    Next
    
     If Cells(st1d, 359).Value > 1 And Cells(st1d, 359).Value < 36 Then _
       Cells(Rows.Count, 654).End(xlUp).Offset(1).Resize(, 22).Value = Cells(st1d, 359).Resize(, 22).Value
    Next st1d        
   
     d = Range("YI" & Rows.Count).End(xlUp).Row
    With Range("YD2:YY" & d)                   
   .Sort key1:=[YI3], order1:=xlDescending, Header:=xlYes     
    End with 
     
    Next
    
    End with 
     
    Next
    
     If Cells(st1e, 359).Value > 1 And Cells(st1e, 359).Value < 36 Then _
       Cells(Rows.Count, 830).End(xlUp).Offset(1).Resize(, 22).Value = Cells(st1e, 359).Resize(, 22).Value
    Next st1e        
   
     e = Range("AFC" & Rows.Count).End(xlUp).Row
    With Range("AEX2:GN" & e)                   
   .Sort key1:=[AFC3], order1:=xlDescending, Header:=xlYes     
    End with 
     
    Next
    
    End with 
     
    Next
    
     If Cells(st1f, 359).Value > 1 And Cells(st1f, 359).Value < 36 Then _
       Cells(Rows.Count, 742).End(xlUp).Offset(1).Resize(, 22).Value = Cells(st1f, 359).Resize(, 22).Value
    Next st1f        
   
     f = Range("ABS" & Rows.Count).End(xlUp).Row
    With Range("ABN2:ACI" & f)                   
   .Sort key1:=[ABS3], order1:=xlDescending, Header:=xlYes     
    End with 
     
    Next
     
    End With
   End Sub

Thank you in advance jaslake.

**jaslake** · 04-17-2016, 03:54 PM

Hi Franky

Try zipping the File then attach.

**Franky alta** · 04-17-2016, 04:26 PM

Hi Jaslake,

File is saved as Binary and Zipped is over 16.3 mb

Thanks.

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Agging report, openingbalance report,customer report vab code was not working

Agging report, openingbalance report,customer report vab code was not working

Code is only running once instead of multiple times, code is also spitting out a runtime e

[SOLVED] produce JSON code and POST to website

[SOLVED] VBA code to search for #REF, #VALUE, #N/A within multiple excel sheets and produce report

[SOLVED] VBA Code to Produce Multiple Pie Charts (data in columns)

manipulating code and adding code to produce pdf

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions