Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Copying a cell contents of another location and filling until the next criteria

    I downloaded a report and want to place it into EXCEL
    the report is easily uploaded to EXCEL except at the beginning of each section there is a date.
    I would like to copy that date in the first column (NEW COLUMN)
    of the relevant section and go to the next section and copy that sections date down the list.
    Is there a way to accomplish this.

    I have a sample of the original download and what I want the final to look like.

    Thank you.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    Does the file to be loaded have blank lines at the top or does the data start in row 1? Obviously it won't have the ORIGINAL tag.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    See the attached.

    Should work as long as the unneeded rows are blank or have the same words as the examples.

    You can copy down the formulas to as may rows as you desire and tweak as needed

    Hope this helps,

    TD
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    Here's some code which will not do exactly as you asked but I tried to think ahead in that if you were copying a report into Excel you would would want to manipulate the data. So I wrote code to create a database (a new sheet) out of the report so that would be easier to do.

    Code:
    Option Explicit
    
    Sub Create_DB()
    
    
       Dim wksSrc   As Worksheet
       Dim wksTgt   As Worksheet
       Dim lLastRow As Long
       Dim lNextRow As Long
       Dim lDBRow   As Long
       Dim lCntr    As Long
       Dim zCurCC   As String
       Dim zBooked As String
       
       lLastRow = Cells(Rows.Count, 1).End(xlUp).Row()
    
       Set wksSrc = ActiveSheet
       Set wksTgt = Sheets.Add(After:=Sheets(Sheets.Count))
       
       wksTgt.Name = "Database"
       [A1].Value = "Date"
       [B1].Value = "Corp Code"
       [C1].Value = "Class"
       
       lDBRow = 2
    
       Sheets("Sheet1").Select
       lNextRow = 1
       [A1].Select
       
       Do While (lNextRow <= lLastRow)
       
            lNextRow = Cells.Find(What:="CLASS", After:=ActiveCell, _
                                             LookIn:=xlFormulas, _
                                             LookAt:=xlWhole, _
                                             SearchOrder:=xlByRows, _
                                             SearchDirection:=xlNext, _
                                             MatchCase:=True, _
                                             SearchFormat:=False).Row()
                                             
       
         zCurCC = Cells(lNextRow, 2).Offset(-2, -1).Value
         zBooked = Cells(lNextRow, 2).Offset(-2, 0).Value
         
         Do While (Cells(lNextRow, 2).Value <> "SORT:")
         
           If Cells(lNextRow, 1).Value = zCurCC Then
           
             With wksTgt
                 .Cells(lDBRow, 1).Value = zBooked
                 .Cells(lDBRow, 2).Value = zCurCC
                 .Cells(lDBRow, 3).Value = Cells(lNextRow, 2).Value
             End With  'wkstgt
             
             lDBRow = lDBRow + 1
             
           End If
           
           lNextRow = lNextRow + 1
           If (lNextRow > lLastRow) Then GoTo FinishUp
         
         Loop
         
           Cells(lNextRow, 1).Select
           
       Loop
       
    FinishUp:
    
      wksTgt.Columns("A:C").EntireColumn.AutoFit
    
    End Sub       'Create_DB
    Results:
    MNN.PNG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    MNN,

    A third option:

    Code:
    Sub MoveDates()
    Dim Index As String, I As Long
        Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Lastrow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
        For I = 1 To Lastrow
            If IsDate(Cells(I, 3)) Then
                Index = Cells(I, 2)
                dte = Cells(I, 3)
            ElseIf Cells(I, 2) = Index Then
                Cells(I, 1) = dte
            End If
        Next I
    End Sub
    Mnn.png

    I doesn't matter which row you start at as long as you pasted into column A.

    HTH,
    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •