Excel Question

[jaikin]

In my continuing quest to use excel for saving QA data for a contact
center, I have a scoring sheet called "observations" that supervisors
can use for scoring a call.
At the end of the sheet is a button that saves the data to anotehr
sheet in the same workbook called "Sheet1".
Thanks to several people - including Pers for their help with that
Here is the code that does this:

Private Sub CommandButton1_Click() 
Dim ShA As Worksheet 
Dim ShB As Worksheet 
Dim TargetRange1 As Range 
Dim TargetRange2 As Range 
Dim TargetRange3 As Range 
Dim TargetRange4 As Range 
Dim TargetRange5 As Range 
Dim TargetRange6 As Range 
Dim TargetRange7 As Range 
Dim TargetRange8 As Range 
Dim TargetRange9 As Range 
Dim TargetRange10 As Range 
Dim TargetRange11 As Range 
Dim TargetRange12 As Range 
Dim TargetRange13 As Range 
Dim TargetRange14 As Range 
Dim TargetRange15 As Range 
Dim TargetRange16 As Range 
Dim TargetRange17 As Range 
Dim TargetRange18 As Range 
Dim TargetRange19 As Range 
Dim TargetRange20 As Range 
Dim TargetRange21 As Range 
Dim TargetRange22 As Range 
Dim TargetRange23 As Range 
Dim CopyToCell As Range 
Set ShA = Worksheets("Observation") 
Set ShB = Worksheets("Sheet1") 
With ShA 
    Set TargetRange1 = .Range("C5:C11,C19:C25") 
    Set TargetRange2 = .Range("D18:F18") 
    Set TargetRange5 = .Range("C27:C34") 
    Set TargetRange6 = .Range("D26:F26") 
    Set TargetRange9 = .Range("C36:C44") 
    Set TargetRange10 = .Range("D35:F35") 
    Set TargetRange13 = .Range("C46:C49") 
    Set TargetRange14 = .Range("D45:F45") 
    Set TargetRange17 = .Range("C51:C52") 
    Set TargetRange18 = .Range("D50:F50") 
    Set TargetRange21 = .Range("C53, E53:F53") 
End With 
If ShB.Range("A2") = "" Then ' Headings in row 1 
    Set CopyToCell = ShB.Range("A2") 
Else 
    Set CopyToCell = ShB.Range("A1").End(xlDown).Offset(1, 0) 
End If 
TargetRange1.Copy 
CopyToCell.PasteSpecial xlPasteValues, , , Transpose:=True 
TargetRange2.Copy 
CopyToCell.Offset(0, 14).PasteSpecial xlPasteValues 
TargetRange5.Copy 
CopyToCell.Offset(0, 17).PasteSpecial xlPasteValues, , , 
Transpose:=True 
TargetRange6.Copy 
CopyToCell.Offset(0, 25).PasteSpecial xlPasteValues 
TargetRange9.Copy 
CopyToCell.Offset(0, 28).PasteSpecial xlPasteValues, , , 
Transpose:=True 
TargetRange10.Copy 
CopyToCell.Offset(0, 37).PasteSpecial xlPasteValues 
TargetRange13.Copy 
CopyToCell.Offset(0, 40).PasteSpecial xlPasteValues, , , 
Transpose:=True 
TargetRange14.Copy 
CopyToCell.Offset(0, 44).PasteSpecial xlPasteValues 
TargetRange17.Copy 
CopyToCell.Offset(0, 47).PasteSpecial xlPasteValues, , , 
Transpose:=True 
TargetRange18.Copy 
CopyToCell.Offset(0, 49).PasteSpecial xlPasteValues 
TargetRange21.Copy 
CopyToCell.Offset(0, 52).PasteSpecial xlPasteValues 
Application.CutCopyMode = False 
End Sub

That is all working - and perfectly
Now that I'm ready to actually make use of the sheet, I would like the
data saved to a workbook on a shared network drive so that every
supervisor can have a copy of the sheet running at their desk, and
when they click the save button, instead of saving locally, it saves
the data to:
s:\qadata\data.xls on sheet1
I've been making changes to this line:
Set ShB = Worksheets("Sheet1")
But have so far been unsuccessful.
I'm really struggling with the syntax to do this - any suggestions??

[clintfoot]

Wow, thanks so much for sharing this. I'm gonna save this one, because I know I might be needing this sometime.

[Charles Alvear]

Originally Posted by jaikin

I would like the
data saved to a workbook on a shared network drive so that every
supervisor can have a copy of the sheet running at their desk, and
when they click the save button, instead of saving locally, it saves
the data to:
s:\qadata\data.xls on sheet1
I've been making changes to this line:
Set ShB = Worksheets("Sheet1")
But have so far been unsuccessful.
I'm really struggling with the syntax to do this - any suggestions??

I use some formulas in one of my spreadsheet to read data from an external file on our network.
Try this syntax:
Set ShB = Worksheets('S:\qadata\[data.xls]Sheet1')