Results 1 to 9 of 9

Thread: Help Setting VBA Memory Variables...

  1. #1

    Help Setting VBA Memory Variables...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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...

    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

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    remove the word Set from beginning of line.

  3. #3
    Quote Originally Posted by NoS View Post
    remove the word Set from beginning of line.
    Could have sworn I did that already, but it must have been when I was tying to define those variables as ranges insead of strings.

    Anyway, did that, and making progress, but now hangs at the line: Range("FROMRange:TORange").Select

    with the message Run-time error '1004': Method 'Range' of object '_Global' failed

  4. #4
    The cells I'm referencing contain the addresses of other cells, should I be defining these variables as strings or ranges?

  5. #5
    If I make...

  6. #6
    A couple more posts...

  7. #7
    I'll be able to post a link to my workbook...

  8. #8
    And there we go...


    I've put my test workbook in my Dropbox at the link below if you're interested in taking a look.

    https://www.dropbox.com/s/4e1khypu0f...0AST.xlsm?dl=0

  9. #9
    Still looking for a hand here, if anyone can spot what I'm doing wrong.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •