VBA code to copy specific columns to another sheet

[SandiC]

This is my 1st time posting. Help would be greatly appreciated!

I need a code that will copy Columns A thru D from sheet1 starting at line 5, my sheet name is "In Progress" , to sheet2 called "Completed" next available row, when in column A a date is entered and I don't want to copy formatting. Also then delete from Sheet1.

Thanks for any help!

 

[Ezguy4u]

SandiC Let's get the ball rolling. We have to start somewhere. Now here is my solution to your problem. This usually generates more questions than answers. So let the discussion begin. I anticipate more questions and problems so let us know. Don't forget to add a sheet name Completed. Also I need to know which cell in column A a date will be entered. When we know that we can add the event handler.

Sub Program1()
'
    Sheets("Completed").Select
    Columns("A:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A5").Select
    Sheets("In Progress").Select
    Range("A5").Select
    Set Rng = Sheets("In Progress").UsedRange
    Rng.Select
'    Range("A5:D10").Select
    Selection.Copy
    Sheets("Completed").Select
    Range("A5").Select
    ActiveSheet.Paste
    Sheets("In Progress").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A5").Select
End Sub

24-08-14 work 1.xlsm
	A	B	C	D
5	Data 1	Data 1	Data 1	Data 1
6	Data 1	Data 1	Data 1	Data 1
7	Data 1	Data 1	Data 1	Data 1
8	Data 1	Data 1	Data 1	Data 1
9	Data 1	Data 1	Data 1	Data 1
10	Data 1	Data 1	Data 1	Data 1
In Progress

 

[SandiC]

SandiC Let's get the ball rolling. We have to start somewhere. Now here is my solution to your problem. This usually generates more questions than answers. So let the discussion begin. I anticipate more questions and problems so let us know. Don't forget to add a sheet name Completed. Also I need to know which cell in column A a date will be entered. When we know that we can add the event handler.

Sub Program1()
'
    Sheets("Completed").Select
    Columns("A:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A5").Select
    Sheets("In Progress").Select
    Range("A5").Select
    Set Rng = Sheets("In Progress").UsedRange
    Rng.Select
'    Range("A5:D10").Select
    Selection.Copy
    Sheets("Completed").Select
    Range("A5").Select
    ActiveSheet.Paste
    Sheets("In Progress").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A5").Select
End Sub

24-08-14 work 1.xlsm
	A	B	C	D
5	Data 1	Data 1	Data 1	Data 1
6	Data 1	Data 1	Data 1	Data 1
7	Data 1	Data 1	Data 1	Data 1
8	Data 1	Data 1	Data 1	Data 1
9	Data 1	Data 1	Data 1	Data 1
10	Data 1	Data 1	Data 1	Data 1
In Progress

Thanks for you quick response. I know VERY little about coding.
I have included pictures of my excel file. So this is what I am trying to do.

1.) when anywhere in Column A in the "In Progress" sheet a date is entered copy columns A thru D of that row to the next available row in the " Completed" sheet with no formatting or anything, just the values
2.)then delete the row from "In Progress"

Thanks for any help

 

[SandiC]

This code does everything I need except it copies formatting and I don't want that.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "Code is running"
' Check to see only one cell updated
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub

If IsDate(Target) Then
Application.EnableEvents = False


' Copy columns A to D to Completed sheet in next available row
Range(Cells(Target.Row, "A"), Cells(Target.Row, "D")).Copy Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
' Delete current row after copied
Rows(Target.Row).Delete
Application.EnableEvents = True
End If

End Sub

 

[Ezguy4u]

Ok Sandic, you now have to decide if you want to spend the time to download XL2BB. If you are planning to use Mr. Excel, for any reason, you really need to be able to copy and paste using XL2BB. Pictures just don't get it. Now I am also going to add that XL2BB is no guarantee that you will get the solution to your problem. Using XL2BB is a big step in the right direction but the problem still has to make sense. We need to test your coding with your worksheets.

 

[SandiC]

Thank you for all your help
Thanks for the advice, I will download it for next time.
I don't know how, but I figured the code out to Copy certain columns from sheet1 to another sheet when criteria is met and paste only values in the next available empty row.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


' Check to see only one cell updated

If Target.CountLarge > 1 Then Exit Sub

If Target.Column <> 1 Then Exit Sub


If IsDate(Target) Then

Application.EnableEvents = False


' Copy columns A to D

Range(Cells(Target.Row, "A"), Cells(Target.Row, "D")).Copy


'Paste to Completed sheet in next available row checking column B (because A is sometimes empty) for blanks and pasting 1 down -1 to left (to the A column)

Sheets("Completed").Cells(Rows.Count, "B").End(xlUp).Offset(1, -1).PasteSpecial Paste:=xlPasteValues


' Delete current row after copied

Rows(Target.Row).Delete

Application.EnableEvents = True



End If



End Sub