automatic copy and paste from sheet to sheet in a workbook

[Guest]

I'm really beating my head against the wall on this one, I've gotten some
previous input that I haven't been able to make work. I know there has got to
be a way to do this. All help is appreciated.


I have a 13 sheet workbook

I want the info input into cells C15:C60 (including things like background
colors) on Sheet 1 to go to cells C4:AZ4 on Sheet 13.

I want the info input into cells C15:C60 (including things like background
colors) on Sheet 2 to go to cells C5:AZ5 on Sheet 13.

And so on for Sheets 3-12 using the previous pattern.

Just info, Sheets 1-12 are training scoring sheets with daily scores input
and Sheet 13 is a tracking chart for the previously scored days. The goal is
for there to be no input from the user required on page 13. I already have a
working code for my background colors that generate background shading based
upon certian numbers and keywords. I can provide the code that I'm using for
my background colors if you find that useful.

As always, all help is appreciated and thanks in advance.

 

[R.VENKATARAMAN]

have I understood correctly
c15 to c60 are 46 cells
c4 to az4 has 50 cells. perhaps AZ4 should be AV4 please check

what you want is column values to be transposed to row values

use edit-copy-edit-pastesepcial-all-transpose

highlight c15 to c60 and copy
goto sheet13 highlight C4 and use edit -pastespecial-all-trnspose

similary for other sheets.
you can preparea a programme
If I have misunderstood aplogise.

 

[Sharad]

Hello,

Worksheets("Sheet1").Range("C15:C60").Copy _
Destination:=Worksheets("Sheet13").Range("C5:AZ5")

Sharad

 

[Tom Ogilvy]

Worksheets("Sheet1").Range("C15:C60").Copy _

Destination:=Worksheets("Sheet13").Range("C5:AZ5")

That would make 46 copies of C15:C60 from Sheet1 on sheet 13.

This would be a better approach:
Sub AA()
Worksheets("Sheet1").Range("C15:C60").Copy
Worksheets("Sheet13").Range("C4").PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
End Sub

This will process the first 12 sheets in the tab order placing the results
on successive rows of a sheet named Sheet13.

Sub AB()
For i = 1 To 12
Worksheets(i).Range("C15:C60").Copy
Worksheets("Sheet13").Range("C4").Offset(i - 1, 0) _
.PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next
End Sub

 

[Sharad]

sorry earlier post was without fully understanding what you want to do.

Below is correct complete code.


Dim sName As String
For i = 1 To 12
sName = "Sheet" & i
Worksheets(sName).Range("C15:C60").Copy
Worksheets("Sheet13").Range("C" & 3 + i).PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
Next i

Sharad

 

[Guest]

You're right, is is only the 46 cells so it would only be C to AZ

I haven't been able to get even a trial version of 5-6 cells to work so I
don't think that is the problem.

I have my main workbook created and have been trying out the suggested codes

 

[Guest]

I have finally done it. Thanks to all who gave imput on this problem.

This is what finally ended up working for me.


Sub AA()
Worksheets("Sheet1").Range("A1:A15").Copy
Worksheets("Sheet3").Range("C4").PasteSpecial Paste:=xlPasteAll,
Transpose:=True
End Sub


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:AW50")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 0
Target.Interior.ColorIndex = 1
Case 1 To 2
Target.Interior.ColorIndex = 3
Case 3
Target.Interior.ColorIndex = 13
Case 4 To 5
Target.Interior.ColorIndex = 10
Case 6 To 7
Target.Interior.ColorIndex = 41
Case “INFOâ€
Target.Interior.ColorIndex = 6
Case "VH"
Target.Interior.ColorIndex = 3
Case "FMCT"
Target.Interior.ColorIndex = 3
Case "H"
Target.Interior.ColorIndex = 13
Case "FM"
Target.Interior.ColorIndex = 13
Case "CT"
Target.Interior.ColorIndex = 10
Case "M"
Target.Interior.ColorIndex = 10
Case "L"
Target.Interior.ColorIndex = 41
Case "DISP"
Target.Interior.ColorIndex = 41
Case "X"
Target.Interior.ColorIndex = 6
End Select
End If
End Sub