0

Thank you in advance for any help. My knowledge is Beginner Level. I can read code but struggle with writing. I'm not sure which line this latest error refers to.

Also, I assume there is always a better (more efficient) way to write code.

This post comes after this one: VBA Excel - How to transfer Values of Named Ranges from Workbook B into same/similar Named Ranges in Workbook A?

After troubleshooting many errors I finally am getting error: Object doesn't support this property or method.

1st Reference Post: excel Copy shapes from one worksheet to another

2nd Reference Post: https://superuser.com/questions/1042887/naming-an-object-in-excel-vba-so-it-can-be-selected-once-its-copied-to-another

The Problem: The shape will copy over but will not be positioned correctly. It is named correctly.

I am attempting the following:
1. store the shape properties of specific shapes in Workbook A
2. copy specific named shapes from Workbook B
3. paste the copied shapes into a specific worksheet in Workbook A
4. apply the stored shape properties to the copied shapes

Here is all the Code:

Sub Button_Transfer_FromOlderVersion()

' Start of Error Handling
    On Error GoTo Errorcatch

' Declare string variable and use current open workbook filename as value
    Dim WorkbookNameNew As String
    WorkbookNameNew = ThisWorkbook.Name

' Declare string variable for 2nd workbook not yet identified
    Dim WorkbookNameOld As String

' Find out the name of the 2nd workbook
' Declare string variable for finding and separating the filename from the path
    Dim OldWorkbookFileName As String

' Show the open dialog and pass the selected file name to the string variable "OldWorkbookFileName"
    OldWorkbookFileName = Application.GetOpenFilename

' If the user cancels finding the workbook file then exit subroutine
    If OldWorkbookFileName = "False" Then Exit Sub

' Troubleshooting: Show me the filename with path of Workbook B
'    MsgBox OldWorkbookFileName

' Troubleshooting: Show me the filename of Workbook A
'    MsgBox WorkbookNameNew

' Open Workbook B which the user just selected
    Workbooks.Open Filename:=OldWorkbookFileName

' Separate the filename from the path for Workbook B
    WorkbookNameOld = Dir(OldWorkbookFileName)

' Troubleshooting: Show me the filename of Workbook B
'    MsgBox WorkbookNameOld

' Temporarily change some settings to speed up the transfer process
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

' Transfer Values of Named Ranges from Workbook B
' Workbooks(WorkbookNameNew).Worksheets("WorksheetName").Range("NamedRange01").Value = Workbooks(WorkbookNameOld).Worksheets("WorksheetName").Range("NamedRange01").Value

' Unprotect specific Worksheet in Workbook A to help eliminate transfer issues of shapes
    Sheet05.Unprotect Password:="MyPassword"

' Declare all variables
    Dim worksheet01 As Worksheet
    Dim worksheet02 As Worksheet
    Dim PictureName01 As String
    Dim PictureName02 As String
    Dim shape01 As Shape
    Dim shape02 As Shape
    Dim shape03 As Shape
    Dim shapeTop As Long
    Dim shapeLeft As Long
    Dim shapeHeight As Long
    Dim shapeWidth As Long

'******* ******* ******* SHAPE 01

 ' Set variables so they are not equal to Nothing
    Set worksheet01 = Workbooks(WorkbookNameNew).Worksheets("WorksheetName")

    ' Identify the name of the existing shape
    PictureName01 = "WorkbookNewNamedPicture01"

    Set shape01 = worksheet01.Shapes(PictureName01)

    Set worksheet02 = Workbooks(WorkbookNameOld).Worksheets("WorksheetName")

    ' Identify the name of the existing shape
    PictureName02 = "WorkbookOldNamedPicture01"

    Set shape02 = worksheet02.Shapes(PictureName02)

    Set shape03 = shape01

' Capture properties of exisitng picture such as location and size
' Measurements in points must be converted from desired inches. Use http://www.thecalculatorsite.com/conversions/length/points-to-inches.php

    shapeTop = shape01.Top
    shapeLeft = shape01.Left
    shapeHeight = shape01.Height
    shapeWidth = shape01.Width

' Copy first shape in Workbook B
    worksheet02.Shapes(PictureName02).Copy

' Delete existing shape in Workbook A
    worksheet01.Shapes(PictureName01).Delete

' Paste the copied shape into Workbook A
    worksheet01.Paste

' Identify and select the most recently added shape in Workbook A
    Set shape03 = worksheet01.Shapes(worksheet01.Shapes.Count)

' Reapply shape properties to the recently added shape in Workbook A
    shape03.Top = shapeTop
    shape03.Left = shapeLeft
    shape03.Height = shapeHeight
    shape03.Width = shapeWidth

' Apply expected name to the recently added shape in Workbook A
    shape03.Name = "WorkbookNewNamedPicture01"

'******* ******* ******* SHAPE 02

'******* ******* ******* SHAPE 03

'******* ******* ******* SHAPE 04

'******* ******* ******* SHAPE 05

'******* ******* ******* SHAPE 06

' User Feedback of successful transfer and name of Workbook B
    MsgBox ("TRANSFER COMPLETED FROM:" & " " & WorkbookNameOld)

' Re-protect specifc worksheet in Workbook A that received new shapes from Workbook B
    Sheet05.Protect Password:="MyPassword"

' Restore the settings that were changed temporarily to speed up the transfer process
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

Exit Sub

' Finish Error Handling
    Errorcatch:
    MsgBox Err.Description

End Sub
TechGuy737
  • 5
  • 4
  • 1
    How is it not positioned properly? Is it consistently in the same incorrect place? Is the code you posted an [mcve]? Or can you perhaps remove extra space that helps us read/undertand it? If you step through your code with `F8`, do you know where exactly things go wrong? – BruceWayne Sep 17 '18 at 19:08
  • I have to seriously applaud your use of comments for future users of your macro but I don't think they're totally necessary for posting on this site – Marcucciboy2 Sep 17 '18 at 19:15
  • @BruceWayne It always positions itself to whatever range was last selected on the worksheet when the workbook was saved. My apologies on not creating a better example per those directions. I will work on editing it. And no, I don't know where exactly it goes wrong because it appears to jump through a bunch of code and generate the error. I'm still learning how to debug. Maybe my error handling is not helping me. – TechGuy737 Sep 17 '18 at 19:54
  • @Marcucciboy2 Thanks! I'm still learning programming and trying to teach myself as I go. – TechGuy737 Sep 17 '18 at 19:58
  • 1
    Ah! Remove (or comment out) the `On Error Goto...` line so it errors. When/if it does, hit "Debug" and let us know the error and line. Also, stepping through the code line by line with `F8` can help as well. – BruceWayne Sep 17 '18 at 20:08
  • @BruceWayne Thanks! That helped me discover that it was this line of code: `Set shape03 = worksheet01.Shapes(shape03.Shapes.Count)` – TechGuy737 Sep 17 '18 at 21:05
  • @TechGuy737 - Ah, then perhaps you need `...worksheet01.Shapes(worksheet01.Shapes.Count)`? I think the issue is you're using a `Shape` object (`shape03`) and using `Count` on that, instead of a worksheet. – BruceWayne Sep 17 '18 at 21:09
  • @BruceWayne That was the answer I've been trying to resolve the past 2 days!! Thank you so much! I've been super frustrated with not being able to figure this out. I'll edit my posted code so that it reflects your answer plus a couple more debugging things I found after. Next time you're in town I'll take you out for a drink. – TechGuy737 Sep 17 '18 at 21:33
  • @TechGuy737 - Actually, don't edit your code in the OP. In the future, if someone comes in with a similar problem, they won't see the original code. Keep it as-is, and if anything, make a small, short edit. – BruceWayne Sep 17 '18 at 21:35

1 Answers1

0

As noted in the comments, you're erroring on the line Set shape03 = worksheet01.Shapes(shape03.Shapes.Count)

You can't count a shape object. Instead, use:

Set shape03 = worksheet01.Shapes(worksheet01.Shapes.Count)
BruceWayne
  • 22,923
  • 15
  • 65
  • 110