code to select range, copy and paste to fit into cell 1

[Allilue]

Hello,

I have a range that varies in size (number of rows and columns) depending on hidden cells. I'd like to copy the range and paste this into a cell in another sheet. The only thing is I want to resize the selected and copied range and make it fit into the cell.

So if the range is A1:Z50 (again, some rows and some columns will be hidden), then I want to copy the selection and paste it into Sheet2 cell B2. I've already expanded B2 to a fixed column width and row height, so this is not changeable.

At the moment I'm doing this manually by pasting the selection into a textbox and resizing this. Is there code that can make this easier and automated?

Many thanks,
Allison

 

[SkipVought]

Hi,

"At the moment I'm doing this manually by pasting the selection into a textbox and resizing this."

Turn on your macro recorder and do that.

Post back with your code if you still need help.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Allilue]

Hi,
I'v'e got the following code:

Sub ButtonEmail_Click()
'
' ButtonEmail_Click Macro

Range("B4:AX100").Select
Selection.Copy
Sheets("App'l Email").Select
ActiveSheet.Shapes("Text Box 20").Select
ActiveSheet.Paste
Selection.ShapeRange.ScaleWidth 0.19, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.19, msoFalse, msoScaleFromTopLeft
End Sub

Everything works except for the end where I want to resize the selected range and fit this into the specified cell, A6. Can you help with this please?

Thanks,
Allison

 

[SkipVought]

When you say that you are RESIZING the selection is that the TextBox that you are resizing?

Sub ButtonEmail_Click()
'
' ButtonEmail_Click Macro
    Dim oData As New DataObject

    Sheets(1).Range("B4:AX100").Copy
    oData.GetFromClipboard
    With Sheets(2)
        With .Shapes(1)
            .OLEFormat.Object.Object.Value = oData.GetText(1)
            .Top = Sheets(2).[B2].Top
            .Left = Sheets(2).[B2].Left
            .Width = Sheets(2).[B2].Width
            .Height = Sheets(2).[B2].Height
        End With
    End With
End Sub

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Allilue]

Hi again, sorry but i'm just being dense.

Can you help me with
.OLEFormat.Object.Object.Value = oData.GetText(1)
and what I need to enter after GetText?

Thanks,
Allison

 

[SkipVought]

"...and what I need to enter after GetText"

I'd suggest that you enter NOTHING. Are you not just using copy 'n' paste to record the code?

What seems to be the problem?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Allilue]

ok, so this is what i have:


Sub ButtonEmail_Click()
'
' ButtonEmail_Click Macro
Dim oData As New DataObject

Sheet16.Range("B4:AX100").Copy
oData.GetFromClipboard
With Sheet11
With .Shapes("Text Box 20")
.OLEFormat.Object.Object.Value = oData.GetText
.Top = Sheet11.[B2].Top
.Left = Sheet11.[B2].Left
.Width = Sheet11.[B2].Width
.Height = Sheet11.[B2].Height
End With
End With
End Sub


I keep getting an error on the line i mentioned above. Does this make sense?

THanks

 

[SkipVought]

What is the exact error that you are getting on statement...

    .OLEFormat.Object.Object.Value = oData.GetText
[/code

Skip,
[sub]
[glasses] [b]To be safe on the [red]FOURTH[/red],[/b]
[b]Don't take a [red]FIFTH[/red] on the [red]THIRD[/red][/b]
[b]Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red][/b]
[b][red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white][/b] [tongue][/sub]

 

[Allilue]

The error is

Run-Time error '438'
Object doesn't support this property or method

When I click on Debug, the line
.OLEFormat.Object.Object.Value = oData.GetText
is highlighted.

 

[WalkerEvans]

It makes sense that you would have an error:

.OLEFormat.Object.Object.Value = oData.GetText

You're missing the subscript on GetText; this may be what you were asking Skip about, but he (and I) didn't understand that as your question. Try putting in the "(1)" and see what happens.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[Allilue]

Hiya,

Sorry, I'm not that good at this but have been working on something that I need to make work... :-(

But the (1)... I'm not sure what should be in here? Is it reference to the sheet, tab, textbox..?

 

[PHV]

But the (1)... I'm not sure what should be in here?
Place your cursor inside the GetText word in your code and press the F1 key (This is named context sensitive help ...)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

HELP is your friend!

You do not need ANYONE's permission to use yours.

Failing to do so, will render you HELPLESS!

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}