Help with cut and pasting 1

[simoncpage]

Hi all, can anyone help me with this I would like to be able to cut and paste a whole sheet with formulae and paste it as values. This is fine as long as there arent any merged cells....but when there are it a really problem.

Can anyone help with this or perhaps know of an add-in that does this?

Any help would be most appreciated

Thanks

Simon

 

[mmtraining]

How about copying the whole worksheet (not just the table on the worksheet), highlighting the contents, copying it and then inserting it as values into the same place?

Couldn't test this idea, sorry.

Carol
Berlin, Germany

 

[Behroozme]

If I got you right you just have to:
1. Edit & Copy your worksheet
2. When you past it, use past special and then check the formulas checkbox

This simple thing would able you to past the worksheet with its formula as you asked for.

Hope it helps mate.

Bruce

 

[simoncpage]

That would seem like the obvious solution but excel cant perform this on cells that are merged.

Hence you need to unmerge the cells then do it and then remerge and thats just a pain in the tits!

I was hoping someone would know how to paste the complete format of a sheet including formatting of cells so that I can just simply paste special from one to the other

Simon

 

[RobBroekhuis]

mmtraining's suggestion above is the way to go. More explicitly:

Sheets("OldSheet&quot.Copy
Cells.Copy
cells.PasteSpecial Paste:=xlValues

This worked just fine, including a merged cell.
Rob

 

[simoncpage]

I thought of that the problem is that I only want to extract a certain range not the entire sheet. I guess I could work around this though.

Thanks

 

[RobBroekhuis]

Sounds like there's no easy solution - you'll have to do some extra work whichever way you go. You could use the approach above, and THEN do a normal copy from the new (temporary) worksheet back to where you want the block of cells to go. Not too bad, in terms of extra work...
Rob

 

[simoncpage]

I'm thinking of doing a select area and then copy it and in the new sheet delete everything out side of the selected range.

Thanks

 

[simoncpage]

You dont happen to know of a function that deletes anthing out side of a selected range - by any chance?

Simon

 

[RobBroekhuis]

I don't think there is. But it takes at most four statements to achieve, e.g.

columns("A:F&quot.delete (or .clear)
columns("N:IV&quot.clear
rows("1:11&quot.delete (or .clear)
rows("22:65535&quot.clear

Rob

 

[simoncpage]

Ive got one thanks - if your interested I'll email it to

Simon

 

[RobBroekhuis]

Could you post it? I think it would be of general interest to the forum!
Rob

 

[simoncpage]

Problem is it is easy to see it working in an excel sheet. I'll post it on

http://www.clpuk.com/public/vb

when I have finished it heres the basics:


Private Sub HideRowsAndColumns(ScrollRange)
Dim row1 As Long, row2 As Long, col1 As Long, col2 As Long
Dim MaxRows As Long, MaxCols As Long
MaxRows = ActiveSheet.Rows.Count
MaxCols = ActiveSheet.Columns.Count

Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False

row1 = ScrollRange.Range("A1&quot.Row
row2 = row1 + ScrollRange.Rows.Count - 1

col1 = ScrollRange.Range("A1&quot.Column
col2 = col1 + ScrollRange.Columns.Count - 1

On Error GoTo ErrHandle
If row1 <> 1 Then Range(Cells(1, 1), Cells(row1 - 1, 1)).EntireRow.Hidden = True
If row2 <> MaxRows Then Range(Cells(row2 + 1, 1), Cells(65536, 1)).EntireRow.Hidden = True
If col1 <> 1 Then Range(Cells(1, 1), Cells(1, col1 - 1)).EntireColumn.Hidden = True
If col2 <> MaxCols Then Range(Cells(1, col2 + 1), Cells(1, 256)).EntireColumn.Hidden = True
Application.ScreenUpdating = True
Exit Sub
ErrHandle:
Application.ScreenUpdating = True
MsgBox &quot;An error occurred.&quot; & vbCrLf & Err.Description & vbCrLf & vbCrLf & &quot;Make sure that the rows and columns to be hidden do not contain any charts, graphic objects, or cell comments.&quot;, vbInformation, APPNAMEEx2
On Error Resume Next
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
On Error GoTo 0
End Sub