Results 1 to 7 of 7

Thread: Select Range based on cell content

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2007
    Posts
    19

    Select Range based on cell content

    Hi there

    I am new to VBA and am trying to write some code to select a range of cells dependent on the content and copy and paste it into another worksheet.

    I have attached a sample file so that it is easier to see what i want to do. Basically, i want to select all the cells in column A that contain ADE, for example, and then for those copy all the relative cells in columns B, D, E and L.

    The problem i have is that there will be a different number of ADE's each time i create the spreadsheet, and the first occurance of ADE will not always be in cell A4, so i need the code to be able to search the sheet for the first occurance and select the relevant number to copy.

    Can anyone help?
    Attached Files Attached Files

  2. #2
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

    Re: Select Range based on cell content

    try this:
    Code:
    Sub Macro2()
    '
    ' Macro2 Macro
    ' Macro recorded 14/05/2007 by Erin Redwood
    '
    ' Keyboard Shortcut: Ctrl+Shift+A
    '
    Dim i As Integer
    Dim j As Integer
    j = 1
    'run thru lines 2 to 13
    For i = 2 To 13
        Sheets("Sheet1").Select
        Cells(i, 1).Select
        If Cells(i, 1).Value = "ADE" Then
            'copy value in column A
            Cells(i, 1).Select
            Selection.Copy
            Sheets("Sheet2").Select
            Cells(j, 1).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
            'copy value in column B
            Cells(i, 2).Select
            Selection.Copy
            Sheets("Sheet2").Select
            Cells(j, 2).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
            'copy value in column D
            Cells(i, 4).Select
            Selection.Copy
            Sheets("Sheet2").Select
            Cells(j, 3).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
            'copy value in column E
            Cells(i, 5).Select
            Selection.Copy
            Sheets("Sheet2").Select
            Cells(j, 4).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
            'copy value in column L
            Cells(i, 12).Select
            Selection.Copy
            Sheets("Sheet2").Select
            Cells(j, 5).Select
            ActiveSheet.Paste
            j = j + 1
        End If
    Next i
    End Sub
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  3. #3

    Thread Starter
    Junior Member
    Join Date
    May 2007
    Posts
    19

    Re: Select Range based on cell content

    Thanks Opus

    That one doesn't seen to have worked. Someone else has suggested the following code:

    Sub filterADE()

    Sheets("Sheet1").Cells.autofilter 1, "ADE"
    Range("B:B").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Sheet2").Range("A1").PasteSpecialxlValues

    End Sub

    Which also doesn't work! Your code comes back with the following error: 400

    The other code runs ok but doesn't actually paste anything into Sheet 2.

    Any clues?

    B.

  4. #4
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

    Re: Select Range based on cell content

    Which part of the code I posted doesn't work.
    I tested it here, my "Sheet2" did get all the lines that contain ADE in column 1.
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  5. #5

    Thread Starter
    Junior Member
    Join Date
    May 2007
    Posts
    19

    Re: Select Range based on cell content

    I'm not sure which bit of it isn't working, but it is not pasting anything at all into Sheet 2 and i am just getting an error box with 400 written in it...

  6. #6
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

    Re: Select Range based on cell content

    If you start the macro with fuction key F8 it will be run line by line, that way you'll be able to find the problem!
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  7. #7
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

    Re: Select Range based on cell content

    please post the code you are actually using!
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

Posting Permissions

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



Click Here to Expand Forum to Full Width