Cell References from old worksheet to new worksheet.
Glad you figured out how to do it.
"Ray Elias" wrote:
Thanks for the reply.
I actually "rigged" a way to do what I wanted. Yours looks much cleaner,
though.
FWIW, I created a column (A) and for each row, I "autofilled"
"1,1,2,2,3,3,4,4...". Then, using "INDIRECT" statements referring back to
that column...
=INDIRECT("'worksheet1'$A$"&A2) for each cell in both rows.
Then, I just copied & pasted that section of cells all the way down.
Once I figured out to have the autofilled column and use INDIRECT, it was a
piece of cake.
Hope this helps someone.
Thanks again, Kleev, for your very insightful response!
"Kleev" wrote:
Try this:
Sub ColToNewRow()
Dim wsSource As Worksheet, wsDest As Worksheet
Dim SourceStartRow As Long, SourceFinalRow As Long
Dim DestStartRow As Long
Dim SourceArray(3) As String
Dim rng As Range, cell As Range
Dim i As Integer
Set wsSource = ThisWorkbook.Worksheets("sheet1")
Set wsDest = ThisWorkbook.Worksheets("sheet6")
SourceStartRow = 1
SourceFinalRow = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
DestStartRow = 3
Set rng = wsSource.Range("a" & SourceStartRow & ":a" & SourceFinalRow)
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
For Each cell In rng
For i = 1 To 3
SourceArray(i) = cell.Offset(, i - 1)
wsDest.Cells(DestStartRow, i) = SourceArray(i)
Next i
DestStartRow = DestStartRow + 1
For i = 4 To 6
SourceArray(i - 3) = cell.Offset(, i - 1)
wsDest.Cells(DestStartRow, i - 3) = SourceArray(i - 3)
Next i
DestStartRow = DestStartRow + 1
Next cell
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
"Ray Elias" wrote:
Let's say I have a workbook with 6 columns of data and 2 rows of data.
(simplified for easier explanation)
I want to, in a new spreadsheet, re-organize it so it's better formatted for
printing.
Here's what I want to do. For each existing row, I want to turn it into 2
rows on the new workbook.
For example, In the new workbook, I want row 1 columns A-C in row 1 Columns
A-C, and I want row 1 columns D-F in row 2 columns A-E. Then from the old
workbook, I want the old row 2 to take up rows 3 & 4 in the new workbook.
OLD:
R1CA R1CB R1CC R1CD R1CE R1CF...
R2CA R2CB R2CC R2CD R2CE R2CF...
NEW:
R1CA R1CB R1CC
R1CD R1CE R1CF
R2CA R2CB R2CC
R2CD R2CE R2CF
I cannot, for the LIFE of me, figure out how to do this
(easily/automatically) for a LARGE amount of data (200 Rows, 20 Columns ---
400 Rows, 10 Columns).
Any help would be GREATLY appreciated (before I pull out the remainder of my
hair)
|