refedit cell values instead of just the refedit value?

ratt2581

Board Regular
Joined
Nov 11, 2006
Messages
100
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?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi ratt2581,

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

Code:
   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

Board Regular
Joined
Nov 11, 2006
Messages
100
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Board Regular
Joined
Nov 11, 2006
Messages
100
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.
 

Forum statistics

Threads
1,181,724
Messages
5,931,688
Members
436,798
Latest member
spprtpplcm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top