Solved: is there a paste special for this data?

[genracela]

I don;t know if this is asking to much from excel.

But, is there a way to do a paste special for my excel concern?

Please see attached sample.

Is it possible that Sheet 1(Orig Data)
will look like Sheet 2(Is this Possible)

Paste Special - Tranpose doesn't work, so maybe a VBA code will be able to solve this problem.

Thanks! I appreciate all your help.

[GTO]

Greetings genracela,

A couple of questions:

Does the formatting (font/interior colors and such) matter, or do we just need the values?
Can we count on a 28 day cycle, or do we need to find the last column?Thanks,

Mark

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Columns(3).Insert
For i = LastRow To 2 Step -1

.Rows(i + 1).Resize(LastCol - 2).Insert
.Cells(1, "D").Resize(, LastCol - 2).Copy
.Cells(i + 1, "C").Resize(LastCol - 2).PasteSpecial _
Paste:=xlPasteAll, _
Transpose:=True
.Cells(i, "D").Resize(, LastCol - 2).Copy
.Cells(i + 1, "D").Resize(LastCol - 2).PasteSpecial _
Paste:=xlPasteAll, _
Transpose:=True
.Cells(i, "A").Resize(, 2).AutoFill .Cells(i, "A").Resize(LastCol - 1, 2)
.Rows(i).Delete
Next i

.Columns(3).AutoFit
.Range("C1:D1").Value = Array("Date", "Qty")
.Range("D1").Resize(LastCol - 3).ClearContents
End With

End Sub

[GTO]

Maybe the slightest tweak to the last bit.


'// Change...
'.Range("D1").Resize(LastCol - 3).ClearContents
'// ..to..
.Range("E1").Resize(, LastCol - 3).Clear
'// To rid the selection //
.Range("A1").Select
End With

[genracela]

I tried the code, and it worked, but there's a slight problem.

D2: D27 is blank. It supposed to have qty.

Thanks for all the help!

[Bob Phillips]

Missing comma



Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Columns(3).Insert
For i = LastRow To 2 Step -1

Debug.Assert i <> 2
.Rows(i + 1).Resize(LastCol - 2).Insert
.Cells(1, "D").Resize(, LastCol - 2).Copy
.Cells(i + 1, "C").Resize(LastCol - 2).PasteSpecial _
Paste:=xlPasteAll, _
Transpose:=True
.Cells(i, "D").Resize(, LastCol - 2).Copy
.Cells(i + 1, "D").Resize(LastCol - 2).PasteSpecial _
Paste:=xlPasteAll, _
Transpose:=True
.Cells(i, "A").Resize(, 2).AutoFill .Cells(i, "A").Resize(LastCol - 1, 2)
.Rows(i).Delete
Next i

.Columns(3).AutoFit
.Range("C1:D1").Value = Array("Date", "Qty")
.Range("D1").Resize(, LastCol - 3).ClearContents
End With

Application.ScreenUpdating = True

End Sub

[genracela]

Thanks again XLD!:joy: