Copy cells from one worksheet and paste to incrementing sheets?

[scottadams80]

Hi,

I am slightly familiar with excel, macros and visual basic but would
really like a quick fix for this problem. I want to take a range of
cells that look like this:

Ex.1 Ex.2 Ex.3
2007-02-09 1 4 1
2007-02-10 3 3 3
2007-02-11 5 2 4
2007-02-12 4 7 7
2007-02-13 7 9 5
2007-02-14 3 8 9
2007-02-15 2 6 8

I then want to be able to take the first row (i.e. 1,4,1) and copy it
to the next sheet in my workbook but values only and transposed, which
ive already figured out as:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

In the same macro i then want to take the second row (3,3,3) and copy
it to the sheet after the one I just pasted to and carry on down the
list until I hit blank.

Any ideas?


Help much appreciated

 

[JE McGimpsey]

One way:

Public Sub TransposeAndCopy()
Const nCOLS As Long = 4
Dim rCell As Range
Dim i As Long
Dim nMaxSheets As Long


With ActiveSheet
nMaxSheets = .Parent.Worksheets.Count
i = .Index + 1
For Each rCell In .Range("A1:A" & _
.Range("A" & .Rows.Count).End(xlUp).Row)
Worksheets(i).Cells(1).Resize(nCOLS, 1).Value = _
Application.Transpose(rCell.Resize(1, nCOLS).Value)
i = i + 1
If i > nMaxSheets Then Exit For
Next rCell
End With
End Sub

 

[Guest]

You could use a formula: asuming your data starts in row 1, column B (column
A is date?) and starting in A1 on sheet2:

=IF(OFFSET(Sheet1!$B$1,INT((ROW()-1)/3),MOD(ROW()-1,3))<>"",OFFSET(Sheet1!$B$1,INT((ROW()-1)/3),MOD(ROW()-1,3)),"")

and copy down.

Then highlight data, COPY, Paste Special=>Values.

HTH