Results 1 to 8 of 8

Thread: copy past range using variable for start cell address and 2nd variable for end cell address
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2016
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default copy past range using variable for start cell address and 2nd variable for end cell address

    Please help
    I have searched the web and tried every solution I have found, none worked.
    I am trying to copy a range of cells, say T2,AB5 to another sheet. Problem is I don't know where this well appear so have to set a variable.
    It may appear several times throughout the data sheet.
    each occurrence must be copied to another sheet in succession.

    T2,AB5 to sheet2 D6,L9
    T33,AB36 to sheet 2 D10,L13
    and so on

    This is the code I am trying to make work but get a Run-time error '1004': Application-defined or object-defined error

    Please tell me what I am doing wrong with this code line??
    DestLR is the variable and is dim as Long

    Code:
                     Worksheets("Wells").Range(Sheets("Wells").Cells(DestLR, 20), Sheets("Wells").Cells(DestLR + 3, 36)).Copy
                    Sheets("Summary").Range(DestLR5, 7).PasteSpecial xlPasteValues

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,242
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: copy past range using variable for start cell address and 2nd variable for end cell address

    Code:
    Sheets("Summary").Range(DestLR5, 7).PasteSpecial xlPasteValues
    looks like it should be

    Code:
    Sheets("Summary").Cells(DestLR5, 7).PasteSpecial xlPasteValues
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    New Member
    Join Date
    Feb 2016
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy past range using variable for start cell address and 2nd variable for end cell address

    Quote Originally Posted by MARK858 View Post
    Code:
    Sheets("Summary").Range(DestLR5, 7).PasteSpecial xlPasteValues
    looks like it should be

    Code:
    Sheets("Summary").Cells(DestLR5, 7).PasteSpecial xlPasteValues
    Thank you so very much!!!!!

    It works great, however, I was sure that was what I had started with originally and couldn't get to work so started
    searching for solutions and tried several and by the time I posted the question I was using the .Range.

    I must have changed something else that effected it in the process. I would never have gone back to the .Cells solution
    as I was so frustrated.
    Thanks again. but now that that portion of the code is working fine another part which used to work does not. As I tried to find why...
    I discovered the button which is on two different sheets but starts the same module works on one but not the other, confusing.

    Could it be that I have a statement wrong such that if I use the button on the sheet that is referenced by the line in question it works
    but if I use the button on the other sheet it errors out??

    here is the line that errors out with "Run-time error '1004' Application-defined or object-defined error";

    Code:
                    Worksheets("Wells").Range(Cells(DestLR, 20), Cells(DestLR4, 36)).FillRight
    again let me say that this works if I am in the sheet that this refers to ie "Wells" and use the button there.

    this is puzzling to me because I am using the "Worksheets("Wells") to define where the range is and where the object is???

  4. #4
    New Member
    Join Date
    Feb 2016
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy past range using variable for start cell address and 2nd variable for end cell address

    I forgot to add that in the vb I can click on run continue and the code will complete with everything just as it should be.

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,242
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: copy past range using variable for start cell address and 2nd variable for end cell address

    Try either

    Code:
    Worksheets("Wells").Range(Worksheets("Wells").Cells(DestLR, 20), Worksheets("Wells").Cells(DestLR4, 36)).FillRight
    or

    Code:
    With Worksheets("Wells")
      .Range(.Cells(DestLR, 20), .Cells(DestLR4, 36)).FillRight
    End With
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    New Member
    Join Date
    Feb 2016
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy past range using variable for start cell address and 2nd variable for end cell address

    Thanks a heap. tried the first line and it works!!
    My next step now is look over the code and try to find where I can make it run faster. It takes over 5 hours to process 250 pages of data.
    I so appreciate your help.

  7. #7
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,242
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: copy past range using variable for start cell address and 2nd variable for end cell address

    Thanks a heap. tried the first line and it works!!
    both codes do exactly the same, the 2nd one is just a different way of laying out the first.

    It takes over 5 hours to process 250 pages of data.
    What other code do you have?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  8. #8
    New Member
    Join Date
    Feb 2016
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy past range using variable for start cell address and 2nd variable for end cell address

    Wow, just found out how to suppress formula calculation until code completes, now it runs in less than a minute instead of over 5 hours.
    Thanks for all the help Mark858

Some videos you may like

User Tag List

Tags for this Thread

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
  •