Copying/Pasting from one sheet to another [SOLVED]

Are you using a UserForm inserted into your VB Editor?

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.

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
SourceRange.Copy
DestRange.PasteSpecial xlPasteAll

'Clear the clipboard
Application.CutCopyMode = False

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

Exit Sub

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

End Sub

hmm... I dont like that the forum gets rid of the tabs you put in.... oh well... makes it a little harder to read...;;)

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

Putman:
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)
rng.Copy
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!

and thanks for the info Derk!

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

Sub CopyPasteRanges2() 
'///Tweaked version of Putman's offering eariler in the thread
Dim SourceRange As Range, 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 


'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 


'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 


'perform the copy and paste, using destination clears the clipboard
SourceRange.Copy DestRange(1,1)


'Kill your range objects. Probably not necessary, VB will _ 
      [theoretically] terminate for you, but good practice, _
      in the event of bugs e.g., addref gets called twice, _
      or some circular under the hood reference, also _
      not likely in this case


Set SourceRange = Nothing: Set DestRange = Nothing 


End Sub

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:

Bonjour,

Quote

Originally posted by bearcub
Code runs fine.

Good deal.

Quote

What are bb codes tags?

Check out the faq

Quote

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: http://www.smilies.nl/nut.gif]) would bollocks up an otherwise nice method like this, it seriously detracts from the use of it. [Blocked Image: http://www.smilies.nl/puke.gif]

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: http://www.smilies.nl/happy/wink2.gif]