Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Excel Macro (Excel 2002)

    Hello!
    I've received the following code from Hans in the past (tweaked here and there to fit my needs), and I'm wanting to change the area of the macro below that has lots of spaces around it (sorry, I couldn't get it highlighted) to copy and paste the data to the target worksheet based on a change in column AC. As it stands now, if there is a new change in the first two digits of the data in column Z, then it creates a new "journal entry" in the target worksheet (keep in mind that the first two digits in column Z results in the company number).... now I'd like it to make a different "journal entry" based on column AC - I've changed the macro to sort the data in column AD first (this is the company number, as a new journal entry is needed for every company number), then to sort column AC, which is the narrative of the journal entry. I'm needing that bit of code that will read each line of data and when the data in column AC "changes" then start a new copy & paste action. The result would be a new journal entry for each company AND each different narrative... leaving us with one company having numerous DIFFERENT journal entries as opposed to ONE BIG journal entry....I hope this makes sense!??!?!
    Thanks so much!
    Lana

    Sub CopyData()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngSourceRow As Long
    Dim lngTargetRow As Long
    Set wshSource = Worksheets("GL")
    Set wshTarget = Worksheets("JEUpload")

    'Copy columns A thru E to column Z for sorting purposes only
    wshSource.Columns("A:E").Copy
    wshSource.Range("Z1").PasteSpecial Paste:=xlPasteValues


    'Clear out the old data from the JEUpload worksheet
    wshTarget.Range("A16:IV5000").Clear
    wshTarget.Range("A15:B15").Clear
    wshTarget.Range("K15").ClearContents

    'Sort the copied and pasted data in Z thru AD
    lngSourceRow = 3
    lngTargetRow = wshTarget.Range("A65536").End(xlUp).Row
    wshSource.Range("Z" & lngSourceRow).CurrentRegion.Sort _
    Key1:=wshSource.Range("AD" & (lngSourceRow - 1)), Order1:=xlDescending, Header:=xlYes, _
    Key2:=wshSource.Range("AC" & (lngSourceRow - 1)), Order1:=xlAscending, Header:=xlYes
    Do





    If Not Left(wshSource.Range("Z" & lngSourceRow), 2) = _
    Left(wshSource.Range("Z" & (lngSourceRow - 1)), 2) And lngSourceRow > 3 Then
    lngTargetRow = lngTargetRow + 4
    wshTarget.Range("A11:I14").Copy Destination:=wshTarget.Range("A" & lngTargetRow)
    lngTargetRow = lngTargetRow + 3
    End If





    'Copy data from rows Z thru AC and paste into the JEUpload worksheet
    If wshSource.Range("AB" & lngSourceRow) <> 0 Then
    lngTargetRow = lngTargetRow + 1
    wshTarget.Range("A15:K15").Copy Destination:=wshTarget.Range("A" & lngTargetRow)
    wshSource.Range("Z" & lngSourceRow).Copy Destination:=wshTarget.Range("A" & lngTargetRow)
    wshSource.Range("AA" & lngSourceRow).Copy Destination:=wshTarget.Range("B" & lngTargetRow)
    wshSource.Range("AB" & lngSourceRow).Copy Destination:=wshTarget.Range("K" & lngTargetRow)
    wshSource.Range("AC" & lngSourceRow).Copy Destination:=wshTarget.Range("G" & lngTargetRow)
    End If
    lngSourceRow = lngSourceRow + 1
    Loop Until wshSource.Range("Z" & lngSourceRow) = ""

    'Clear out the copied & pasted data performed for sorting
    'purposes via the 1st step above
    wshSource.Range("Z:AD").Clear
    Sheets("GL").Select
    Range("A2").Select
    Sheets("JEUpload").Select
    Range("A1").Select

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Macro (Excel 2002)

    It's very hard to visualize without seeing the workbook, but is it sufficient to change the two occurrences of "Z" in the lines you indicated to "AC"?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel 2002)

    I've attached an example for reference (as I should have done earlier...sorry). Please note that the data is now in columns A thru E, as opposed Z thru AD. Further explanation of my thoughts are in the worksheet.
    Thanks!!!
    Lana
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Macro (Excel 2002)

    Does the attached macro do what you want?
    Attached Files Attached Files

Posting Permissions

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