Copying and pasting content in multiple worksheets

[Kavish Sekhri]

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

EndSub

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

 

[Marc L]

A demonstration of your code revamped :​

Sub Demo1()
    Sheet2.Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 5).Value = [TRANSPOSE(Input!J5:J9)]
    [Input!J5:J9].ClearContents
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

Now if you need to copy source values & formatting :​

Sub Demo2()
    With Sheet1.[J5:J9]
        .Copy
         Sheet2.Cells(Sheet2.UsedRange.Rows.Count + 1, 1).PasteSpecial xlPasteValuesAndNumberFormats, , , True
        .ClearContents
    End With
End Sub

You may Like it !
​