copy a whole row from one sheet to another in excel

Question

I'm using the code below to copy a complete line from my inventory tab (ECC) to my sales tab (2026) and also copy it to a month tab, i have each month by the first three letters. I want to execute this macro by selecting Competed in column AF when the sale is complete. HansV MVP helped me with this and it worked, but I had to do some adjustments on it. now I'm getting debug errors.

the first error i'm getting currently highlighted i yellow is

Set monthSheet = ThisWorkbook.Worksheets(Left(Format(cel.Offset(0, 10).Value, "mmm"), 3))

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Dim cel As Range

Dim targetSheet As Worksheet

Dim monthSheet As Worksheet

' Edited cells in column AF

Set rng = Intersect(Range("AF2:AF" & Rows.Count), Target)

If Not rng Is Nothing Then

    Application.ScreenUpdating = False

    Application.EnableEvents = False

    Set targetSheet = ThisWorkbook.Worksheets("2026")

    ' Loop through the changed cells

    For Each cel In rng

        Select Case UCase(cel.Value)

            ' Check if cell contains "COMPLETED"

            Case "COMPLETED"

                ' Copy the entire row to the 2026 sheet

                cel.EntireRow.Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)

                ' Set the month sheet

                Set monthSheet = ThisWorkbook.Worksheets(Left(Format(cel.Offset(0, 10).Value, "mmm"), 3))

                ' Copy the entire row to the target sheet

                cel.EntireRow.Copy Destination:=monthSheet.Cells(monthSheet.Rows.Count, "A").End(xlUp).Offset(1)

                ' Add row to range to be deleted

        End Select

    Next cel

    ' Delete the rows from the source sheet

    Application.EnableEvents = True

    Application.ScreenUpdating = True

End If

End Sub

Answer 1

Hi Karl

Regarding "...then copy it to the correct month based on the date in column J. I keep getting an error."

The reason is that this piece of code cel.Offset(0, 10).Value returns the value from a cell, 10 columns to the right of the AF column, i.e the value in the column AP. Therefore, if you don't have a date value in the AP column, it would return an ERROR.

So, please, replace

Set monthSheet = ThisWorkbook.Worksheets(UCase(Left(Format(cel.Offset(0, 10).Value, "mmm"), 3)))

With

Set monthSheet = ThisWorkbook.Worksheets(UCase(Left(Format(Cells(cel.Row, "J").Value, "mmm"), 3)))

With Cells(cel.Row, "J").Value in this case is a better code syntax, since you can directly type the column letter ("J"), from which you want to return the value.

I hope this finally helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Dim cel As Range

Dim targetSheet As Worksheet

Dim monthSheet As Worksheet

' Edited cells in column AF

Set rng = Intersect(Range("AF2:AF" & Rows.Count), Target)

If Not rng Is Nothing Then

    Application.ScreenUpdating = False

    Application.EnableEvents = False

    Set targetSheet = ThisWorkbook.Worksheets("2026")

    ' Loop through the changed cells

    For Each cel In rng

        Select Case UCase(cel.Value)

            ' Check if cell contains "COMPLETED"

            Case "COMPLETED"

                ' Copy the entire row to the 2026 sheet

                cel.EntireRow.Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)

                ' Set the month sheet

                Set monthSheet = ThisWorkbook.Worksheets(UCase(Left(Format(cel.Offset(0, 10).Value, "mmm"), 3)))

                ' Copy the entire row to the target sheet

                cel.EntireRow.Copy Destination:=monthSheet.Cells(monthSheet.Rows.Count, "A").End(xlUp).Offset(1)

                ' Add row to range to be deleted

        End Select

    Next cel

    ' Delete the rows from the source sheet

    Application.EnableEvents = True

    Application.ScreenUpdating = True

End If

End Sub

I know you said you tried this and it worked, is there a better way to do this or a better VBA code?

1. I'm trying to move the line from ECC tab once I select Completed in column AF to 2026 tab and then copy it to the correct month based on date in column J. I keep getting an error.

Answer 3

Hi @KARL HEINE, 

My name is Jeovany, and I'd like to help you.

Looking closely at your description and pictures,

I noticed that you named each month's sheets in capital letters, i.e. JAN, FEB,...DEC.

Here, the Left(Format(cel.Offset(0, 10).Value, "mmm"), 3) part returns the months as Jan, Feb,...,Dec

Hence the ERROR

Please replace the error line

Set monthSheet = ThisWorkbook.Worksheets(Left(Format(cel.Offset(0, 10).Value, "mmm"), 3))

With this line

Set monthSheet = ThisWorkbook.Worksheets(UCase(Left(Format(cel.Offset(0, 10).Value, "mmm"), 3)))

This will solve your problem. I tested on my side, and it works OK.

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 4

You can put a breakpoint on the Copy statement. When the macro pauses, enter some print commands to look at values of interest. For example ?cel.address will tell you which cell is currently being processed and ?cel.offset(0,10).value will show you the value you are trying to format. You will also be able to look at targetSheet and determine if the copy worked as you wanted it to.

Answer 5

Dear @KARL HEINE, 

Thank you for posting your question in the Microsoft Q&A forum.  

I’ve been looking closely at your requirements; while waiting for HansV to reply, you can also refer to these two forums Stack Overflow and Tech Community - they are the definitive gold standard for coding challenges like this. 

To ensure you receive the highest level of technical expertise, I strongly recommend sharing this query on the Newest Questions - Stack Overflow and Contributing to the Community | Microsoft Community Hub. The communities there consists of developers who specialize in VBA and can offer optimized code snippets that are often beyond the scope of general application support. Posting there will put your code in front of thousands of experts who can help you refine the logic or identify syntax conflicts that might be hidden. 

I greatly appreciate your understanding. I hope you quickly find a solution for your issue! 

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.