2-dimensional Date Range With Dynamic Copy And Paste

epynephrin · Jun 4th 2007

Re: 2-dimensional Date Range With Dynamic Copy And Paste

Hm. That is food for thought.

I'm thinking I'll just stick it out with what I've come up with. I recall there being a reason--either a design request, or my personal preference--behind avoiding the style you've suggested, Jim. I'm looking and thinking to myself "197 line items for 20 participants? Seems a bit much."

Again, many thanks for all your help. The way my system is coming together, that method wouldn't be effective or clean enough. Thanks for trying, though. I will keep it in mind on future projects.

Thanks to everyone who tried. I think that my solution a few posts ago, the refresh macro, best solves my needs. This thread can now be laid to rest.

junho lee · Jun 4th 2007

Re: 2-dimensional Date Range With Dynamic Copy And Paste

activate sheet,wrongway,after insert sheet 1
and run

Code

Sub datatrans()
Dim ll As Integer


ll = Cells(1, 1).End(xlDown).Row
k = 3
For I = 3 To ll
    For j = 8 To 24
        If Not Cells(I, j) = "" Then
            Range(Cells(I, 1), Cells(I, 7)).Copy Sheets("sheet1").Cells(k, 1)
            Cells(I, j).Copy Sheets("sheet1").Cells(k, 9)
            Cells(2, j).Copy Sheets("sheet1").Cells(k, 8)
            k = k + 1
        End If
    Next
Next
Range(Cells(2, 1), Cells(2, 7)).Copy Sheets("sheet1").Cells(2, 1)
    With Sheets("sheet1")
        .Cells(2, 8) = "course"
        .Cells(2, 9) = "date"
        Sheets("Sheet1").Select
    Range("H2:I2").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    Selection.Font.Bold = True
    Columns("A:I").Select
    Selection.Columns.AutoFit
    Range("H8").Select
    Selection.Columns.AutoFit
    Columns("H:H").ColumnWidth = 20.29
    Columns("H:H").Select
    Selection.Rows.AutoFit
    Range("G13").Select
    End With
End Sub

Display More

regards,

2-dimensional Date Range With Dynamic Copy And Paste

Participate now!

Share

Users Viewing This Thread