Copy PIctures from and place in cells

[smhann]

Hello, I was hoping that someone might be able to help me. I have a Master work sheet that has many columns with numbers going down the columns. Inside each column, I have a picture that represents a product for that column of numbers. I have a template sheet that I run a macro that goes to the master and links all the values in the columns I choose. I want to be able to collect the picture associated withe the column that I have choosen when I run the macro and place them into postion in the template worksheet. In my template I can change the value to tell the macro what columns to pull the information from the master. I now need it to pull the pictures too and place it column C5 and D5 depending on which column pictures that I pick from the master. I hope this makes some sense and someone might be able to help me. I copied my VBA code for all 4 macros that I am using so far.

Thank you
Shane



Sub Makenewsheet()
'
' Makenewsheet Macro
' Macro recorded 06/11/2008 by smhann
'
Sheets("Template").Copy Before:=Sheets(3)
Columns("C:C").Replace What:="f", Replacement:=Range("g6").Value, LookAt:=xlPart
Range("H8").Select
Application.CutCopyMode = False
Columns("D:D").Replace What:="f", Replacement:=Range("h6").Value, LookAt:=xlPart
Sheets("Template").Select
Range("G6:H6").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Template (2)").Select
Range("A1").Select
End Sub
Sub Run_All()
'
' Run_All Macro
' Macro recorded 06/11/2008 by smhann
'
'
Application.Run "'Wrapper Centerline Sheet 155.xls'!Makenewsheet"
Application.Run "'Wrapper Centerline Sheet 155.xls'!hiderow155"
Application.Run "'Wrapper Centerline Sheet 155.xls'!MakeName"

End Sub


Sub hiderow155()
'
' hiderow155 Macro
' Macro recorded 11/03/2008 by shann
For Each cell In Range("k19:k193")
If cell.EntireRow.Hidden = True Then
cell.EntireRow.Hidden = False
Else
If cell.Value = "0" Then
cell.EntireRow.Hidden = True
End If
End If
Next cell
End Sub


Sub MakeName()
'
' MakeName Macro
' Macro recorded 11/09/2008 by shann
'
Range("C3:D3").Select
Selection.Copy
Range("L7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L8").Select
Application.CutCopyMode = False
Selection.Copy
Range("L9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L10").Select
Application.CutCopyMode = False
ActiveSheet.Name = Range("l9").Value
Range("A1").Select
End Sub




How to copy picture........ This is all I have so far but it will not go and get the picture I want depending on the columns that I choose when I run the macro from the template worksheet.

Sheets("Master").Select
Range("F5").Select
Selection.Copy
Sheets("Template").Select
Range("C5").Select
ActiveSheet.Paste
Sheets("Master").Select
Range("G5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Template").Select
Range("D5").Select
ActiveSheet.Paste
Range("A1").Select



Thank you again if you can help.

 

[BrianB]

Your code is not correctly commented or formatted. No-one is likely to try to read it. It is often difficult to understand someone else's code.

In general terms we need to know the name of the picture (as it appears in the box above cell A1). This makes it unnecessary to select anything, or worry about which worksheet is active. A good way of doing this is to have the name in the cell that is covered by the picture.

Here is some basic code :-

Sub test()
'=============================================================================
'- COPY/PASTE A PICTURE FROM ONE SHEET TO ANOTHER
'- does not matter if either sheet is activeor not
'=============================================================================
Sub test()
    Dim MyPicture As String
    '--------------------------------------------------------------------------
    MyPicture = Worksheets("Sheet1").Range("A1").Value  ' eg. "Picture 1"
    '--------------------------------------------------------------------------
    '- COPY
    Worksheets("Sheet1").Pictures(MyPicture).Copy
    '--------------------------------------------------------------------------
    '- PASTE - RE-POSTION  - ADD THE NAME TO THE CELL
    With Worksheets("Sheet2")
        .Paste Destination:=.Range("A1")
        .Pictures(MyPicture).Top = 10
        .Pictures(MyPicture).Left = 10
        .Range("A1").Value = MyPicture  ' add the name to the cell
    End With
    '--------------------------------------------------------------------------
End Sub
'=============================================================================

 

[Felix Atagong]

I just tried above code to copy/pase and reposition an image through vba but the bit

.Pictures(MyPicture).Top = 10
.Pictures(MyPicture).Left = 10

gives a syntax error on my Excel 2000.

(I have this problem that when I copy images they often jump a bit and are placed on top of a cell that contains text...)

 

[BrianB]

There should be no problems with simple code like this. As you can see, I am using XL2000 too. Generally we need to know the exact error message you get, and on what exact line of code.

I suggest you check your spelling of the picture name.

You can see that your other code is superfluous until you get this bit working. I suggest you record a macro of just the copy/paste process which may give a clue.

 

[Felix Atagong]

The images need to have the same name on both pages.

If Picture 1 is pasted to sheet 2 but gets another name over there, for instance Picture 5 the macro will not continue as it will try to move a non-existent Picture 1.

So before repositioning the copied image you first need to find out whatever its new name is...

 

[Osh]

Are you wanting to resize the image to a cell? If so I'm interested on the outcome of this too.

 

[Felix Atagong]

No, no resizing, just repositioning to the top left cell of every page of the printing range.

I have a macro that chooses an address from an Excel sheet and then creates address labels out of it. On these labels is also the logo of our company.

The macro copies the logo (a gif picture) present on the Excel page and pastes it at the top of every address label (basically every 11 rows for as much rows as there are labels). I always see that there is a positioning difference between the very first label and all the others.