Macro to automatically copy and paste rows to different Sheet

[prabhu2]

Hi,

I'm new to VBA, but I would really appreciate if you can help with creating a macro to copy rows to different sheet.

There are two version of the same excel file. One of the file(test22) is compatible with excel 97-2003.

test.xlsx

test22.xls

The workbook has two sheets one is 'Names' and the other is 'ColumnF.'

All information will be updated to the "Names" worksheet, which is the main sheet and I would like to automatically move any rows with 'Yes' for ColumnF to the "ColumnF" worksheet. But the rows should only be copied up to ColumnE.

I would also like to have a Yes or No selection under Column F, if possible. Thank you.

[oeldere]

Show in the file the desired (expected) result.

[prabhu2]

Hi,

I have updated the files to reflect the 'expected' result. The worksheet 'ColumnF' should copy up to Column E from the worksheet 'Names.' If you view the file now the worksheet "ColumnF" will show all names with 'Yes' under ColumnF Thanks.

[prabhu2]

Anyone else that can help with this? Thanks...

[AB33]

The code is in sheet name and is fired-on by changing column F to "Yes"

[prabhu2]

Hi,

Thank you AB33 for replying to this thread.

I have made some changes to the original workbook and I had found a piece of code online which helped me to full fill my need, and I would appreciate if you could help me modify it a bit to suit my need.

The new workbook has Sheet1 and Sheet2. Sheet1 contain all information and filtered data needs to be moved over to Sheet2, ColumnF is the filter range.

The rows are being copied from ColumnA to ColumnE. Rest of the columns in Sheet2 will be updated as required.

The issue I have now is to keep the column names unchanged in Sheet2 when information is copied over from Sheet1.

When I run the code the column names in Sheet2 disappearing.

How do I instruct the macro to only copy from the 6th or 7th row? I would also like to add a button to run the macro on Sheet1.

Please see the attached workbook. Many Thanks!


dtest.xlsm

Sub test()
Dim LR As Long, i As Long
Sheets("Sheet2").UsedRange.ClearContents
With Sheets("Sheet1")
    LR = .Range("F" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("F" & i)
            If .Value = "x" Then
                .Offset(, -5).Resize(, 5).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
        End With
    Next i
End With
End Sub

[AB33]

If you are referring to the above code, it copies, starting from 2 in sheet2, so I do not know which one do you want to change.

[AB33]

Oops,
Change this line

Sheets("Sheet2").UsedRange.Offset(1).ClearContents

[prabhu2]

Thank you, that resolved the column names issue. But, If I wanted to start copying from the 4th or 5th how should I modify the code? I'm pretty new to VBA, thanks for helping

[AB33]

For i = 1 To LR

TO

For i = 4 To LR

[prabhu2]

Thank you AB33,

I would like to have Sheet1 to automatically execute the macro in modules, whenever the value in columnF get changed. I found a piece of code for this online but it is for a cell range instead of a column,

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    If Target.Address = "$A$1" Then Process 
End Sub

I have changed the code above to reflect the macro on dtest file which is called 'test'

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    If Target.Address = "$A$1" test 
End Sub

But I'm not sure about the syntax to use for setting up the columnf as range... please help. Thanks

[AB33]

Use and amend the code I gave you earlier (post #5)

[prabhu2]

Thank you, I have modified the code you gave earlier and added to 'sheet1,' and it seems to be updating sheet2 automatically. I have also attached a sub process to this code in sheet1, which pops a message box whenever the ColumnF is modified. I'm curious why this message box appears when I run the macro on Sheet1 from VBA, but it doesn't show the message when I update things on the worksheet. I have pasted the code below. Thanks again AB33!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

 If Target.Column = 6 And Target.Value = "x" Then test

End Sub

Sub Process()
    MsgBox "You changed ColumnF!"
End Sub

[AB33]

You should not mix, standard module codes with event driven codes. If you firing the event code from the module, that code should not be on sheets, but on standard module, so the sub process should be on standard module and sub change even code on the sheet the change events takes place.

[prabhu2]

Hi,

Thanks for the reply, so should I be placing the sub process for the message box on standard module? I have added to the bottom of the code on module and left sheet1 with the

Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

 If Target.Column = 6 And Target.Value = "x" Then test

End Sub

Module1:

Sub test()
Dim LR As Long, i As Long
Sheets("Sheet2").UsedRange.Offset(1).ClearContents
With Sheets("Sheet1")
    LR = .Range("F" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("F" & i)
            If .Value = "x" Then
                .Offset(, -5).Resize(, 5).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
        End With
    Next i
End With
End Sub



Sub Process()
    MsgBox "You changed ColumnF!"
End Sub

But this still not showing the message box...am I doing something wrong here? Thanks

[AB33]

You have called

If Target.Column = 6 And Target.Value = "x" Then test

But not Sub Process()

Or you could include the message within the test code, like

Sub test()
Dim LR As Long, i As Long
Sheets("Sheet2").UsedRange.Offset(1).ClearContents
With Sheets("Sheet1")
    LR = .Range("F" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("F" & i)
            If .Value = "x" Then
                .Offset(, -5).resize(, 5).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
        End With
    Next i
End With
 MsgBox "You changed ColumnF!"
End Sub

[prabhu2]

Great! That works like a charm.

One more thing, the code on Sheet1 automatically updates Sheet2 if we input 'x' under columnF, but the automatic update fails, when we reverse the 'x' to 'n.'

But it works fine if we run the macro from VBA. Any suggestions? Thanks AB33.

[AB33]

This line again, it says "x"

If Target.Column = 6 And Target.Value = "x" Then test

You also need to change the x in to n in sub test

[prabhu2]

Hi AB33,

If I were to change "x" into "n" then the macro will only copy rows if the values are "n" under ColumnF.

The macro on module1 is already copying rows when x values are placed in ColumnF, but the automatic update code on Sheet1 copies rows to Sheet2 and fail to reflect changes on Sheet1 if values are changed from 'x' to 'n'

When I execute the macro form VBA it seems to be updating Sheet2, reflecting changes made to Sheet1.

Is there a way to modify the code on Sheet1 to reflect changes and apply them to Sheet2? If this is not possible,
can you help me with creating a button on Sheet1, which would run the macro on module1? Thank you.

[prabhu2]

For i = 1 To LR

Hi AB33,

I also have another question, I understand the above piece of code define the copy range of the rows from Row1 etc, but what about the pasting range, what If I wanted to paste starting from Row5th per say? Thanks.

I have re-pasted the code on Module1 below. Thanks again for helping me with this

Sub test()
Dim LR As Long, i As Long
Sheets("Sheet2").UsedRange.Offset(1).ClearContents
With Sheets("Sheet1")
    LR = .Range("F" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("F" & i)
            If .Value = "x" Then
                .Offset(, -5).Resize(, 5).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
        End With
    Next i
End With
MsgBox "You changed ColumnF!"
End Sub

[AB33]

This is going to be my last post. I spent few hours on this thread and does not seem the end is in sight.
If you have data in row 4 and nothing below it, the next empty row would be 5, so this line works.

.Offset(, -5).Resize(, 5).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

But if this is not the case.

Sub test()
Dim LR As Long, i As Long, NR&
Sheets("Sheet2").UsedRange.Offset(1).ClearContents
NR = 5
With Sheets("Sheet1")
    LR = .Range("F" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("F" & i)
            If .Value = "x" Then
                .Offset(, -5).Resize(, 5).Copy Sheets("Sheet2").Range("A" & NR)
                NR = NR + 1
            End If
        End With
    Next i
End With
MsgBox "You changed ColumnF!"
End Sub

[prabhu2]

Thank you AB33, that seems to work. Thank you for helping me with the macro. I do not intend to takeaway your personal time. I will be doing some more modifications to the file and if I need anymore help, I'm sure someone else would be able to help here or I'll seek help elsewhere.

I really appreciate your help.
Thanks and have a nice day.

[prabhu2]

I'm glad to say I was able to manipulate the codes to my need Problem is resolved! :D