0

I am using the below code to try to copy and paste B4 from sheet 10 to sheet 6 and then in column b of sheet 6 enter a time stamp.

However, I am getting the subscript out of range error on the line of code where the * is.

 Application.ScreenUpdating = False
 Dim copySheet As Worksheet
 Dim pasteSheet As Worksheet

 Set copySheet = ThisWorkbook.Sheets(Sheet10Name)   ****
 Set pasteSheet = ThisWorkbook.Sheets(Sheet6Name)

 copySheet.Range("B4").Copy
 pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Range("B" & (ActiveCell.Row)).Select
 ActiveCell.Value = Now()
 Application.CutCopyMode = False
 Application.ScreenUpdating = True

Now apparently I am not getting the now() to insert the time stamp in column b of sheet6.

Correct end result

 Application.ScreenUpdating = False
 Dim copySheet As Worksheet
 Dim pasteSheet As Worksheet

 Set copySheet = ThisWorkbook.Sheets(Sheet10.Name)
 Set pasteSheet = ThisWorkbook.Sheets(Sheet6.Name)

 copySheet.Range("B4").Copy
 pasteSheet.Cells(pasteSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 pasteSheet.Cells(pasteSheet.Rows.Count, 1).End(xlUp).Offset(0, 1).Value = Now()
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
Doug Coats
  • 6,255
  • 9
  • 27
  • 49

1 Answers1

2

I think it's the way you're using (perhaps misusing) the Sheets() part. Try this:

Sub t()
Application.ScreenUpdating = False
 Dim copySheet As Worksheet
 Dim pasteSheet As Worksheet

 Set copySheet = ThisWorkbook.Sheets(Sheet10.Name)
 Set pasteSheet = ThisWorkbook.Sheets(Sheet6.Name)

 copySheet.Range("B4").Copy
 pasteSheet.Cells(pasteSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 pasteSheet.Range("B" & (ActiveCell.Row)).Select
 ActiveCell.Value = Now()
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
End Sub

Also note that I explicitly gave the sheet in your Rows.Count and Range("B" & Activecell...) parts, which should help too. Without that, you'd likely get some errors as you switch worksheets.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • (SheetX.Name) fixed that issue, but now it wont insert the now() timestamp. I may make another question for this unless you have some advice on that? also Range("B" & (ActiveCell.Row)).Select worked but when you stated the pastSheet in front of it it errored out. – Doug Coats Dec 30 '15 at 16:45
  • 1
    @DougCoats - I have a feeling that it's due to the `ActiveCell` part, not the `now()` part. What cell do you want to put the `Now()` info in? Let's instead use something like `Cells(1,1).Value = Now()`, but we need to determine which cell you want the active cell to be. – BruceWayne Dec 30 '15 at 16:47
  • i guess i am still a new. I thought "pasteSheet.Range("B" & (ActiveCell.Row)).Select" was making the active row of column b the active cell? or does it need to be pasteSheet.Range("B" & (ActiveCell.Row)).Value= Now() ? – Doug Coats Dec 30 '15 at 16:49
  • 1
    @DougCoats - Check out [this thread](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) on how to avoid using `.Select`, which is best practice for VBA. It looks like you're starting to see how to do that...and you're correct (and I just overlooked that part) that `.select` makes it active. So yes, try using `pasteSheet.Range("B" & (ActiveCell.Row)).Value= Now() `. However, I'd like to remove all `.Select` - so it looks like the cell you want with `Now()` is going to be the last row of your pasted range, plus one row, correct? – BruceWayne Dec 30 '15 at 16:58
  • Correct. Though this did work, when I try activating this multiple times the timestamp doesnt go into the active row, and only goes in b3 (weird) and replaces the timestamp each time. I have a feeling I need to redo that whole line to include an offset reasoning – Doug Coats Dec 30 '15 at 17:06
  • 1
    @DougCoats - Yes! If you can add some logic that determines the cell, do that. It's tricky when you use `.Select` and `ActiveCell` (`Active` anything really). I'll tweak the code in a minute. – BruceWayne Dec 30 '15 at 17:12