Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Copying Three selection within loop

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This procedure works ( Thanks to help from Barrie Davidson)
    I now want to add two further cell selections to the main range selection

    Adding the two cells with in the range by bracketing or using the , only appear to cause the selection to become the first cell in range to the last cell in range

    Any help would be appreciated
    I have ' the section requiring help

    Sub cmr()
    Dim i
    i = 15
    ' To select condition c in column e then place in sheet cmr at A15
    Sheets("mobile").Select
    Range("e15").Select
    For Each X In Range("E15", Range("E16").End(xlDown))
    If X = "" Then Exit For
    If UCase(ActiveCell.Value) = "C" Then
    Sheets("mobile").Activate
    Range("A" & i).Select
    Range("A" & i & ":H" & i).Select ' This is where I want add ("y" & i) plus ("CF" & i)
    Selection.Copy
    Sheets("cmr").Select
    Range("A15").Select
    Sheets("cmr").Select
    Range("A2000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
    Sheets("mobile").Select
    i = Selection.Row + 1
    ElseIf UCase(Activecel.Value) <> "C" Then
    i = Selection.Row + 1
    Range ("E" & i)
    End If
    Next
    Sheets("mobile").Select
    Range("E15").Select
    End Sub

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Keith, change:

    Range("A" & i & ":H" & i).Select

    to read:
    Range("A" & i & ":H" & i & ",Y" & i & ",CF" & i).Select

    Note that when you paste this it will be in columns A:J.
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Barrie, I will try that. However I did try this before and it select from a to cf

    I will advise
    kk

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That doen't work.

    I have tried Range (Range (), Range(), Range() ).select
    No joy,

    Range( Range as above ).select
    No joy

    I wonder if it should be ton three seperate lines with activat as the key

    KK

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try with

    Union(Range("A1"),Range("B5:D7"),Cells(1,3))

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Keith, I tested that code and it worked okay for me. It selected the cells in row (i) in columns A:H, column Y, and column CF. Did you try copying it and pasting it in your code?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barrie,
    Thanks a million. That works a treat.
    I placed the , left of the quote on the additional ranges.

    Ps I was working on 2 machines.

    It runs sweetly on both.

    Thanks again

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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