0

In sheet3 (c6:h6) data will get updated in 3 min.

I need to copy this data to another sheet4 (b6:G6) last row.

Error is coming in "Range("b1").End(xlDown).Offset(1, 0).Select"

Sub test()

Workbooks("nifty1.xlsm").Activate
Sheet3.Select
Range("c6:h6").Copy

Sheet4.Select
Range("b1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Application.OnTime Now + TimeValue("00:03:10"), "test"
End Sub
Community
  • 1
  • 1
mohan kumar
  • 1
  • This error will be produced if column B is empty. use `Range("B1000000").End(xlUp).Offset(1,0).Select` instead – tnavidi Oct 03 '22 at 10:45
  • Avoid Select & Activate.... `With ThisWorkbook.Worksheets("Sheet4"): Workbooks("nifty1.xlsm.xlsm").Worksheets("Sheet3").Range("C6:H6").Copy Destination:=.Cells(.Rows.Count, 2).End(xlUp).Offset(1): End With`. The `:` in the code is used in place of a new line. Sheet tabs are named Sheet4 and Sheet3 rather than using the sheet codename. – Darren Bartrup-Cook Oct 03 '22 at 10:55

1 Answers1

1

You could try:

With Workbooks("nifty1.xlsm")
    .Sheet3.Range("c6:h6").Copy
    .Sheet4.Range("B" & .Sheet4.Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End With

Application.CutCopyMode = False

Application.OnTime Now + TimeValue("00:03:10"), "test"

Please, read How to avoid using Select in Excel VBA

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • 1
    The sheet code names are meant to be used as they are, without a qualifier e.g. `Sheet3`. They refer to sheets in the workbook containing the code (`ThisWorkbook`) which in this case, due to lack of information, is only likely to be the `nifty1.xlsm` workbook. – VBasic2008 Oct 03 '22 at 13:12