refedit cell values instead of just the refedit value?

[ratt2581]

What I'm trying to do is use the refedit function in a user form to allow the user to select a group of cells. What I want the form then to be able to do is move those cells (not the refedit value) onto a different spreadsheet. I seem to be getting tied up in being able to parse out the Sheet!refedit value and am unable to copy those cells to a different sheet.

So if the user selected Sheet99!B2:E30 so refedit = Sheet99!B2:E30

I would like Sheet99!B2:E30 copy/moved to a different sheet

Hope this makes sense?

 

[Damon Ostrander]

Hi ratt2581,

All the code you need to copy the range to a different sheet is something like this:

   Range(RefEdit1.Value).Copy Destination:=Sheet2.Range("D7")

This will copy the range to Sheet2 with its upper-left corner cell in D7. This code can be placed, for example, in a commandbutton's Click event to do the copy when the button is clicked.

Keep Excelling.

Damon

 

[ratt2581]

I'm occasionally getting an error when this runs - after I select the cells occasionally it seems to try and reference different cells from the wb i initially ran the macro from.

Example:

Say by refedit1.value = [BookTest]SheetTest1!$A$9:$C$10

When I go to click Run it opens a different workbook and changes the refedit.value to a cell on that worksheet???

Very confusing to me - anyone have any suggestions?

 

[Damon Ostrander]

Hi ratt2581,

I just want to make sure I understand the problem you are having. You mention that it 'opens' a different workbook. Do you really mean that it opens a workbook that was previously closed? Or is it just activating a workbook that is already open but not the active workbook?

Also, are you saying that refedit1.value gets changed? Or is it that the range that gets copied is in a different workbook than what refedit1.value specified? Does it at least copy the value from the correct sheet?

Damon

 

[ratt2581]

I haven't been able to reproduce the error today but what was happening was it was activating a workboook that was already open and the refedit1.value was getting changed to a value in that newly activated workbook. It was strange and I couldn't figure out why it was happening. When it works it works perfectly copying the range specified in the refedit1.value but when the error was occuring it was "working" but the refedit1.value was getting changed to something other than what I had originally selected - if that makes sense?

I'll try a few today and see if I can reproduce the error - hopefully not - and again thank you for your time and help.