Still learning VBA here, appreciate any help anyone can offer.
Wrote a macro to copy and paste a picture link automatically to another sheet. Trying to set memory values to use as a range in the copy operation, as the range checnges every calendar day (or can be changed by the user).
This code hangs with the "Compile Error: Object Required" error message at the
<Set HOMERange = ActiveWorkbook.Worksheets("Crew Schedule").Range("BDX9").Value> line.
Can't figure out what I'm doing wrong...
Wrote a macro to copy and paste a picture link automatically to another sheet. Trying to set memory values to use as a range in the copy operation, as the range checnges every calendar day (or can be changed by the user).
This code hangs with the "Compile Error: Object Required" error message at the
<Set HOMERange = ActiveWorkbook.Worksheets("Crew Schedule").Range("BDX9").Value> line.
Can't figure out what I'm doing wrong...
Code:
Option Explicit
'
Sub ATESTER()
'
' ATESTER Macro
' Goes in Dashboard worksheet to execute on sheet selection
'
Dim HOMERange As String
Dim FROMRange As String
Dim TOrange As String
'
Set HOMERange = ActiveWorkbook.Worksheets("Crew Schedule").Range("BDX9").Value
Set FROMRange = ActiveWorkbook.Worksheets("Crew Schedule").Range("FROM").Value
Set TOrange = ActiveWorkbook.Worksheets("Crew Schedule").Range("TO").Value
Application.ScreenUpdating = False
'
' Select & delete old linked picture:
'
ActiveSheet.Unprotect
ActiveSheet.Shapes.Range(Array("7DaySkedPic")).Select
Selection.Delete
'
' Go to Crew Schedule sheet, select range for new linked picture & copy it
'
Sheets("Crew Schedule").Select
ActiveSheet.Unprotect
Range("FROMRange:TORange").Select
Selection.Copy
'
' Paste new linked picture into dashboard and reprotect Dashboard sheet
'
Sheets("Dashboard").Select
Range("K8").Select
ActiveSheet.Pictures.Paste(Link:=True).Select
Selection.Name = "7DaySkedPic"
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'
' Reprotect Crew Schedule sheet
'
Sheets("Crew Schedule").Select
Range("HOMERange").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True
' End looking at Dashboard sheet with new picture pasted
Sheets("Dashboard").Select
'
ActiveWorkbook.Save
'
Application.ScreenUpdating = True
'
End Sub