Results 1 to 11 of 11

Copy, Paste, Tidy macro help

Threaded View

  1. #1
    Registered User
    Join Date
    01-27-2009
    Location
    NZ
    MS-Off Ver
    Excel 200a
    Posts
    14

    Red face Copy, Paste, Tidy macro help

    G'day.
    A while ago JBeaucaire provided me with code for a macro that copied data from one sheet, pasted it into another sheet, deleted empty and duplicate cells and sorted the results. I tweaked the code to get it to work in my work book and ended up with the following code:
    Option Explicit
    
    Sub MakeList()
    Dim LR As Long, LC As Long, i As Long
    LR = Range("AA4").SpecialCells(xlCellTypeLastCell).Row
    LC = Range("AA4").SpecialCells(xlCellTypeLastCell).Column
    Application.ScreenUpdating = False
    
    'Copy all data to second sheet in one column
        For i = 26 To LC
            Range(Cells(2, i), Cells(LR, i)).Copy _
                Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Offset(1, 0)
        Next i
    
    'Clear out null cells and sort
        Sheets("Sheet1").Activate
        Range("A1") = "List"
        Range("B1") = "Key"
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Range("B2:B" & LR).FormulaR1C1 = "=OR(RC1="" "",RC1="""")"
        Range("A2").AutoFilter Field:=2, Criteria1:="TRUE"
        Range("A2:B" & LR).SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
        
    'Cleanup
        Rows("1:1").Delete
        Columns("B:B").ClearContents
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Range("A1:A" & LR).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo
        Range("A1:A" & LR).RemoveDuplicates Columns:=1, Header:=xlNo
        Range("A1").Select
        Application.ScreenUpdating = True
    End Sub
    Can anyone tell me how to make the code paste the first result into cell A2 so I can have a title in cell A1. I'm sure it is very easy, I just don't know how to do it.

    Cheers
    Last edited by falcon5nz; 07-20-2009 at 08:47 AM. Reason: Solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1