CODEWITHSUNDEEP
vbaexcel
ks_1029
ks_1029

Reputation: 11

VBA code for copying and pasting content in multiple worksheets

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

Upvotes: 0

Views: 639

Answers (1)

jamheadart
jamheadart

Reputation: 5293

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

Upvotes: 1

Related Questions