Re: Transpose or Macro?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


Sub FixDates()


With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
LastRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

MyDate = .Range("A" & RowCount)
Set LastCol = .Cells(RowCount, Columns.Count).End(xlToLeft)
Set CopyRange = .Range(.Range("B" & RowCount), LastCol)
CopyRange.Copy
Sheets("Sheet2").Range("B" & NewRow).PasteSpecial
Transpose:=True
LastRow = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A" & NewRow & ":A" & LastRow) = MyDate
Sheets("Sheet2").Range("A" & NewRow & ":A" & LastRow) _
NumberFormat = "DD-MMM"
RowCount = RowCount + 1
Loop
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=151281

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

.

Relevant Pages

  • RE: hiding rows
    ... "Joel" wrote: ... Dim SearchCol As Variant ... Next RowCount ... End Sub ...
    (microsoft.public.excel.programming)
  • RE: VBA help w/ look & fill in data
    ... statementt is the only place in this sub that will cause a failure.. ... It just filled out blank data in sheet1. ... individual sheet. ... RowCount = RowCount + 1 ...
    (microsoft.public.excel.programming)
  • RE: reutrn a value from a cell in each workbook filename from anot
    ... "Joel" wrote: ... Filename Thisworkbook.name Then ... RowCount = RowCount + 1 ... Sub Gettotals() ...
    (microsoft.public.excel.programming)
  • RE: Find and report
    ... "Joel" wrote: ... Sub MakeReport() ... RowCount = RowCount + 1 ... Loop While Not c Is Nothing And c.Address firstAddress ...
    (microsoft.public.excel.misc)
  • RE: VBA help w/ look & fill in data
    ... Sub checkso() ... Dim R1300M100 ... For Each cell In ColHRange ... RowCount = RowCount + 1 ...
    (microsoft.public.excel.programming)