VBA code for copying and pasting content in multiple worksheets

Question

Please help me with a macro for copying and pasting text from Input sheet (worksheet1) to the Reports sheet (worksheet 2) within the same excel file.

Input Sheet Format:

Name- cell J5

Date- cell J6

Start Time- cell J7

End Time- cell J8

Downtime- cell J9

I need a macro for copying J5:J9 and paste it in A2:E2, using transpose function. Also, the sheet is shared with multiple users so it needs to be pasted in the last available row in the Reports sheet (worksheet 2).

Presently, I am using the following macro code:

Sub Report()

    Sheets("Input").Select
    Range("J5:J9").Select
    Selection.Copy
    Sheets("Reports").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Sheets("Input").Select
    ActiveWindow.SmallScroll Down:=-5

    Sheets("Input").Select
    Range("J5:J9").Select
    Selection.ClearContents

End Sub

I am getting error at "ActiveCell.Offset(1, 0).Select" while running the macro.

Please advise any changes to the code which can help me fixing the issue.

Thanks

Please read How to avoid using Select in Excel VBA and follow it. This will solve your issues. — Pᴇʜ

jamheadart jamheadart · Accepted Answer · 2018-05-08T10:35:43

You can rewrite everything you're doing in a few lines:

Sub Report()
' Copy the range from J5:J9 on INPUT worksheet
ThisWorkbook.Worksheets("Input").Range("J5:J9").Copy
With ThisWorkbook.Worksheets("Reports")
    ' Find the LAST ROW in COLUMN A
    Dim lROW As Long: lROW = .Cells(.Rows.Count, 1).End(xlUp).Row
    ' Paste in cell A-lROW with transpose
    .Range("A" & lROW).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Transpose:=True
End With
' Clear entry in original INPUT sheet
ThisWorkbook.Worksheets("Input").Range("J5:J9").ClearContents
End Sub

I'm not sure why you're getting an error for offsetting by one row, it works for me - but it's probably to do with your method of "selecting" the last row

VBA code for copying and pasting content in multiple worksheets

1 Answers