Don here is a part of it. I left out all the other cases to keep the
post smaller.
Also I have this as a command button code but I would love to have it
as a module and have the requesting sheet be the active sheet.
Thanks
Steve
Private Sub CommandButton2_Click()
Dim R, C As Integer
Dim client_contact, client, project, project_number, boring, depth,
sample, lab_Id, test, date_rec As String
client = Range("C4")
project = Range("c5")
project_number = Range("p11")
date_rec = Range("ak12")
Worksheets("input").Range("b3..b6").Value = "" 'clear range
Worksheets("input").Range("b10..k13").Value = "" 'clear range
Worksheets("input").Range("b3").Value = client
Worksheets("input").Range("b4").Value = project
Worksheets("input").Range("b5").Value = project_number
Worksheets("input").Range("b6").Value = date_rec
For C = 6 To 38 ' Scroll thru all the tests
If Cells(33, C).Value > 0 Then ' Check to see if any tests for this
project
z = 2
For R = 19 To 28
If Cells(R, C).Value <> "" Then
Worksheets("input").Cells(10, z).Value = Cells(R, 2).Value '
insert boring
Worksheets("input").Cells(11, z).Value = Cells(R, 3).Value '
insert depth
Worksheets("input").Cells(12, z).Value = Cells(R, 4).Value '
insert sample
Worksheets("input").Cells(13, z).Value = project_number &
Cells(R, 1).Value ' insert lab id number
z = z + 1
End If
Next R
Select Case C
Case 6
Sheets("Water Content").Visible = True ' Note I just started to go thru
and add all these statements.
Worksheets("Water Content").PrintOut
Sheets("Water Content").Visible = False ' Note I just started to go
thru and add all these statements.
Case 7
Sheets("Limit").Visible = True ' Note I just started to go thru and add
all these statements.
Worksheets("Limit").PrintOut
For s = 3 To Cells(33, C).Value + 1
Worksheets("input").Cells(10, 2).Value = Worksheets("input").Cells(10,
s).Value ' insert boring
Worksheets("input").Cells(11, 2).Value = Worksheets("input").Cells(11,
s).Value ' insert Depth
Worksheets("input").Cells(12, 2).Value = Worksheets("input").Cells(12,
s).Value ' insert Sample
Worksheets("input").Cells(13, 2).Value = Worksheets("input").Cells(13,
s).Value ' insert Lab Sample No.
Worksheets("Limit").PrintOut
Next s
Sheets("Limit").Visible = False ' Note I just started to go thru and
add all these statements.
Case 8
Sheets("Shrinkage").Visible = True ' Note I just started to go thru and
add all these statements.
Worksheets("shrinkage").PrintOut
Sheets("Shrinkage").Visible = False ' Note I just started to go thru
and add all these statements.
End Select
End If
Next C
End Sub