Hi there,
I know this is going to be a big ask, but I am hoping someone here can provide some guidance to help solve my issue.
I have an Excel workbook that contains many worksheets. My goal is to pull data from each worksheet and put it into one of two summary worksheets.
More specifically, there are two variables I am interested in pulling, Variable 3 and Variable 7. Each worksheet has a cell (B2) that indicates whether the data belongs to either a control participant or an injured participant. I want to design a macro that will put the data from each sheet into the respective summary sheet, labelled either "Control" or "Injured", based on what is listed in Cell B2. And with each subsequent worksheet, I want the data to be put into the cells adjacent to the previous data in the appropriate summary sheet.
Lastly, I also want there to be a label for each variable, that combines the variable name with the subject's ID number found in Cell B1 for each worksheet.
I really hope this makes sense. I have created a mock-table to give an idea of what each worksheet looks like (I made three), and what I want the summary worksheets to look like (one for control and one for injured).
Below is what I have pieced together thus far (obviously not finished), but I am really just shooting for the stars at this point. Not even sure if I am on the right path, as this is my first foray into Excel Macros.
Sub controldata()
Dim Sht As Worksheet
Dim Cell_txt As String
Dim Cell_lbl_1 As String
Dim Cell_lbl_2 As String
For each Sht In ActiveWorkbook.Worksheets
Cell_txt = Range(“B2”).Text
Cell_lbl_1 = “=Concatenate(“F1”,B1)”
Cell_lbl_2 = “=Concatenate(“J1”,B1)”
If InStr(1, celltxt, “Cont”) Then
Sht.Select
Range(“F3:F33”).Copy Sht(“Control”).Range(“A2:A32”)
Range(“J3:J33”).Copy Sht(“Control”).Range(“B2:B32”)
Sht(“Control”).Range(“A1”) = Cell_lbl_1
Sht(“Control”).Range(“B1”) = Cell_lbl_2
ElseIf InStr(1, celltxt, “Inj”) Then
End If
End Sub
Thanks for any help you can provide!
Bookmarks