Posting Data to Summary Tab

[Cdmassey987]

I have a spreadsheet with 10 tabs, not all tabs have data, but those that do, I am moving specific information to a summary tab. Identifying data all goes in the same two columns. That part was easy. However, numerical data consisting of 4 columns must also be moved to the summary tab, but it doesn't go into the same columns as the previous tab's numerical data (i.e. tab 1 numerical data goes in columns D-G and tab 2 numerical data goes in I-L and so on. However, how do I get the numerical data to post in the correct corresponding row as the relevant identification data?

I've tried offsets, but if the prior tab is blank that doesn't work. I tried doing a count of rows in conjunction with an offset but that didn't work. I tried just pulling ID data and using index match to pull the relevant data to the summary tab, but that resulted in duplication of numerical data if identifying data is listed multiple times over the multiple tabs.

I'm using a Macro to identify pages that are blank and skip the copy and paste process for that tab. Essentially it identifies the necessary columns on a tab and copies and paste to the summary, I just can't get the paste of numerical data to sync with the corresponding ID data from that tab.

 

[hiker95]

Cdmassey987,

It would help if we could see your actual raw data workbook/worksheets. And, can we see what the results (manually formatted by you) should look like?

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Cdmassey987]

below find a shared link. Essentially what I am trying to do is get all the data into a format that can be easily reviewed to ensure that there is no over awarding of funds/credits etc....
Code so Far

Sub UpdateSummary()'Clear Old Data
    Sheets("Summary").Select
    Range("A3:B1000").ClearContents
    Range("D3:G1000").ClearContents
    Range("I3:L1000").ClearContents
    Range("N3:Q1000").ClearContents
    Range("S3:V1000").ClearContents
    Range("N3:Q1000").ClearContents
    Range("X3:AB1000").ClearContents
    Range("AD3:AG1000").ClearContents
    Range("N3:Q1000").ClearContents
    Range("AI3:AL1000").ClearContents
    Range("AN3:AN1000").ClearContents
    Range("AO3:AO1000").ClearContents
     
'Paste Data From Fellowships to Summary
    'Names & SUIDs
    Sheets("Fellowships").Select
    If Range("A3") = "" Then GoTo Traineeships
    LastRow = Range("A1000").End(xlUp).Row
    Range("A3:B3", Cells(LastRow, "A")).Copy
    Sheets("Summary").Select
    Range("A1000").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    'Semester Breakdown
    Sheets("Fellowships").Select
    LastRow = Range("D1000").End(xlUp).Row
    Range("D3:G3", Cells(LastRow, "D")).Copy
    Sheets("Summary").Select
    Range("D1000").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste


'Paste Data From Traineeship to Summary
Traineeships:
    'Names & SUIDs
    Sheets("Traineeship").Select
    If Range("A3") = "" Then GoTo OutsideGAs
    LastRow = Range("A1000").End(xlUp).Row
    Range("A3:B3", Cells(LastRow, "A")).Copy
    Sheets("Summary").Select
    Range("A1000").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    'Semester Breakdown
    Sheets("Traineeship").Select
    LastRow = Range("G1000").End(xlUp).Row
    Range("G3:J3", Cells(LastRow, "G")).Copy
    Sheets("Summary").Select
    Range("G1000").End(xlUp).Offset(1, 2).Select
    ActiveSheet.Paste
    
'Paste Data From Outside GAs to Summary
OutsideGAs:
    'Names & SUIDs
    Sheets("Outside GAs").Select
    If Range("A3") = "" Then GoTo Sheet0049
    LastRow = Range("D1000").End(xlUp).Row
    Range("D3:E3", Cells(LastRow, "D")).Copy
    Sheets("Summary").Select
    Range("A1000").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    'Semester Breakdown
    Sheets("Outside GAs").Select
    LastRow = Range("J1000").End(xlUp).Row
    Range("J3:M3", Cells(LastRow, "J")).Copy
    Sheets("Summary").Select
    Range("N1").Offset(Col1, 0).Select
    ActiveSheet.Paste


'Paste Data From 0049 to Summary
Sheet0049:
    'Names & SUIDs
    Sheets("00049").Select
    If Range("A3") = "" Then GoTo GradAwards
    LastRow = Range("A1000").End(xlUp).Row
    Range("A3:B3", Cells(LastRow, "A")).Copy
    Sheets("Summary").Select
    Range("A1000").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    'Semester Breakdown
    Sheets("Outside GAs").Select
    LastRow = Range("J1000").End(xlUp).Row
    Range("J3:M3", Cells(LastRow, "J")).Copy
    Sheets("Summary").Select
    Range("G1000").End(xlUp).Offset(1, 7).Select
    ActiveSheet.Paste
    
GradAwards:

My idea was to copy and paste everything into the summary tab and then throw it into a pivot table and while you can do this manually, the problem with automating it is that I can't get the data rows to offset properrly....tride using data model idea to and that didn't work (combine multiple tables into a single pivot).

https://drive.google.com/file/d/125gRmDI59308O3KQsHL14LxC2vOb2yeF/view?usp=sharing

you can view the spreadsheet but not manipulate via the link.

 

[hiker95]

My idea was to copy and paste everything into the summary tab and then throw it into a pivot table

Cdmassey987,

I have no experience with Pivot Tables.

Maybe someone else on MrExcel will be able to solve your request.

 

[Cdmassey987]

so its not so much a pivot table issue as getting all the info on one tab issue. If you look at the code I am taking identifying data from multiple tabs and compiling it into adjacent columns A & B. This identifying data relates to specific numerical data in the other columns. I attempted using an index match to pull the data, the problem is that a identifying data may be listed more than once in column A and B so I would have duplication happening. If I copy and paste the data from the tabs my issue then is not duplication of numerical data, but positioning of that data in the corresponding row to the identifying data. ie Identifier in ROW 1 corresponds to Numerical Data posted in columns D:H.

Once I add data from a second and third tab is where I begin to have an issue. How do I get the numerical data to post in the proper row across from identifying data using VBA. I could do the process manually, but the spreadsheet could change on a hourly/daily/weekly/monthly basis and I wanted to automate a process through which we could quickly look at the data for 1 corresponding identifier across all the tabs of the spreadsheet.

Again I have tried offset, but there is no guarantee that a specific column/cell/row will be filled. I tried making tables of the data ranges on each tab and then creating a relationship between the tables in a pivot table, but I think I may be doing it wrong source for that idea. Besides manually, copying and pasting this data from each tab to the summary I think I may be out of ideas....