Copy a row, paste its values, move to next row, move to another sheet and etc


New Member
Dec 5, 2016
Hi guys, I am going to say what I am trying to accomplish in words. I have a report I was doing manually. The info is imported into different workbooks from a master excel file. From then i was copying and pasting each row as of the different days of the month. This could be automated so I have this code. The way i think it should be is, after the data is imported, i need to copy the selected row, paste it as values, move to the next row so the next day it could start copying and pasting from the correct row, move to the next sheet, then the next sheet and so on. The code works fine but after I did it for the second workbook i found out that everything is correct with the first sheet, then the second, third and so on sheets , the data from the row below it is being pasted as values and this shouldn't happen since the references must be there to extract data from the master sheet the next day. I can't get to why this is doing it since the code is the same for all the sheets, i just add an "activate" function before the row is being copied and pasted on the second sheet.

Thanks for the help in advance.

Sub CopyAndPasteRowsSalesmen1()

'copy and paste for Worker 1'

Dim irow As Long

irow = ActiveCell.Row

Range(Cells(irow, "A"), Cells(irow, "L")).Copy
Range(Cells(irow, "A"), Cells(irow, "L")).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("A" & ActiveCell.Row + 1).Select

'copy and paste for Worker 2'

Worksheets("Worker 2").Activate

Dim irow2 As Long

irow2 = ActiveCell.Row

Range(Cells(irow, "A"), Cells(irow2, "L")).Copy
Range(Cells(irow, "A"), Cells(irow2, "L")).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("A" & ActiveCell.Row + 1).Select

'copy and paste for Worker 3"

Worksheets("Worker 3").Activate

Dim irow3 As Long

irow3 = ActiveCell.Row

Range(Cells(irow, "A"), Cells(irow3, "O")).Copy
Range(Cells(irow, "A"), Cells(irow3, "O")).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("A" & ActiveCell.Row + 1).Select

'move to First sheet again at the end of cycle'

Application.CutCopyMode = False

End Sub


New Member
Dec 5, 2016
Update: It seems it is not doing it to all the different workbooks. Some are okay and doesn't paste the whole information below the current row....which is strange... I created different modules and I am starting them for each different workbook...could that be the problem ?

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...