1

Below is a code that I have to record the information in the two cells into one cell whenever the data is refreshed and the macro is ran. The top part works fine. The part I am having trouble figuring out is the last line before the "End Sub". This part of the code takes the value given in G3 and pastes it into the next available row in column D (this is completely separate from the top part of the code). My problem is that it is not pasting it into the D column starting at row 2. Instead it is pasting the value in Column T starting in row 27.

I have tried messing around with the code to see if I can get the code to paste it in Column D starting in Row 2 but Im having no luck.

Sub TimeStamp()
'
' TimeStamp Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
    ActiveCell.Formula = "=CONCATENATE(L1,N1)"
    ActiveCell.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Application.CutCopyMode = False

   ActiveSheet.Columns(4).SpecialCells(xlCellTypeLastCell).Offset(1, 0).Value2 = Range("G3").Value2
End Sub
THAT newbie
  • 347
  • 1
  • 9
  • 20

1 Answers1

0

This will get your macro working.

Sub TimeStamp()
' TimeStamp Macro
' Keyboard Shortcut: Ctrl+Shift+T
    With ActiveCell
        .Formula = "=CONCATENATE(L1,N1)"
        .Cells = .Value
    End With

    With ActiveSheet
        .Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = .Range("G3").Value2
    End With

End Sub

While I hesitate to recommend using ActiveCell and ActiveSheet, you have not provided enough specifics to reference these explicitly. I suspect that ActiveCell is actually G3 but that is not certain.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1
  • G3 isn't the active cell. It is the cell that contains the value that is then plugged into Column D. The Active cell is where the TimeStamp goes. However, this works perfect and I thank you for the help and the useful website. Would you happen to know why in this code the concatenate formula ends up doing "Wrap Text"? – THAT newbie Jul 11 '15 at 23:50
  • The rules that Excel uses to arbitrarily assign *Wrap Text* cell formatting have never been clear to be but it does seem to be consistent whether using `=L1&N1` or `=CONCATENATE(L1,N1)` . –  Jul 12 '15 at 00:03
  • i fixed it by recording myself making the column of cells not wraped. Thanks for the help @Jeeped – THAT newbie Jul 12 '15 at 00:39
  • i tried to use the same code `.Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = .Range("G3").Value2'` for Column `A` rather than column `D`and switching the `G3` for either `"L1&N1"` or replacing the Range part with `.Formula = "=CONCATENATE(L1,N1)"`. Neither seem to work, I am trying to do the same thing as before except the range is no longer 1 cell. Now, it's two cells that are combined into one. Think you might have a solution? – THAT newbie Jul 13 '15 at 00:58
  • @THATnewbie - Sorry but I cannot grasp the scope of your question. If your new situation is different enough that it breaks this accepted answer then it should be posed as a new question with a fresh description of the situation. You can refer back to this thread and paste the existing code into the new question. –  Jul 13 '15 at 01:06
  • Ill go ahead and post a new question but i figured since it is the same question as before just with different ranges, you might be able to help. A new question will allow me to give more detail though. – THAT newbie Jul 13 '15 at 01:14