Copying/Pasting from one sheet to another [SOLVED]

  • I will like to use an two inputboxes to copy a range from one worksheet and paste to a specificied range on another worksheet.

    I have a beginning of the code but I don't know how to be able to make it modeless to be copy to point and code to the specified ranges. At present, I have to manually type in the ranges. How can I modify this so it behaves like the Data Validation range box?

    Does this have something to do with the Ref Edit Box?

    Rng = InputBox("Range to Copy")

    Dest = InputBox("Paste Range")
    Sheets("CMA Verification").Range(Dest).PasteSpecial xlPasteAll


  • No.

    I just started thinking about it this morning.

    Do I have to use a Userform to make it work like this or can I just run a macro from the Editor?

    I have never used the Ref Edit on a userform. How would I set it up if I had to?

  • I haven't used refEdit either, but I am playing about with a UserForm now.
    Hopefully someone with experience of this control will give an answer soon.

  • Just use what you have, but add another parameter to your input box. For example

    Rng=INPUTBOX(Prompt:="Range to copy", Type:=8)

    The 8 will let you select the range the way you want to, rather than typing it in.

  • Thank you,

    Now that you mention it, I do remember reading about this a while back but have never had need to use it.

  • I ran the code but I am problem with my copy range. It is not copying what I have in the input box. Seems like my logic is faulty:

    Rng = Application.InputBox(Prompt:="Range to Copy", Type:=8)

    dest = Application.InputBox(Prompt:="Paste Range", Type:=8)
    Range(dest).PasteSpecial xlAll

    The code crashes at the Range(Rng) line.

    I get a Method 'Range' of object' _ Global' Failed error message.

    I even used the ActiveSheet object but it still crashed.

    What do I need to make the code run properly?

  • try the following code... you have to use the 'Set' keyword in assigning the range objects. This code accounts for different sized ranges between source and destination ranges too. Let me know if you have questions...

    Sub CopyPasteRanges()

    Dim SourceRange As Range
    Dim DestRange As Range

    'get the source range to copy
    Set SourceRange = Application.InputBox(prompt:="Select Source Range", _
    Title:="My Title", Type:=8)

    'Check to make sure the user selected a valid range (and didn't Cancel)
    If SourceRange Is Nothing Then
    Exit Sub
    End If

    'get the destination range to copy
    Set DestRange = Application.InputBox(prompt:="Select Destination Range", _
    Title:="My Title", Type:=8)

    'Check to make sure the user selected a valid range (and didn't Cancel)
    If DestRange Is Nothing Then
    Exit Sub
    End If

    'because excel will throw an error if the DestRange is a
    'different size than the sourcerange, just get the upper
    'left cell of the range (which is also the first cell)
    'and paste into that
    Set DestRange = DestRange.Cells(1)

    'perform the copy and paste
    DestRange.PasteSpecial xlPasteAll

    'Clear the clipboard
    Application.CutCopyMode = False

    'Kill your range objects
    Set SourceRange = Nothing
    Set DestRange = Nothing

    Exit Sub

    MsgBox "Error: " & Err.Number & vbCrLf & _
    Exit Sub

    End Sub

  • Thanks Putnam,

    Seems like you have done some programming in other languages (C, Java)?

    I noticed that you wanted to clear memory (i.e. killing the range object).

    Is this so?

    I noticed that if I wanted to copy multiple ranges, I had to insert a comma. Interesting!?

  • Bearcub: My fault I forgot that the inputbox returns a range, not a string, when the 8 parameter is used.

    There is a hand feature on the database called BBCode that lets you prefix your code with the word code surrounded by [] and end your code with /code in brackets. When you do that you get

    Set rng = Application.InputBox(Prompt:="Range to Copy", Type:=8)
    Set dest = Application.InputBox(Prompt:="Paste Range", Type:=8)
    dest.PasteSpecial xlAll

    and any indentations (I had none above) will be preserved.

  • Actually... I have done a little programming in some other languages, but not much. I am really a Finance guy turned programmer. Started out writing Lotus 123 macros for a mutual fund acct dept and then we switched to Excel 97. I took on the task of switching all of our 'automated' Lotus files over to Excel. I happened to pick up Walkenbach's Power Porgramming book and took off with it. From there I went to a real programming company that dealt with finance and was hired for my knowledge of Excel to create their addin. While I have worked on other things, my primary responsibility has been building our robust addin that uses XML to communicate with our remote server to return customized data to Excel. It has been quite a journey!

    But yes... try to keep your memory cleared up (dont rely on Excel to do it) and make turn on Option Explicit! Some of these things really make a world of difference in performance when you get into building larger and larger scaled apps! And it's good practice in cse you eventually decide to move into another language. Also... M$ will eventually be moving everyone over to .NET, including office folks! Of course, this will take some time (XLM macros still work:) ) so you shouldnt get too worried yet! I personally cant wait to start developing Excel natively in C#! That will be exciting!

  • Yeah, I am in Fianance as well. For a while I was taking courses in Java, and VB but suddenly realized that these are substitutes for what I really want to do.

    I have found that programming allows the "artist" in me flow. I have been a frustated musician for years and Excel, VB and Java have allowed me to express myself where I couldn't do it in music. However, my outlook has not changed and look forward to going home and practicing guitar for hours on end, instead of programming!

  • I got to thinking about what I just wrote and realized it may have come off wrong.

    I spent two years trying to learn the basics of programming (taking a year of VB and a year of Java). Naturally, I took these classes so I could understand what I was doing in Excel - which is what I use everyday in my job(as many of you can attest to).

    Now, I think I am ready to learn VBA and all its intricacies.

  • Howdy, here's a tweaked version of Putnam's code/ comments regarding the tweaks (i.e., why) (except my shorthand for .item :bsmile: :(

    The bugger with pastespecial is that Excel actually selects cells *for* you. Daddy no like... Speaking of which, the extra spacing with bb code tags isn't exactly hitting the spot. Is there a reason html isn't enabled here?

    Incidentally, I haven't tested any of this, I'm bored at a thin client and simply wanted to contribute. :tumble:

    Nate Oliver

  • Bonjour,


    Originally posted by bearcub
    Code runs fine.

    Good deal.


    What are bb codes tags?

    Check out the faq


    I liked you statement using "bugger". Do you live outside the US?

    'Tis indeed a bugger as it bugs. Can't figure out why MS (waiting for Dave on this one [Blocked Image:]) would bollocks up an otherwise nice method like this, it seriously detracts from the use of it. [Blocked Image:]

    I live in Minneapolis, Mn, been in the US since I was 15. However, I was born and raised in Canada, so adding u's to words, pronouncing Mom as Mum, dining on pastie/shepards pies, and pronouncing Z as Zed (versus Zee) are not unfamiliar to me, amongst other things...

    Glad to be of help. Have a good one eh. [Blocked Image:]

    Nate Oliver

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!