I am putting together a quality assurance sheet for a contact center.

The sheet works fine right now to score calls, etc., but the next step
is to save the data somewhere.

So, is there a relatively simple way to save the data into rows on
another sheet?

In more detail, what I'm hoping to do is add a "save" button to the
bottom of the scoring sheet that does the following:

Copies (for example) cells c1, c2, c3, c4, c5, d7, e7 and e11 to a
second sheet and puts that data in rows so that row 1 would look like
this:
Cell A1 contains the data from sheet1, cell c1.
Cell B1 contains the data from seeht 1 cell c2

and so on.

Then after copying the data over, the save button would reset sheet 1.

I understand that this will probably involve VB or something, and
don;t expect you to do it for me, but if you know of examples online,
links to those examples would be fantastic.

Thank you

for i=1 to 5
cells(i,"c").copy sheets("dest").cells(i,"c")
next i


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John Aikin" wrote in message
...
I am putting together a quality assurance sheet for a contact center.

The sheet works fine right now to score calls, etc., but the next step
is to save the data somewhere.

So, is there a relatively simple way to save the data into rows on
another sheet?

In more detail, what I'm hoping to do is add a "save" button to the
bottom of the scoring sheet that does the following:

Copies (for example) cells c1, c2, c3, c4, c5, d7, e7 and e11 to a
second sheet and puts that data in rows so that row 1 would look like
this:
Cell A1 contains the data from sheet1, cell c1.
Cell B1 contains the data from seeht 1 cell c2

and so on.

Then after copying the data over, the save button would reset sheet 1.

I understand that this will probably involve VB or something, and
don;t expect you to do it for me, but if you know of examples online,
links to those examples would be fantastic.

Thank you

You are correct, you will need VB. However, a good start, if not the
solution, would be to record the macro yourself. Simply start recording,
perform the copy& paste operations, and then save. Stop recording.

If you want to review/edit the actual coding, you can call up the editor
(Alt+F11) and your macro will be located in a module. (This is handy for
removing extraneous coding such as window scrolls, extra cell selects, etc.)

Also, to prevent "screen flashing" I'd add the following lines to beginning
of code

Application.ScreenUpdating = False

'and at the end, its counterpart:
Application.ScreenUpdating = True

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"John Aikin" wrote:

I am putting together a quality assurance sheet for a contact center.

The sheet works fine right now to score calls, etc., but the next step
is to save the data somewhere.

So, is there a relatively simple way to save the data into rows on
another sheet?

In more detail, what I'm hoping to do is add a "save" button to the
bottom of the scoring sheet that does the following:

Copies (for example) cells c1, c2, c3, c4, c5, d7, e7 and e11 to a
second sheet and puts that data in rows so that row 1 would look like
this:
Cell A1 contains the data from sheet1, cell c1.
Cell B1 contains the data from seeht 1 cell c2

and so on.

Then after copying the data over, the save button would reset sheet 1.

I understand that this will probably involve VB or something, and
don;t expect you to do it for me, but if you know of examples online,
links to those examples would be fantastic.

Thank you

Hi

Add a button from the Control Toolbox menu on sheet 1, rightclick the
button and view code and paste the code below:

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 CopyToCell As Range

Set ShA = Worksheets("Sheet1")
Set ShB = Worksheets("Sheet2")
With ShA
Set TargetRange1 = .Range("C1:C5")
Set TargetRange2 = .Range("D7:E7")
Set TargetRange3 = .Range("E11")
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, 5)
TargetRange3.Copy
CopyToCell.Offset(0, 7).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange1.ClearContents
TargetRange2.ClearContents
TargetRange3.ClearContents
Application.CutCopyMode = False

End Sub

The macro require headings in row 1 of Sheet 2, data will be pasted to A2
etc. Exit design mode, and give it a try.

Hopes this helps.
....
Per



"John Aikin" skrev i meddelelsen
...
I am putting together a quality assurance sheet for a contact center.

The sheet works fine right now to score calls, etc., but the next step
is to save the data somewhere.

So, is there a relatively simple way to save the data into rows on
another sheet?

In more detail, what I'm hoping to do is add a "save" button to the
bottom of the scoring sheet that does the following:

Copies (for example) cells c1, c2, c3, c4, c5, d7, e7 and e11 to a
second sheet and puts that data in rows so that row 1 would look like
this:
Cell A1 contains the data from sheet1, cell c1.
Cell B1 contains the data from seeht 1 cell c2

and so on.

Then after copying the data over, the save button would reset sheet 1.

I understand that this will probably involve VB or something, and
don;t expect you to do it for me, but if you know of examples online,
links to those examples would be fantastic.

Thank you

Thank you Per - you rock.

I hope you will be patient for just a bit longer, your code worked
great, but I obviously did something wrong when I tried to expand it.

Here is the code I'm trying to use:

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")
Set TargetRange3 = .Range("E18")
Set TargetRange4 = .Range("F18")
Set TargetRange5 = .Range("C27:C34")
Set TargetRange6 = .Range("D26")
Set TargetRange7 = .Range("E26")
Set TargetRange8 = .Range("F26")
Set TargetRange9 = .Range("C36:C44")
Set TargetRange10 = .Range("D35")
Set TargetRange11 = .Range("E35")
Set TargetRange12 = .Range("F35")
Set TargetRange13 = .Range("C46:C49")
Set TargetRange14 = .Range("D45")
Set TargetRange15 = .Range("E45")
Set TargetRange16 = .Range("F45")
Set TargetRange17 = .Range("C51:C52")
Set TargetRange18 = .Range("D50")
Set TargetRange19 = .Range("E50")
Set TargetRange20 = .Range("F50")
Set TargetRange21 = .Range("C53")
Set TargetRange22 = .Range("E53")
Set TargetRange23 = .Range("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
TargetRange3.Copy
TargetRange4.Copy
TargetRange5.Copy
TargetRange6.Copy
TargetRange7.Copy
TargetRange8.Copy
TargetRange9.Copy
TargetRange10.Copy
TargetRange11.Copy
TargetRange12.Copy
TargetRange13.Copy
TargetRange14.Copy
TargetRange15.Copy
TargetRange16.Copy
TargetRange17.Copy
TargetRange18.Copy
TargetRange19.Copy
TargetRange20.Copy
TargetRange21.Copy
TargetRange22.Copy
TargetRange23.Copy
CopyToCell.Offset(0, 55).PasteSpecial xlPasteValues, , ,
Transpose:=True
Application.CutCopyMode = False

End Sub


It turned out that clearing the sheet also removed the formulas, so I
took those lines out of yoru code, and then started adding all of the
cells I actually need to copy.

What happens with my version is that range 1 copies perfectly, but the
rest do not.

Can you point me in the right direction please?

Thank you.


On Jul 16, 10:21*am, "Per Jessen" wrote:
Hi

Add a *button from the Control Toolbox menu on sheet 1, rightclick the
button and view code and paste the code below:

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 CopyToCell As Range

Set ShA = Worksheets("Sheet1")
Set ShB = Worksheets("Sheet2")
With ShA
* * Set TargetRange1 = .Range("C1:C5")
* * Set TargetRange2 = .Range("D7:E7")
* * Set TargetRange3 = .Range("E11")
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, 5)
TargetRange3.Copy
CopyToCell.Offset(0, 7).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange1.ClearContents
TargetRange2.ClearContents
TargetRange3.ClearContents
Application.CutCopyMode = False

End Sub

The macro require headings in row 1 of Sheet 2, data will be pasted to A2
etc. Exit design mode, and give it a try.

Hopes this helps.
...
Per

"John Aikin" skrev i ...



I am putting together a quality assurance sheet for a contact center.

The sheet works fine right now to score calls, etc., but the next step
is to save the data somewhere.

So, is there a relatively simple way to save the data into rows on
another sheet?

In more detail, what I'm hoping to do is add a "save" button to the
bottom of the scoring sheet that does the following:

Copies (for example) cells c1, c2, c3, c4, c5, d7, e7 and e11 to a
second sheet and puts that data in rows so that row 1 would look like
this:
Cell A1 contains the data from sheet1, cell c1.
Cell B1 contains the data from seeht 1 cell c2

and so on.

Then after copying the data over, the save button would reset sheet 1.

I understand that this will probably involve VB or something, and
don;t expect you to do it for me, but if you know of examples online,
links to those examples would be fantastic.

Thank you- Hide quoted text -

- Show quoted text -

Thanks for your reply.

You have to add a destination for each copy statement.

Where target range is a column, it has to be transposed using PasteSpecal,
though if target range is a row, we don't need a paste or paste special
statement, but can state the destination after the copy statement (same
line) with or without using "Destination:="

Try this:

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 Destination:=CopyToCell.Offset(0, 14)
TargetRange5.Copy
CopyToCell.Offset(0, 17).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange6.Copy Destination:=CopyToCell.Offset(0, 25)
TargetRange9.Copy
CopyToCell.Offset(0, 28).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange10.Copy CopyToCell.Offset(0, 37)
TargetRange13.Copy
CopyToCell.Offset(0, 40).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange14.Copy CopyToCell.Offset(0, 44)
TargetRange17.Copy
CopyToCell.Offset(0, 47).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange18.Copy CopyToCell.Offset(0, 49)
TargetRange21.Copy
CopyToCell.Offset(0, 52).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Regards,
Per

"the Swing" skrev i meddelelsen
...
Thank you Per - you rock.

I hope you will be patient for just a bit longer, your code worked
great, but I obviously did something wrong when I tried to expand it.

Here is the code I'm trying to use:

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")
Set TargetRange3 = .Range("E18")
Set TargetRange4 = .Range("F18")
Set TargetRange5 = .Range("C27:C34")
Set TargetRange6 = .Range("D26")
Set TargetRange7 = .Range("E26")
Set TargetRange8 = .Range("F26")
Set TargetRange9 = .Range("C36:C44")
Set TargetRange10 = .Range("D35")
Set TargetRange11 = .Range("E35")
Set TargetRange12 = .Range("F35")
Set TargetRange13 = .Range("C46:C49")
Set TargetRange14 = .Range("D45")
Set TargetRange15 = .Range("E45")
Set TargetRange16 = .Range("F45")
Set TargetRange17 = .Range("C51:C52")
Set TargetRange18 = .Range("D50")
Set TargetRange19 = .Range("E50")
Set TargetRange20 = .Range("F50")
Set TargetRange21 = .Range("C53")
Set TargetRange22 = .Range("E53")
Set TargetRange23 = .Range("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
TargetRange3.Copy
TargetRange4.Copy
TargetRange5.Copy
TargetRange6.Copy
TargetRange7.Copy
TargetRange8.Copy
TargetRange9.Copy
TargetRange10.Copy
TargetRange11.Copy
TargetRange12.Copy
TargetRange13.Copy
TargetRange14.Copy
TargetRange15.Copy
TargetRange16.Copy
TargetRange17.Copy
TargetRange18.Copy
TargetRange19.Copy
TargetRange20.Copy
TargetRange21.Copy
TargetRange22.Copy
TargetRange23.Copy
CopyToCell.Offset(0, 55).PasteSpecial xlPasteValues, , ,
Transpose:=True
Application.CutCopyMode = False

End Sub


It turned out that clearing the sheet also removed the formulas, so I
took those lines out of yoru code, and then started adding all of the
cells I actually need to copy.

What happens with my version is that range 1 copies perfectly, but the
rest do not.

Can you point me in the right direction please?

Thank you.


On Jul 16, 10:21 am, "Per Jessen" wrote:
Hi

Add a button from the Control Toolbox menu on sheet 1, rightclick the
button and view code and paste the code below:

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 CopyToCell As Range

Set ShA = Worksheets("Sheet1")
Set ShB = Worksheets("Sheet2")
With ShA
Set TargetRange1 = .Range("C1:C5")
Set TargetRange2 = .Range("D7:E7")
Set TargetRange3 = .Range("E11")
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, 5)
TargetRange3.Copy
CopyToCell.Offset(0, 7).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange1.ClearContents
TargetRange2.ClearContents
TargetRange3.ClearContents
Application.CutCopyMode = False

End Sub

The macro require headings in row 1 of Sheet 2, data will be pasted to A2
etc. Exit design mode, and give it a try.

Hopes this helps.
...
Per

"John Aikin" skrev i
...



I am putting together a quality assurance sheet for a contact center.

The sheet works fine right now to score calls, etc., but the next step
is to save the data somewhere.

So, is there a relatively simple way to save the data into rows on
another sheet?

In more detail, what I'm hoping to do is add a "save" button to the
bottom of the scoring sheet that does the following:

Copies (for example) cells c1, c2, c3, c4, c5, d7, e7 and e11 to a
second sheet and puts that data in rows so that row 1 would look like
this:
Cell A1 contains the data from sheet1, cell c1.
Cell B1 contains the data from seeht 1 cell c2

and so on.

Then after copying the data over, the save button would reset sheet 1.

I understand that this will probably involve VB or something, and
don;t expect you to do it for me, but if you know of examples online,
links to those examples would be fantastic.

Thank you- Hide quoted text -

- Show quoted text -

thanks again Per.

I'm almost there.

When I use the code you sent below, some fields - for example D18:F18
give me a DIV/0 error on sheet1 - it looks like it is copying the
formula rather than the value.

I changed a line of the code to look like this:

TargetRange2.Copy
CopyToCell.Offset(0, 14).PasteSpecial xlPasteValues, , ,
Transpose:=True

Which fixed that, but now instead of copying the data in a row, it
copies it into the column.

I tried adding the Destination:=" into the line, but then I get errors
in the macro.

Any suggestions?

On Jul 17, 3:06*pm, "Per Jessen" wrote:
Thanks for your reply.

You have to add a destination for each copy statement.

Where target range is a column, it has to be transposed using PasteSpecal,
though if target range is a row, we don't need a paste or paste special
statement, but can state the destination after the copy statement (same
line) with or without using "Destination:="

Try this:

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 Destination:=CopyToCell.Offset(0, 14)
TargetRange5.Copy
CopyToCell.Offset(0, 17).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange6.Copy Destination:=CopyToCell.Offset(0, 25)
TargetRange9.Copy
CopyToCell.Offset(0, 28).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange10.Copy CopyToCell.Offset(0, 37)
TargetRange13.Copy
CopyToCell.Offset(0, 40).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange14.Copy CopyToCell.Offset(0, 44)
TargetRange17.Copy
CopyToCell.Offset(0, 47).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange18.Copy CopyToCell.Offset(0, 49)
TargetRange21.Copy
CopyToCell.Offset(0, 52).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Regards,
Per

"the Swing" skrev i ...
Thank you Per - you rock.

I hope you will be patient for just a bit longer, your code worked
great, but I obviously did something wrong when I tried to expand it.

Here is the code I'm trying to use:

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")
* * Set TargetRange3 = .Range("E18")
* * Set TargetRange4 = .Range("F18")
* * Set TargetRange5 = .Range("C27:C34")
* * Set TargetRange6 = .Range("D26")
* * Set TargetRange7 = .Range("E26")
* * Set TargetRange8 = .Range("F26")
* * Set TargetRange9 = .Range("C36:C44")
* * Set TargetRange10 = .Range("D35")
* * Set TargetRange11 = .Range("E35")
* * Set TargetRange12 = .Range("F35")
* * Set TargetRange13 = .Range("C46:C49")
* * Set TargetRange14 = .Range("D45")
* * Set TargetRange15 = .Range("E45")
* * Set TargetRange16 = .Range("F45")
* * Set TargetRange17 = .Range("C51:C52")
* * Set TargetRange18 = .Range("D50")
* * Set TargetRange19 = .Range("E50")
* * Set TargetRange20 = .Range("F50")
* * Set TargetRange21 = .Range("C53")
* * Set TargetRange22 = .Range("E53")
* * Set TargetRange23 = .Range("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
TargetRange3.Copy
TargetRange4.Copy
TargetRange5.Copy
TargetRange6.Copy
TargetRange7.Copy
TargetRange8.Copy
TargetRange9.Copy
TargetRange10.Copy
TargetRange11.Copy
TargetRange12.Copy
TargetRange13.Copy
TargetRange14.Copy
TargetRange15.Copy
TargetRange16.Copy
TargetRange17.Copy
TargetRange18.Copy
TargetRange19.Copy
TargetRange20.Copy
TargetRange21.Copy
TargetRange22.Copy
TargetRange23.Copy
CopyToCell.Offset(0, 55).PasteSpecial xlPasteValues, , ,
Transpose:=True
Application.CutCopyMode = False

End Sub

It turned out *that clearing the sheet also removed the formulas, so I
took those lines out of yoru code, and then started adding all of the
cells I actually need to copy.

What happens with my version is that range 1 copies perfectly, but the
rest do not.

Can you point me in the right direction please?

Thank you.

On Jul 16, 10:21 am, "Per Jessen" wrote: Hi

Add a button from the Control Toolbox menu on sheet 1, rightclick the
button and view code and paste the code below:

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 CopyToCell As Range

Set ShA = Worksheets("Sheet1")
Set ShB = Worksheets("Sheet2")
With ShA
Set TargetRange1 = .Range("C1:C5")
Set TargetRange2 = .Range("D7:E7")
Set TargetRange3 = .Range("E11")
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, 5)
TargetRange3.Copy
CopyToCell.Offset(0, 7).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange1.ClearContents
TargetRange2.ClearContents
TargetRange3.ClearContents
Application.CutCopyMode = False

End Sub

The macro require headings in row 1 of Sheet 2, data will be pasted to A2
etc. Exit design mode, and give it a try.

Hopes this helps.
...
Per

"John Aikin" skrev i
...

I am putting together a quality assurance sheet for a contact center.

The sheet works fine right now to score calls, etc., but the next step
is to save the data somewhere.

So, is there a relatively simple way to save the data into rows on
another sheet?

In more detail, what I'm hoping to do is add a "save" button to the
bottom of the scoring sheet that does the following:

Copies (for example) cells c1, c2, c3, c4, c5, d7, e7 and e11 to a
second sheet and puts that data in rows so that row 1 would look like
this:
Cell A1 contains the data from sheet1, cell c1.
Cell B1 contains the data from seeht 1 cell c2

and so on.

Then after copying the data over, the save button would reset sheet 1..

I understand that this will probably involve VB or something, and
don;t expect you to do it for me, but if you know of examples online,
links to those examples would be fantastic.

Thank you- Hide quoted text -

- Show quoted text -

Hi

Use PasteSpecial, but set Transpose=False

TargetRange2.Copy
CopyToCell.Offset(0, 14).PasteSpecial xlPasteValues, , ,
Transpose:=False

Regards,
Per


"the Swing" skrev i meddelelsen
...
thanks again Per.

I'm almost there.

When I use the code you sent below, some fields - for example D18:F18
give me a DIV/0 error on sheet1 - it looks like it is copying the
formula rather than the value.

I changed a line of the code to look like this:

TargetRange2.Copy
CopyToCell.Offset(0, 14).PasteSpecial xlPasteValues, , ,
Transpose:=True

Which fixed that, but now instead of copying the data in a row, it
copies it into the column.

I tried adding the Destination:=" into the line, but then I get errors
in the macro.

Any suggestions?

On Jul 17, 3:06 pm, "Per Jessen" wrote:
Thanks for your reply.

You have to add a destination for each copy statement.

Where target range is a column, it has to be transposed using PasteSpecal,
though if target range is a row, we don't need a paste or paste special
statement, but can state the destination after the copy statement (same
line) with or without using "Destination:="

Try this:

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 Destination:=CopyToCell.Offset(0, 14)
TargetRange5.Copy
CopyToCell.Offset(0, 17).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange6.Copy Destination:=CopyToCell.Offset(0, 25)
TargetRange9.Copy
CopyToCell.Offset(0, 28).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange10.Copy CopyToCell.Offset(0, 37)
TargetRange13.Copy
CopyToCell.Offset(0, 40).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange14.Copy CopyToCell.Offset(0, 44)
TargetRange17.Copy
CopyToCell.Offset(0, 47).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange18.Copy CopyToCell.Offset(0, 49)
TargetRange21.Copy
CopyToCell.Offset(0, 52).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Regards,
Per

"the Swing" skrev i
...
Thank you Per - you rock.

I hope you will be patient for just a bit longer, your code worked
great, but I obviously did something wrong when I tried to expand it.

Here is the code I'm trying to use:

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")
Set TargetRange3 = .Range("E18")
Set TargetRange4 = .Range("F18")
Set TargetRange5 = .Range("C27:C34")
Set TargetRange6 = .Range("D26")
Set TargetRange7 = .Range("E26")
Set TargetRange8 = .Range("F26")
Set TargetRange9 = .Range("C36:C44")
Set TargetRange10 = .Range("D35")
Set TargetRange11 = .Range("E35")
Set TargetRange12 = .Range("F35")
Set TargetRange13 = .Range("C46:C49")
Set TargetRange14 = .Range("D45")
Set TargetRange15 = .Range("E45")
Set TargetRange16 = .Range("F45")
Set TargetRange17 = .Range("C51:C52")
Set TargetRange18 = .Range("D50")
Set TargetRange19 = .Range("E50")
Set TargetRange20 = .Range("F50")
Set TargetRange21 = .Range("C53")
Set TargetRange22 = .Range("E53")
Set TargetRange23 = .Range("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
TargetRange3.Copy
TargetRange4.Copy
TargetRange5.Copy
TargetRange6.Copy
TargetRange7.Copy
TargetRange8.Copy
TargetRange9.Copy
TargetRange10.Copy
TargetRange11.Copy
TargetRange12.Copy
TargetRange13.Copy
TargetRange14.Copy
TargetRange15.Copy
TargetRange16.Copy
TargetRange17.Copy
TargetRange18.Copy
TargetRange19.Copy
TargetRange20.Copy
TargetRange21.Copy
TargetRange22.Copy
TargetRange23.Copy
CopyToCell.Offset(0, 55).PasteSpecial xlPasteValues, , ,
Transpose:=True
Application.CutCopyMode = False

End Sub

It turned out that clearing the sheet also removed the formulas, so I
took those lines out of yoru code, and then started adding all of the
cells I actually need to copy.

What happens with my version is that range 1 copies perfectly, but the
rest do not.

Can you point me in the right direction please?

Thank you.

On Jul 16, 10:21 am, "Per Jessen" wrote: Hi

Add a button from the Control Toolbox menu on sheet 1, rightclick the
button and view code and paste the code below:

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 CopyToCell As Range

Set ShA = Worksheets("Sheet1")
Set ShB = Worksheets("Sheet2")
With ShA
Set TargetRange1 = .Range("C1:C5")
Set TargetRange2 = .Range("D7:E7")
Set TargetRange3 = .Range("E11")
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, 5)
TargetRange3.Copy
CopyToCell.Offset(0, 7).PasteSpecial xlPasteValues, , , Transpose:=True
TargetRange1.ClearContents
TargetRange2.ClearContents
TargetRange3.ClearContents
Application.CutCopyMode = False

End Sub

The macro require headings in row 1 of Sheet 2, data will be pasted to
A2
etc. Exit design mode, and give it a try.

Hopes this helps.
...
Per

"John Aikin" skrev i
...

I am putting together a quality assurance sheet for a contact center.

The sheet works fine right now to score calls, etc., but the next step
is to save the data somewhere.

So, is there a relatively simple way to save the data into rows on
another sheet?

In more detail, what I'm hoping to do is add a "save" button to the
bottom of the scoring sheet that does the following:

Copies (for example) cells c1, c2, c3, c4, c5, d7, e7 and e11 to a
second sheet and puts that data in rows so that row 1 would look like
this:
Cell A1 contains the data from sheet1, cell c1.
Cell B1 contains the data from seeht 1 cell c2

and so on.

Then after copying the data over, the save button would reset sheet 1.

I understand that this will probably involve VB or something, and
don;t expect you to do it for me, but if you know of examples online,
links to those examples would be fantastic.

Thank you- Hide quoted text -

- Show quoted text -