Exclude image

[Gav]

hi all

The following code copies and pastes a data range "appendix" to a sheet "contract". How do i amend so that the images within this range do not copy....only the data (text) does??

Cheers!!!

Private Sub CommandButton1_Click(
Dim tm
tmp = Worksheets("quote2").Range("appendix").Rows.Coun
Worksheets("contract").Activat
Cells.Find(What:="appendix1.0", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart).Offset(2, 0).Selec
Selection.Resize(tmp, 1).EntireRow.Inser
Worksheets("quote2").Range("appendix").Cop
ActiveSheet.Past
Application.CutCopyMode = Fals
End Sub

 

[Melanie Breden]

Hi,

The following code copies and pastes a data range "appendix" to a sheet "contract". How do i amend so that the images within
this range do not copy....only the data (text) does???

try this:

Private Sub CommandButton1_Click()
Dim rngSource As Range
Dim rngFind As Range

Set rngSource = Worksheets("quote2").Range("appendix")
Set rngFind = Worksheets("contract").Cells.Find( _
What:="appendix1.0", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart)

If Not rngFind Is Nothing Then
Application.Goto rngFind.Offset(2, 0)
Selection.Resize(rngSource.Rows.Count, 1).EntireRow.Insert
Selection.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
End If
End Sub

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Gav]

Hi Melanie

Thank you!! Ive tried your suggestion but im not sure of the formatting. With your code, the formatting of the cells seems to change. In a perfect world, i would like the code i have originally to do what it does....just without the images!!! Id even like a border i have in some cells to remain. (im probably dreaming). I suppose i can delete the images once they paste?

Please bare with me....im a beginner. When i tried your code, the formatting of the cells changed and the text ended up all centered and bold. Ive checked the formatting on original cells and it seems ok but when cells are inserted, it alters. Any ideas??

Cheers and thank you again!!!

----- Melanie Breden wrote: ----

Hi

The following code copies and pastes a data range "appendix" to a sheet "contract". How do i amend so that the images withi
this range do not copy....only the data (text) does??

try this

Private Sub CommandButton1_Click(
Dim rngSource As Rang
Dim rngFind As Rang

Set rngSource = Worksheets("quote2").Range("appendix"
Set rngFind = Worksheets("contract").Cells.Find(
What:="appendix1.0", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart

If Not rngFind Is Nothing The
Application.Goto rngFind.Offset(2, 0
Selection.Resize(rngSource.Rows.Count, 1).EntireRow.Inser
Selection.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Valu
End I
End Su

--
Regard
Melanie Brede
- Microsoft MVP für Excel

http://excel.codebooks.de (Das Excel-VBA Codebook

 

[Melanie Breden]

Hi Gav,

Thank you!! Ive tried your suggestion but im not sure of the formatting. With your code, the formatting of the cells seems to
change. In a perfect world, i would like the code i have originally to do what it does....just without the images!!! Id even
like a border i have in some cells to remain. (im probably dreaming). I suppose i can delete the images once they paste??

if otherwise no other DrawingObjects are on the sheet, delete simply all images:

Private Sub CommandButton1_Click()
Dim rngSource As Range
Dim rngFind As Range

Set rngSource = Worksheets("quote2").Range("appendix")
Set rngFind = Worksheets("contract").Cells.Find( _
What:="appendix1.0", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart)

If Not rngFind Is Nothing Then
Application.Goto rngFind.Offset(2, 0)
Selection.Resize(rngSource.Rows.Count, 1).EntireRow.Insert
rngSource.Copy Selection
ActiveSheet.DrawingObjects.Delete
End If
End Sub

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)