Copy-paste data frm individual spreadsheets into single Consolidated spreadsheet - Issues

[NametobeRenamed]

Hello Excel and VBA Experts,

Before letting you know my problem and my requirements. I will explain the background of the attached workbook.

Background
Attached workbook contains 5 worksheets with name Project 1, Project 2 .... Project 5 (this is just a sample workbook, but my actual workbook contains 50+).

Requirement:
I want data from each of these Project worksheets to be dumped into one single spreadsheet and I have this as ConsolidatedData.

Problem Statement:
Please look attached screenshot to quickly relate to what I say below.

Screenshot1 is my Project-1 and Screenshot is my Project-2. If you see these two screenshots, Columns A to E (A3 to E3) remains same whereas if you further see from Column-F (Cell F3) there is difference. This is because each project is going to have a different start date (month) and duration (end month). But I want data from each these projects to filled into subsequent matching cells in ConsolidatedData worksheet. Please refer Screenshot-3 for details.

Screenshot-1 (Project-1)
Screenshot-6.PNG

Screenshot-2 (Project-2)
Screenshot-7.PNG

Screenshot-3 (ConsolidataData Worksheet)
Screenshot-8.PNG

Note: Number of sheets in the workbook is not constant. At this time it is 5 and later it can extend to 10, 17, 22, 30 etc.

[Marc L]

According to your missing result elaboration and to your missing expected result within your attachment
an Excel basics VBA demonstration for starters to paste only to ConsolidatedData worksheet module
so working only with good enough readers :

Sub Demo1() 
    Dim S&, C, R& 
        UsedRange.Offset(1).Clear 
        Application.ScreenUpdating = False 
    For S = Index + 1 To Worksheets.Count 
    With Worksheets(S).[A1].CurrentRegion.Rows 
    If .Count > 3 Then 
        C = Application.Match(.Range("F3"), UsedRange.Rows(1), 0) 
     If IsNumeric(C) Then 
        R = UsedRange.Rows.Count + 1 
       .Item("4:" & .Count).Columns("A:E").Copy Cells(R, 1) 
       .Item("4:" & .Count).Columns(6).Resize(, .Columns.Count - 5).Copy Cells(R, C) 
     End If 
    End If 
    End With 
    Next 
        UsedRange.Borders.Weight = 2 
        Application.ScreenUpdating = True 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[NametobeRenamed]

Thanks Marc and really appreciate taking time a writing a code for me!
Unfortunately, it is not working as expected. First time when I run the script it copy-pasted values to ConsolidatedData worksheet correctly. Then after I did a test, I have changed the starting month in Project-1 Worksheet and when I run the script it is pasting values in different month and different year and also it is not copy-pasting from all the worksheets. Not sure where this needs to be refined but can you please check?

[jindon]

See if this is how you wanted.

Please Login or Register  to view this content.

[Marc L]

Not sure where this needs to be refined but can you please check?

Nothing to refine as it well works on my side, I can't reproduce any issue with your attachment even when changing the startin' month
so the bad in on your side …

Edit : all woksheets after ConsolidatedData are included if their first month matches with ConsolidateData headers row.

[NametobeRenamed]

Thank you Jindon again and this script is working super perfect for the sample spreadsheet which I have!!

I tested with my actual spreadsheet (which I cannot share it here) I am seeing some difficulties because of Naming of Worksheet. In the script which you have shared, I see a line the code which mentions,

Please Login or Register  to view this content.

and this is causing some issues.


In the sample spreadsheet, I have worksheet names as Project-1, Project-2, etc. and so script is working great. But in the actual spreadsheet the names will be very different. In the actual workbook, I will be having around 50 projects/worksheets with different names. Apart from these 50 worksheets, I'll be having another 5-6 worksheets which contains another data and these needs to be excluded.

I replaced

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and I am not seeing desired results.

I want these sheets which are with the name (Roles&Dropdowns, OtherDataDump, Summary, Consolidated Data, Project-SampleTemplate) to be excluded and script should run for the remaining.

I have another code (pasting it below) where it pulls all required Worksheet names. Can you modify your code saying to take names from 'OtherDatadump' worksheet from D2 to Last cell in D. And wherever the name matches the scirpt which you have written should be executed OR WHATEVER THE SIMPLIEST WAY FOR YOU AS YOU ARE THE EXPERT. ALL I WANT ABOVE MENTIONED SHEETS SHOULD BE EXCLUDED .

Please Login or Register  to view this content.

[Marc L]

As the simpliest way is what you have done with your attachment :
all data worksheets whatever their names after ConsolidatedData worksheet so the VBA way to go is still within post #2 …

[jindon]

When sheet names differs like "Consolidated Data" & "ConsoldatedData", you must change it for yourself.

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[NametobeRenamed]

For some reason when I replaced this code in my actual workbook it is not working as expected. For this reason and also to avoid further confusion, I am attaching my actual workbook. I removed the data which I don't want to get shared and I have replaced it with DUMMY data.

Hello Jindon,
Quick and last request: Can you please check this workbook (RP Tool_Sharing Copy) and make necessary changes in this spreadsheet?
I have placed your code in Module-3

Again, the requirement being:
From the attached workbook, data should flow from all spreadsheets to 'Consolidate Data' worksheet.

But it should exclude data from these Worksheets - "Roles&Dropdowns", "Other Datadump", "Summary", "Project-Sampletemplate" and "Sheet2"

[INDENT][I]Whereas data should from "Alpha Beta" and "Gamma Delta" worksheets. For now, these are these are the only two worksheets I have but later this workbook going to have many worksheets (50+) whereas needs to be flowed to Consolidate Data worksheet.

Requirement-2 (Nice to have only).
In the Consolidated Data worksheet, Range(G2 to BG-Last Cell; For Last Cell, Row Count can be taken from Column-A last cell. In this Range wherever the cells are blank after running above script instead of blank cells I want these to be filled with zeros. So that it will be easy for me to create PivotTables later on.

Thanks in advance Jindon once when this is done, I'll be closing the loop.

[MatrixMan]

The easier way to include only the real project sheets and exclude everything else is to cycle through all the sheets and look for the formula "=EDATE($E1,COLUMN(A1)-1)" in row 3; if it exists, it's a project sheet, if it doesn't it's not. You just exclude the template and then you don't have to worry about sheet names changing or new ones being added. Another way to do it is to give proper codenames to the non-project sheets and then test if the codename starts with "Sheet" or not; if it does, it's a project sheet.

[MatrixMan]

For requirement 2 ... if the reason for that is simply to show zeroes in your pivot tables, you can do this in the options:

1. first go to field settings and check the box that says "Show items with no data", and
2. go to pivot table options and check the box that says "For empty cells show:" ... and enter 0 in the box.

[MatrixMan]

Modifying Jindon's code that you said works except for the sheets issue and using the first solution option in reply 10 you have this:

Please Login or Register  to view this content.

[NametobeRenamed]

Thanks MatrixMan and appreciate for taking time and looking into the code!

But I see again running into the same issue:

Data not just flowing from the worksheets that I want (For example: 'Alpha Beta', 'Gamma Delta'

But it is also dumping data from unwanted sheet/s, for example I see data from worksheet 'Project-SampleTemplate' is also coming to 'ConsolidatedData' Spreadsheet. This is not the expected behavior'

[MatrixMan]

Oops - I forgot to exclude the template:

Please Login or Register  to view this content.

[NametobeRenamed]

Thank you very much MatrixMan and Jindon!!
I really appreciate both your help. I am going to close this thread.

[jindon]

Change to

Please Login or Register  to view this content.

[NametobeRenamed]

Hello Jindon and MatrixMan!!

I have reviewed the Workbook and the contents that I prepared with your help with my Boss. He liked the worked but asked for one more thing with which I think this script need to be modified a little bit. For this reason, I have reopened this thread.

New Requirement: From each Worksheet now we also need to bring corresponding 'Project Name', 'Start Date', 'Duration' and 'Project Manager' values to ConsolidatedData Spreadsheet.

Project Name: Cell B1, Start Date: E1, Duration: G1 and Project Manager: K1 from each spreadsheet and the final results should like below screenshot.

Sincere request - Can you to please modify the script accordingly and provide it to me? Thanks in advance!!

Screenshot-10.PNG

[MatrixMan]

I rewrote this in a longer form than jindon .. I don't have his knack for short-hand coding I guess! But you might find this easier to follow; I've basically split the data collection into 3 parts and commented in the code what it's doing.

Please Login or Register  to view this content.

It's in the attached workbook in the modMM module.

You'll just need to add a button or trigger it however you want to do that and there's some error-trapping to be done as well, such as for when a date isn't found and so on.

Hope that helps. MM.

[NametobeRenamed]

This is fantastic and thank you very much sir!!
As this is resolved, I am closing this thread.