Paste chart to the first sheet with specific name beginning with ....

[filido]

Hi,

Is it possible to write a code line that pastes a picture from worksheet A to worksheet B, whose name starts with "Sheet.....". I have multiple sheets named "Sheet1", "Sheet2", "Sheet3" etc. and I only want to copy&paste one picture to the first sheet that comes up first. and I can't use Worksheets("Sheet1") because the number changes everytime since the macro deletes and creates sheets while looping through a new filter.

I'm looking for something like 'Paste the picture to this sheet if it is the first sheet that starts with name "Sheet". Or 'Paste the picture to a sheet whose name starts with "Sheet" and followed by the lowest number, e.g. in case of workbook containing "Sheet13", "Sheet14","Sheet15","Sheet16", the macro would paste the picture to "Sheet13".

 

[DanteAmor]

I can't use Worksheets("Sheet1") because the number changes everytime since the macro deletes and creates sheets while looping through a new filter.

But if you tell the macro to put the smallest sheet at the beginning of all the sheets. Then instead of Worksheets("Sheet1") you could use Worksheets(1).


But if the above is not possible, then try the following:

Sub Copy_Picture()
  Dim sh As Worksheet, n As Long, wmin As Long, s As Worksheet
  Set sh = Sheets("[COLOR=#ff0000]A[/COLOR]") 'Change "[COLOR=#ff0000]A[/COLOR]" to the name of the sheet that contains the picture.
  n = 0
  wmin = Sheets.Count + 1
  For Each s In Sheets
    If InStr(1, LCase(s.Name), LCase("Sheet")) Then
      n = Replace(LCase(s.Name), LCase("Sheet"), "")
      If n < wmin Then
        wmin = n
      End If
    End If
  Next
  If wmin < Sheets.Count + 1 Then
    sh.DrawingObjects(1).Copy
    Sheets("Sheet" & wmin).Paste
  End If
End Sub