extract data from 3 sheets into one table with 3 variables to display sheet name ?

[chloev]

Hello,
First post and i'm a scientist, with no programming experience so by all means tell me if i am being unreasonable with my expectations or if i am being incredibly stupid !
I have a workbook containing with 3 reports
Each report represents a status of a type of blood sample (1 per row), so one sample type (3 column variables) will only ever appear on 1 of the 3 sheets.

I want to create a large table that pulls columns C, E & I from each row with a fourth column telling me which sheet is has pulled from- so essentially a big list of 3 columns that are common to all reports, plus am additional column which tells me which report it has come from

This will give me the status of each sample and then I should be able to get summary data from this large table.
Does that make sense? And is it possible?
I have looked through lots of the amazing you tube videos related to this site but can’t seem to find anything that would work.
Another important thing is that the reports will be updated monthly with more rows added.
I would appreciate any help and even just to point me in the direction of what to put into youtube search!!
Thanks so much
From an amateur!!
i don't know if you can see my screen shots?

 

[chloev]

I also cant even work out how to get my image in here

 

[WarPigl3t]

Sounds easy. See if this is what you want.

Sub myMacro()
     'You said you have 3 reports aka 3 sheets.  myReports(2) has 3 elements because it starts with 0.  Name each sheet below.
     Dim myReports(2) As String
     myReports(0) = "Sheet1"
     myReports(1) = "Sheet2"
     myReports(2) = "Sheet3"

     'This part will create a new sheet and call it by today's date.  
     myDate = Date()
     myDateFormatted = Replace(myDate, "/", "_")
     Sheets.Add.Name = myDateFormatted

     'This part will copy everything from your 3 reports and paste it into the newly created sheet.  Also it will locate the last column and insert a new column where it will say what sheet the data came from.
     'Start by inserting headers.
     Sheets(myDateFormatted).Activate
     Sheets(myDateFormatted).Select
     Sheets(myReports(0)).Rows(1).Copy
     Sheets(myDateFormatted).Rows(1).xlPasteSpecial PasteValues
     Application.CutCopyMode = False
     'Now copy paste everything from each sheet.
     lastColumn = Sheets(myDateFormatted).Cells(1, Columns.Count).End(xlToLeft).Column + 1
     For Each element in myReports
          Sheets(element.Name).Cells.Copy
          pasteRow = Sheets(myDateFormatted).Range("C" & Rows.Count).End(xlUp).Row + 1
          Sheets(myDateFormatted).Rows(printRow).xlPasteSpecial PasteValues
          Application.CutCopyMode = False
          Sheets(myDateFormatted).Rows(pasteRow).Delete
          'Last column will say what sheet the data came from
          Do Until pasteRow > Sheets(myDateFormatted).Range("C" & Rows.Count).End(xlUp).Row
               Sheets(myDateFormatted).Cells(pasteRow, lastColumn).Value = element.Name
               pasteRow = pasteRow + 1
          Loop
     Next element
End Sub

Now I want something from you. Answer this honestly for me. Scientifically speaking, if you pass through the event horizon of a worm hole while in hyperspace, how many cows does it take to make a chicken sandwich if the chicken had cow for dinner?

 

[chloev]

You are a wonderful wonderful Person- one quick question- what about if i only want to copy certain rows from each sheet? The sheets aren't strictly identical but they have 3 common fields which i want to copy?

I realize you are all scientists here, what i really meant was i'm a Biologist, AKA someone who relies on real scientists to crack all the difficult problems! but im going to guess the answer to your problem is 0.

Chloe

Sounds easy. See if this is what you want.

Sub myMacro()
     'You said you have 3 reports aka 3 sheets.  myReports(2) has 3 elements because it starts with 0.  Name each sheet below.
     Dim myReports(2) As String
     myReports(0) = "Sheet1"
     myReports(1) = "Sheet2"
     myReports(2) = "Sheet3"

     'This part will create a new sheet and call it by today's date.  
     myDate = Date()
     myDateFormatted = Replace(myDate, "/", "_")
     Sheets.Add.Name = myDateFormatted

     'This part will copy everything from your 3 reports and paste it into the newly created sheet.  Also it will locate the last column and insert a new column where it will say what sheet the data came from.
     'Start by inserting headers.
     Sheets(myDateFormatted).Activate
     Sheets(myDateFormatted).Select
     Sheets(myReports(0)).Rows(1).Copy
     Sheets(myDateFormatted).Rows(1).xlPasteSpecial PasteValues
     Application.CutCopyMode = False
     'Now copy paste everything from each sheet.
     lastColumn = Sheets(myDateFormatted).Cells(1, Columns.Count).End(xlToLeft).Column + 1
     For Each element in myReports
          Sheets(element.Name).Cells.Copy
          pasteRow = Sheets(myDateFormatted).Range("C" & Rows.Count).End(xlUp).Row + 1
          Sheets(myDateFormatted).Rows(printRow).xlPasteSpecial PasteValues
          Application.CutCopyMode = False
          Sheets(myDateFormatted).Rows(pasteRow).Delete
          'Last column will say what sheet the data came from
          Do Until pasteRow > Sheets(myDateFormatted).Range("C" & Rows.Count).End(xlUp).Row
               Sheets(myDateFormatted).Cells(pasteRow, lastColumn).Value = element.Name
               pasteRow = pasteRow + 1
          Loop
     Next element
End Sub

Now I want something from you. Answer this honestly for me. Scientifically speaking, if you pass through the event horizon of a worm hole while in hyperspace, how many cows does it take to make a chicken sandwich if the chicken had cow for dinner?

 

[WarPigl3t]

Well first I'd have to understand which rows and more importantly why those rows. The why is what I'll use in the coding logic.

 

[Peter_SSs]

I also cant even work out how to get my image in here

See my signature block below for info about posting small screen shots.
You can't display an image that is stored on your own computer.

I'm assuming that before running this code, you manually create a 'Summary' sheet and enter the four headings in columns A:D.

Another important thing is that the reports will be updated monthly with more rows added.

This isn't clear to me so may need some further changes later.
When you process the data next time, will something new be added to the bottom of the existing data in the Summary worksheet, or will the whole summary sheet data be created again from the 3 original sheets?

Anyway, after creating & naming the 'Summary' sheet in a copy of your workbook, give this code a try.

Sub Combine()
  Dim aRws
  Dim ws As Worksheet, wsSumm As Worksheet
  Dim nr As Long, LR As Long
  
  Set wsSumm = Sheets("Summary")
  nr = wsSumm.Cells.Find(What:="*", After:=wsSumm.Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious, SearchFormat:=False).Row + 1
  For Each ws In Worksheets
    If ws.Name <> wsSumm.Name Then
      With ws
        LR = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious, SearchFormat:=False).Row
        aRws = Evaluate("row(2:" & LR & ")")
        wsSumm.Cells(nr, 1).Resize(UBound(aRws), 3).Value = Application.Index(.Cells, aRws, Array(3, 5, 9))
        wsSumm.Cells(nr, 4).Resize(UBound(aRws)).Value = .Name
        nr = nr + UBound(aRws)
      End With
    End If
  Next ws
End Sub

 

[chloev]

Well first I'd have to understand which rows and more importantly why those rows. The why is what I'll use in the coding logic.

sorry i meant columns! not rows!

 

[WarPigl3t]

I'll let Peter help you on this one because my method is slow due to that I use loops to get each cell value. Peter will give you code that pastes the whole column at once. If he doesn't help, message me and I'll write my way.

 

[chloev]

Hi,
Ok so I have had a try and failed with both suggestions- Its clearly down to my explanation and I can’t seem to get the addin to work either (possibly due to this being my work laptop)
Ok so here goes again
This is my excel workbook
Sheet 1: Pending Analysis Viracore
Sheet 2: BSM Discrepancy
Sheet 3: BSM awaiting shipment
Sheet 4: Resulted
Sheet 5: Summary
Sheets 1,2,3,4 are the 4 raw data sets.
Sheet 5 contains 4 columns with the following headers
A: Subject ID
B: CDMCPE *this is the chemo cycle*
C: Sample type
D: Sheet name
Columns A,B,C are common to sheets 1-4
Sheets 1-4 will be updated/replaced monthly – some rows will disappear and some will appear
I would like in sheet 5 to extract the information from sheets 1-4 for the common columns and also in column D tell me which sheet it has come from.
This will give me the status of each sample without having to look for it on sheet.
Im not sure if bigger picture info helps but here is:
There are 300 subjects on our clinical trial. Each subject has multiple tests at multiple time points and each of these samples goes off on a journey to different checkpoints before we have a result.
We receive an excel spreadsheet each month from each checkpoint. Each sheet tells us what is at their checkpoint. We need summary data of everything, and also the ability to drill down. So my thought is if I get it all onto one table with the “checkpoint” in a column, then I can pivot it and also make charts and things that can be viewed periodically. This may be a silly line of thought so let me know if anyone has any other thoughts? Thanks so much so Far !
Chloe

 

[Peter_SSs]

Hi,
Ok so I have had a try and failed with both suggestions-

In relation to my code ..
In what way did it fail?
- Error message? If so, details please.
- It did nothing?
- It copied data to the Summary sheet but it was the wrong data? If so, details please.
- Something else? If so, details please.

Columns A,B,C are common to sheets 1-4

Are the source columns still columns C, E & I on each of the sheets 1-4?