Results 1 to 3 of 3

Thread: Create 3D Cell References with Macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Create 3D Cell References with Macro

    Hi all... me again *sigh*

    I am trying to create a macro that will create a 3d-reference to another sheet - all references need to be added to the sheet 'Reminders' and will be based on the values in the active row on the active sheet.

    I kinda had two macros that did bits of what I wanted and I've tried to put it all together but to no avail... I'm not getting debug messages anymore (yay) but the only part that's working is the part copying the contents of Cell A1 on the active sheet into the last empty row of column A on Reminders... nothing else happens.

    I'll be so, SO super grateful if anyone can help!

    TIA!

    Samantha

    Sub cellRefTEST()
    '
    Dim lastrow As Long


    With ThisWorkbook.Worksheets("Reminders")
    lastrow = FormulaR1C1 = "=ActiveSheet!R[ActiveCell.Row]C[1]"
    '(2, .Cells(.Rows.Count, "B").End(xlUp).Row + 1)

    '.Range("B" & lastrow).
    'Range("A" & ActiveCell.Row).Value =
    'FormulaR1C1 = "=ActiveSheet!R[ActiveCell.Row]C[1]"
    '.Resize(, 16).Value = _


    '.Resize(, 16).Value

    Dim R As Range
    'Range("A1:E1").Copy

    Set R = Worksheets("Reminders").Cells(Rows.Count, 1).End(xlUp) 'last cell in Column A with data
    If Len(R.Value) > 0 Then Set R = R.Offset(1)
    R.Value = ActiveSheet.Range("a1").Value
    End With
    End Sub

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,468
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create 3D Cell References with Macro

    Only a guess, based on the bits of code posted:

    Worksheets("Reminders").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 16).Formula = "='" & ActiveSheet.Name & "'!A" & ActiveCell.Row

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create 3D Cell References with Macro

    Quote Originally Posted by StephenCrump View Post
    Only a guess, based on the bits of code posted:

    Worksheets("Reminders").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 16).Formula = "='" & ActiveSheet.Name & "'!A" & ActiveCell.Row
    PERFECT!!!

    Thank you so much! This was the last major thing to sort out for this project!!

    Many thanks

    Samantha

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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