automatic copy and paste from sheet to sheet in a workbook

[ramseyjramseyj]

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.

 

[Sharad]

Hello,

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

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[ramseyjramseyj]

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

 

[ramseyjramseyj]

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