Cell References from old worksheet to new worksheet.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
If you only have to do this once, I'd do this:

Save the original file and do this on another copy (just in case)
1) Add a helper column.
2) Enter the values 1,2,3 ... all the way down through the data.
3) Copy all rows and paste at the bottom.
4) For the last group of rows, delete the data that you don't want in the
"second" row
5) For the first group of rows, delete the data that you don't want in the
"first" row
6) Sort on the helper column
 
Unfortunately, this is something I'm going to do frequently.

This is a simplified explanation...

I get a worksheet with many columns. I created a new worksheet that in row
1 references the cells in the first half of the columns and in row 2
references the second hald of the columns. I was hoping to copy/paste the
formulas down, but row 3 (in the new worksheet) sucks in row 3 from the old
worksheet, instead of row 2 (and on down the line).

Like I said, unfortunately, it's a large sheet and I'll be doing this a
couple times a week.
 
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
 
Actually, looking at this again, I wasted an array slot. The line that dims
SourceArray could be changed to
Dim SourceArray(1 To 3) As String
Not that it would make too much difference.
 
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!
 
Glad you figured out how to do it.

Ray Elias said:
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!
Click to expand...
 
Back
Top