Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Replacing shapes on sheet1 with shapes from sheet2

  Asked By: Adelisa    Date: Mar 02    Category: MS Office    Views: 1130
  

Well, here is another project that needs some VB coding to get this to
work. I have a workbook with two sheets: sheet1 and sheet2.

Sheet1 needs to be updated, daily with shapes from Sheet2. The shapes
on sheet1 reside in specific Cells of a particular row, e.g., Row 2,
Cells C, D, E and F.
The replacement shapes reside in Sheet2 in four cells not adjacent to
each other (E5, G5, E8 and G8).

The trick is to get the shapes to update on Screen1 whenever the
workbook is opened, or when it is refreshed.

Eventually, this workbook will have many worksheets. So, the code I
need to develop will need to somehow be able to step through each
sheet. If the sheets have specific names to correspond to "project
names", can the code still refer to the Sheet numbers? I would
definitely appreciate any assistance you all could provide.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Wade Jordan     Answered On: Mar 02

This might give you a start. Might not be the most code  efficient but works You
probably need to call this from a Auto_Open event in the ThisWorkbook module.
"Sheet2 & Picture X" must be modified if necessary to match the name of each
sheet or picture.

Sub ReplacePics()

Application.ScreenUpdating = False

Dim iSheetCount As Integer
Dim iSheet As Integer

iSheetCount = ActiveWorkbook.Worksheets.Count
For iSheet = 1 To iSheetCount
Worksheets(iSheet).Activate
If ActiveSheet.Name <> "Sheet2" Then
Application.DisplayAlerts = False
ActiveSheet.Shapes.SelectAll
Selection.Delete
Application.DisplayAlerts = True
Sheets("Sheet2").Shapes("Picture 1").Copy
ActiveSheet.Range("C2").PasteSpecial
Application.CutCopyMode = False
Sheets("Sheet2").Shapes("Picture 2").Copy
ActiveSheet.Range("D2").PasteSpecial
Application.CutCopyMode = False
Sheets("Sheet2").Shapes("Picture 3").Copy
ActiveSheet.Range("E2").PasteSpecial
Application.CutCopyMode = False
Sheets("Sheet2").Shapes("Picture 4").Copy
ActiveSheet.Range("F2").PasteSpecial
Application.CutCopyMode = False
End If
Next iSheet
Application.ScreenUpdating = True
End Sub

 
Answer #2    Answered By: Roderick King     Answered On: Mar 02

I do have a question. What if I have
more than one worksheet, with more than picture (shape)? The reason
I ask is because worksheets  are being added dynamically, as more are
needed, using a template we provide. The worksheet has the pictures
already embedded in their respective cells, but I think that their
names change ("Picture 275" versus "Oval 15" for example). Now I
don't mind selecting each cell on the main worksheet (worksheet1,
called "Dashboard") and deleting the shape in that cell, but I'm not
sure how to do that. Also, on the first worksheet (the dashboard)
there is a legend that displays pictures I don't want to delete. So
a universal "Shapes.SelectAll" may not suffice. Perhaps there is a
way?? to make those shapes  found in the legend "read-only"
(protected in some way) so they can't be deleted?? What do you
advise?

I am making a mock-up of the dashboard workbook, with dummy data,
etc., so you can see what I am working with from a structural point
of view.

 
Answer #3    Answered By: Fatih Farooq     Answered On: Mar 02

Sorry it took so long, I have been busy.

You might take a look here to get an idea of how to work with shapes.

www.rondebruin.nl/controlsobjectsworksheet.htm

The code  I sent doesn't care how many worksheets  are in the workbook  or what
their names  are. When it is run, it will work on every sheet  unless code is
added to skip other sheets.

The code I gave you was as requested using cell addresses. If this is the way
your document is set up then you will need to insert code for every shape. If
there is a way you can set the shapes  in the same cells  as where it will be
pasted in the other sheet, then you can copy shapes and paste them all in one
code line. If all shapes are copied, without calling a shape name, it is very
quick.

If you are using cell locations and shape names, then turn on your Macro
Recorder and it will record your moves. Send me the code and I will clean it up
for you.

 
Didn't find what you were looking for? Find more on Replacing shapes on sheet1 with shapes from sheet2 Or get search suggestion and latest updates.




Tagged: