VBA code(s) to get data from multiple sheets

mentikk

Board Regular
Joined
Jan 17, 2014
Messages
65
Hi,
I got this vba codes that return values from different sheets (IWKA_PLAN, ADL_PLAN) to main sheet (Gannt). The problem is that when I run one macro it's ok, I get results from specified sheet. But when I start next results replace of previous macro. I'd like to have them below. The amount of results will be changed every day. In future there are about to be more sheets I'll take data from. Also that macros can be run in different order.
The best would also be if data from different separated by border.
So far it looks like that.
Code:
Sub Gantt()
  
    
    With Sheets("Gantt")
    Application.ScreenUpdating = False
    
    lastrow2 = Worksheets("IWKA_PLAN").Range("C48576").End(xlUp).Row
    'lastrow3 = Worksheets("ADL_PLAN").Range("C48576").End(xlUp).Row
    .Range("C4:C" & lastrow2 + 1).Value = Worksheets("IWKA_PLAN").Range("A3:A" & lastrow2).Value
    .Range("H4:H" & lastrow2 + 1).Value = Worksheets("IWKA_PLAN").Range("D3:D" & lastrow2).Value
    .Range("I4:I" & lastrow2 + 1).Value = Worksheets("IWKA_PLAN").Range("H3:H" & lastrow2).Value
     lastrow1 = Sheets("Gantt").Range("C48576").End(xlUp).Row
     
  
     
    
    '.Cells(lastrow1 + 1, 3).Value = Worksheets("NTS_PLAN").Range("A3:A" & lastrow3 + lastrow3).Value
    '.Cells(lastrow1 + 1, 8).Value = Worksheets("NTS_PLAN").Range("D3:D" & lastrow3 + lastrow3).Value
    '.Cells(lastrow1 + 1, 9).Value = Worksheets("NTS_PLAN").Range("H3:H" & lastrow3 + lastrow3).Value
  'Worksheets("Gantt").Range("I4:I" & lastrow1 + 1).Value = Worksheets("NTS_PLAN").Range("H3:H" & lastrow3).Value
 
  
   End With
   
Application.ScreenUpdating = True
End Sub
Sub gannt2()


  Application.ScreenUpdating = False
    
    
    lastrow3 = Worksheets("ADL_PLAN").Range("C48576").End(xlUp).Row
    
    With Sheets("Gantt")
    lastrow5 = Sheets("Gantt").Range("C48576").End(xlUp).Row
    .Range("C4:C" & lastrow3 + 1).Value = Worksheets("ADL_PLAN").Range("A3:A" & lastrow3).Value
    .Range("H4:H" & lastrow3 + 1).Value = Worksheets("ADL_PLAN").Range("D3:D" & lastrow3).Value
    .Range("I4:I" & lastrow3 + 1).Value = Worksheets("ADL_PLAN").Range("H3:H" & lastrow3).Value
     lastrow4 = Sheets("Gantt").Range("C48576").End(xlUp).Row
    


  End With
  Application.ScreenUpdating = True
End Sub

http://asset-9.soup.io/asset/6671/2313_9f32.xlsm
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
mentikk,

Perhaps something like this....
You will need to consider whether you will need to clear the existing Gantt data before running?

Code:
Sub Gantt()
  
    
    With Sheets("Gantt")
    Application.ScreenUpdating = False
    
    Plan = Array("IWKA_PLAN", "NTS_PLAN", "ADL_PLAN")  '<<<<<< Edit Sheets to transfer
    firstrow = 4
    For c = 0 To UBound(Plan)
    lastplanrow = Worksheets(Plan(c)).Range("C48576").End(xlUp).Row
    lastrow = firstrow + lastplanrow - 3
    .Range("C" & firstrow & ":C" & lastrow).Value = Worksheets(Plan(c)).Range("A3:A" & lastplanrow).Value
    .Range("H" & firstrow & ":H" & lastrow).Value = Worksheets(Plan(c)).Range("D3:D" & lastplanrow).Value
    .Range("I" & firstrow & ":I" & lastrow).Value = Worksheets(Plan(c)).Range("H3:H" & lastplanrow).Value
    firstrow = lastrow + 1
    Next c
      
  
   End With
   
Application.ScreenUpdating = True
End Sub

Hope that helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,949
Messages
5,599,025
Members
414,274
Latest member
LisaGreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top