VBA code to copy specific columns to another sheet

SandiC

New Member
Joined
Aug 14, 2024
Messages
4
Office Version
  1. 2021
Platform
  1. Windows
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!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.

VBA Code:
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
ABCD
5Data 1Data 1Data 1Data 1
6Data 1Data 1Data 1Data 1
7Data 1Data 1Data 1Data 1
8Data 1Data 1Data 1Data 1
9Data 1Data 1Data 1Data 1
10Data 1Data 1Data 1Data 1
In Progress
 
Upvote 0
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.

VBA Code:
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
ABCD
5Data 1Data 1Data 1Data 1
6Data 1Data 1Data 1Data 1
7Data 1Data 1Data 1Data 1
8Data 1Data 1Data 1Data 1
9Data 1Data 1Data 1Data 1
10Data 1Data 1Data 1Data 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😊
 

Attachments

  • In Progress Sheet.JPG
    In Progress Sheet.JPG
    132.6 KB · Views: 6
  • Completed.JPG
    Completed.JPG
    239 KB · Views: 5
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,543
Messages
6,179,429
Members
452,914
Latest member
echoix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top