Macro to move certain columns to new sheet based on status (ie; "done")

[AnnyCav]

Hello everyone, I have a spreadsheet with TWO sheets.
Sheet ONE (Current Tasks - Sheet will be renamed once sorted)
- This sheet contains all the tasks that I am working on.
Sheet TWO (Future Tasks - sheet will be renamed once sorted)
- This sheet holds all my future/upcoming tasks, there may be 30 of them which are listed by "Date Due"

What I am wanting to do (not so successfully)

I have a spreadsheet which I have already started on including code (you will laugh, but some of it works!), but I do not understand how to attach it, so please let me know how I can do this.
In my spreadsheet is want to:

STEP 1

1. Use a command button [Get Today's Tasks] in Sheet ONE to pull tasks from sheet TWO where the Task Date is equal to TODAY'S DATE. (I want them to be cut and pasted, not copied).
2. When the data comes into Sheet ONE, it will be pasted on the Next available line (as they may already be unfinished tasks in that list), It will only need to be a straight cut/paste only because
   1. I will already have each column formatted as required ie; Date format, Cycle column will be pre-formatted with data validation (in case I want to change the cycle at any time and to keep it correct)
   2. Status column same as cycle column for same reason.

STEP 2

1. Use a command button [Move Tasks]which I have already done some coding on - the bit that will make you laugh, (and as you will see on the images provided - does not quite work - LOL!) to ...
   1. Move any tasks that have "DONE" in the status, back to Sheet TWO, however, this will be based on the following ...
      1. The new Task Date that will go over to sheet TWO will be the date in Column F (Next Due) of sheet 1 (this has been calculated using a formula based on the the cycle)
      2. The new List Date that will go over to sheet TWO will be the date in Column G (Next List) of sheet 1 (also calculated using a formula based on the cycle)
      3. The Task description will be copied and pasted into the Task Description of sheet TWO as normal.
      4. The Cycle information will also be copied/pasted back into The Cycle in Sheet TWO as normal

So, as you can see, the tasks will more often than not re-cyle over and over based on the cycle column.

 

[AnnyCav]

Anyone? Anyone at all?

 

[GK039]

Hi

Name your two Sheets cnGetToday and cnMoveTasks as provided in the image. Then associate sub GetTodaysTask() with Get today's tasks button and sub MoveTasks() with Move tasks button. Code is below:

Option Explicit

Sub GetTodaysTask()

Dim NextRow As Integer
Dim ws As Worksheet
Dim MoveDate As Date

'First empty row in col A
NextRow = cnGetToday.Cells(Rows.Count, 1).End(xlUp).Row + 1


Set ws = ThisWorkbook.Sheets(2)

ws.Activate
Dim i As Integer
Dim LastMoveTaskRow As Integer

'Set var MoveDate to todays date
MoveDate = Date

'Find last row in Sheet2
LastMoveTaskRow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row

'Loop throughthe range to find data to copy
For i = 1 To LastMoveTaskRow
    If ws.Range("a" & i).Value = MoveDate Then
        ws.Range("a" & i).Resize(, 4).Cut Destination:=cnGetToday.Range("A" & NextRow)
        NextRow = NextRow + 1
    End If
Next i

'Clear
Application.CutCopyMode = False

End Sub
Sub MoveTasks()

Dim ws As Worksheet
Dim i As Integer
Dim myRng As Range

Dim LastNewxtTaskRow As Integer

'Find last row with data to loop through
LastNewxtTaskRow = cnGetToday.Cells(Rows.Count, 6).End(xlUp).Row
Set ws = ThisWorkbook.Sheets(2)

'Starting Row Is 4
For i = 4 To LastNewxtTaskRow

    'Find last row to paste values in Sheet 2
    Dim LastMoveTaskRow As Integer
    LastMoveTaskRow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row + 1
   
    'Status=DONE
    If cnGetToday.Range("e" & i).Value = "Done" Then
        ' Cut and paste appropriate data
        cnGetToday.Range("F" & i).Cut Destination:=cnMoveTasks.Range("A" & LastMoveTaskRow)
        cnGetToday.Range("B" & i).Cut Destination:=cnMoveTasks.Range("B" & LastMoveTaskRow)
        cnGetToday.Range("G" & i).Cut Destination:=cnMoveTasks.Range("C" & LastMoveTaskRow)
        cnGetToday.Range("D" & i).Cut Destination:=cnMoveTasks.Range("D" & LastMoveTaskRow)
    End If
Next i

'Clear
Application.CutCopyMode = False

End Sub

Hope this helps
George

 

[AnnyCav]

Hi

Name your two Sheets cnGetToday and cnMoveTasks as provided in the image. Then associate sub GetTodaysTask() with Get today's tasks button and sub MoveTasks() with Move tasks button. Code is below:

Option Explicit

Sub GetTodaysTask()

Dim NextRow As Integer
Dim ws As Worksheet
Dim MoveDate As Date

'First empty row in col A
NextRow = cnGetToday.Cells(Rows.Count, 1).End(xlUp).Row + 1


Set ws = ThisWorkbook.Sheets(2)

ws.Activate
Dim i As Integer
Dim LastMoveTaskRow As Integer

'Set var MoveDate to todays date
MoveDate = Date

'Find last row in Sheet2
LastMoveTaskRow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row

'Loop throughthe range to find data to copy
For i = 1 To LastMoveTaskRow
    If ws.Range("a" & i).Value = MoveDate Then
        ws.Range("a" & i).Resize(, 4).Cut Destination:=cnGetToday.Range("A" & NextRow)
        NextRow = NextRow + 1
    End If
Next i

'Clear
Application.CutCopyMode = False

End Sub
Sub MoveTasks()

Dim ws As Worksheet
Dim i As Integer
Dim myRng As Range

Dim LastNewxtTaskRow As Integer

'Find last row with data to loop through
LastNewxtTaskRow = cnGetToday.Cells(Rows.Count, 6).End(xlUp).Row
Set ws = ThisWorkbook.Sheets(2)

'Starting Row Is 4
For i = 4 To LastNewxtTaskRow

    'Find last row to paste values in Sheet 2
    Dim LastMoveTaskRow As Integer
    LastMoveTaskRow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row + 1
  
    'Status=DONE
    If cnGetToday.Range("e" & i).Value = "Done" Then
        ' Cut and paste appropriate data
        cnGetToday.Range("F" & i).Cut Destination:=cnMoveTasks.Range("A" & LastMoveTaskRow)
        cnGetToday.Range("B" & i).Cut Destination:=cnMoveTasks.Range("B" & LastMoveTaskRow)
        cnGetToday.Range("G" & i).Cut Destination:=cnMoveTasks.Range("C" & LastMoveTaskRow)
        cnGetToday.Range("D" & i).Cut Destination:=cnMoveTasks.Range("D" & LastMoveTaskRow)
    End If
Next i

'Clear
Application.CutCopyMode = False

End Sub

Hope this helpsUpload Image
George

Thank you for this George!, it looks simple and sleek and I find it quite easy to follow and understand. After some time I realised that I had to change the sheet name in sheet properties (and not in the tab). I have done that. I also pasted the code behind the button/s and name the button. The code is working, however there are some issues

1. On click of [Get Todays Tasks] it moves the correct data over, but it takes me into Sheet 2 (the all tasks list) and stays there, can I have it finish back into the main (Current Tasks, Sheet 1) screen?

2. When the tasks move OUT of the All Tasks sheet (based on Done) it leaves a dead row (see image), It does the same when I move INTO todays Can we delete those rows once moved over? It does the same when taking tasks out of the all tasks sheet (there will be many dead lines before I find tasks after a few days!

Question, I was hoping to convert these lists into "Tables" as I have helper columns and formula columns that I would like to "automatically calculate once the task has come into "TODAY" (they calculate the cycle follow-up dates). Whilst the Tasks move in ok, my calculations have to be reset (See image). I did try to covert it to a table, however, when I moved the task in, it took the table as a non available space and placed the data in the first row UNDER the table. How best is this issue to be addressed?

I understand that I could potentially have the cycle calculations done as part of the code, but my brain would not allow for it (LOL). I am happy for it to be hidden columns in the spreadsheet and calculate away nicely as my tasks come in. At the moment I have to reset them each time.

I wholeheartedly appreciate your help here. Your code was much shorter and better than mine which was fraught with errors. I am great with formulas but coding just flies over my head. Thank you again.
Anny

 

[GK039]

Thank you for this George!, it looks simple and sleek and I find it quite easy to follow and understand. After some time I realised that I had to change the sheet name in sheet properties (and not in the tab). I have done that. I also pasted the code behind the button/s and name the button. The code is working, however there are some issues

1. On click of [Get Todays Tasks] it moves the correct data over, but it takes me into Sheet 2 (the all tasks list) and stays there, can I have it finish back into the main (Current Tasks, Sheet 1) screen?

2. When the tasks move OUT of the All Tasks sheet (based on Done) it leaves a dead row (see image), It does the same when I move INTO todays Can we delete those rows once moved over? It does the same when taking tasks out of the all tasks sheet (there will be many dead lines before I find tasks after a few days!

Question, I was hoping to convert these lists into "Tables" as I have helper columns and formula columns that I would like to "automatically calculate once the task has come into "TODAY" (they calculate the cycle follow-up dates). Whilst the Tasks move in ok, my calculations have to be reset (See image). I did try to covert it to a table, however, when I moved the task in, it took the table as a non available space and placed the data in the first row UNDER the table. How best is this issue to be addressed?

I understand that I could potentially have the cycle calculations done as part of the code, but my brain would not allow for it (LOL). I am happy for it to be hidden columns in the spreadsheet and calculate away nicely as my tasks come in. At the moment I have to reset them each time.

I wholeheartedly appreciate your help here. Your code was much shorter and better than mine which was fraught with errors. I am great with formulas but coding just flies over my head. Thank you again.
Anny

Hi Anny,

I am glad that I helped you!

For question 1: Place this line of code

cnGetToday.Activate

just before End Sub of GetTodaysTask sub.
Just to mention here that naming the sheet in the Sheet Properties rather than giving a name in the tab makes your code easier to construct, your application easier to maintain and bulletproof you from accidentally changing the worksheet name in the tab. The code will still work if you use the code name. But if you hardcode the name of the sheet within your code and in the future you change the Sheet name from the Tab your code will return an error.

For question2: I created another routine (RemoveEmptyRows) to delete rows you do not want. You have to call it and an input box prompts you to select which action to proceed with. Delete rows from Sheet1 or Sheet2

The entire code follows:

Option Explicit

Sub GetTodaysTask()

Dim NextRow As Integer
Dim ws As Worksheet
Dim MoveDate As Date

'First empty row in col A
NextRow = cnGetToday.Cells(Rows.Count, 1).End(xlUp).Row + 1

Set ws = ThisWorkbook.Sheets(2)

ws.Activate
Dim i As Integer
Dim LastMoveTaskRow As Integer

'Set var MoveDate to todays date
MoveDate = Date

'Find last row in Sheet2
LastMoveTaskRow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row

'Loop throughthe range to find data to copy
For i = 1 To LastMoveTaskRow
    If ws.Range("a" & i).Value = MoveDate Then
        ws.Range("a" & i).Resize(, 4).Cut Destination:=cnGetToday.Range("A" & NextRow)
        NextRow = NextRow + 1
    End If
Next i

'Clear
Application.CutCopyMode = False
cnGetToday.Activate

Call RemoveEmptyRows
End Sub
Sub MoveTasks()
'Remove rows from Sheet1 where data have been moved over to Sheet2
Dim ws As Worksheet
Dim i As Integer
'Dim myRng As Range

Dim LastNewxtTaskRow As Integer

'Find last row with data to loop through
LastNewxtTaskRow = cnGetToday.Cells(Rows.Count, 6).End(xlUp).Row
Set ws = ThisWorkbook.Sheets(2)

'Starting Row Is 4
For i = 4 To LastNewxtTaskRow

    'Find last row to paste values in Sheet 2
    Dim LastMoveTaskRow As Integer
    LastMoveTaskRow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    'Status=DONE
    If cnGetToday.Range("e" & i).Value = "Done" Then
        ' Cut and paste appropriate data
        cnGetToday.Range("F" & i).Cut Destination:=cnMoveTasks.Range("A" & LastMoveTaskRow)
        cnGetToday.Range("B" & i).Cut Destination:=cnMoveTasks.Range("B" & LastMoveTaskRow)
        cnGetToday.Range("G" & i).Cut Destination:=cnMoveTasks.Range("C" & LastMoveTaskRow)
        cnGetToday.Range("D" & i).Cut Destination:=cnMoveTasks.Range("D" & LastMoveTaskRow)
    End If
Next i

'Clear
Application.CutCopyMode = False

Call RemoveEmptyRows
End Sub

NEW SUB HERE

Option Explicit

Sub RemoveEmptyRows()

'Declare Constant for Status column
Const myCol As Integer = 5

Dim i As Integer, Lrow As Integer
Dim myUserSelect As String

'Select which rows to delete
myUserSelect = InputBox("Delete empty rows from Sheet1 or Sheet2? Enter 1 for Sheet1 or 2 for Sheet2", "Delete Rows")

If myUserSelect = 1 Then
    'Delete forws from Sheet1
    'Last row in column A
    Lrow = cnGetToday.Cells(Rows.Count, 1).End(xlUp).Row
    
    'Loop through rows
    For i = Lrow To 4 Step -1
        'Find sratus "Done" in Status column
        If cnGetToday.Cells(i, myCol).Value = "Done" Then
            'Delete Rows
            cnGetToday.Cells(i, myCol).EntireRow.Delete
        End If
    Next i
Else
    'Delete rows from Sheet2
    'Last row in column A
    Lrow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row
    
    'Loop through rows
    For i = Lrow To 4 Step -1
        'Entire row is empty
        If Application.WorksheetFunction.CountA(cnMoveTasks.Rows(i)) = 0 Then
            'Delete Rows
            cnMoveTasks.Rows(i).Delete
        End If
    Next i
End If

End Sub

For your last question, I cannot tell since I don't have your entire workbook or formulas that you use. To create the calculations inside the code sounds good but still I cannot be sure. Another way is to have your code replace the correct formulas in the lines that return an error.

George

 

[AnnyCav]

Hi Anny,

I am glad that I helped you!

For question 1: Place this line of code

cnGetToday.Activate

just before End Sub of GetTodaysTask sub.
Just to mention here that naming the sheet in the Sheet Properties rather than giving a name in the tab makes your code easier to construct, your application easier to maintain and bulletproof you from accidentally changing the worksheet name in the tab. The code will still work if you use the code name. But if you hardcode the name of the sheet within your code and in the future you change the Sheet name from the Tab your code will return an error.

For question2: I created another routine (RemoveEmptyRows) to delete rows you do not want. You have to call it and an input box prompts you to select which action to proceed with. Delete rows from Sheet1 or Sheet2

The entire code follows:

Option Explicit

Sub GetTodaysTask()

Dim NextRow As Integer
Dim ws As Worksheet
Dim MoveDate As Date

'First empty row in col A
NextRow = cnGetToday.Cells(Rows.Count, 1).End(xlUp).Row + 1

Set ws = ThisWorkbook.Sheets(2)

ws.Activate
Dim i As Integer
Dim LastMoveTaskRow As Integer

'Set var MoveDate to todays date
MoveDate = Date

'Find last row in Sheet2
LastMoveTaskRow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row

'Loop throughthe range to find data to copy
For i = 1 To LastMoveTaskRow
    If ws.Range("a" & i).Value = MoveDate Then
        ws.Range("a" & i).Resize(, 4).Cut Destination:=cnGetToday.Range("A" & NextRow)
        NextRow = NextRow + 1
    End If
Next i

'Clear
Application.CutCopyMode = False
cnGetToday.Activate

Call RemoveEmptyRows
End Sub
Sub MoveTasks()
'Remove rows from Sheet1 where data have been moved over to Sheet2
Dim ws As Worksheet
Dim i As Integer
'Dim myRng As Range

Dim LastNewxtTaskRow As Integer

'Find last row with data to loop through
LastNewxtTaskRow = cnGetToday.Cells(Rows.Count, 6).End(xlUp).Row
Set ws = ThisWorkbook.Sheets(2)

'Starting Row Is 4
For i = 4 To LastNewxtTaskRow

    'Find last row to paste values in Sheet 2
    Dim LastMoveTaskRow As Integer
    LastMoveTaskRow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row + 1
   
    'Status=DONE
    If cnGetToday.Range("e" & i).Value = "Done" Then
        ' Cut and paste appropriate data
        cnGetToday.Range("F" & i).Cut Destination:=cnMoveTasks.Range("A" & LastMoveTaskRow)
        cnGetToday.Range("B" & i).Cut Destination:=cnMoveTasks.Range("B" & LastMoveTaskRow)
        cnGetToday.Range("G" & i).Cut Destination:=cnMoveTasks.Range("C" & LastMoveTaskRow)
        cnGetToday.Range("D" & i).Cut Destination:=cnMoveTasks.Range("D" & LastMoveTaskRow)
    End If
Next i

'Clear
Application.CutCopyMode = False

Call RemoveEmptyRows
End Sub

NEW SUB HERE

Option Explicit

Sub RemoveEmptyRows()

'Declare Constant for Status column
Const myCol As Integer = 5

Dim i As Integer, Lrow As Integer
Dim myUserSelect As String

'Select which rows to delete
myUserSelect = InputBox("Delete empty rows from Sheet1 or Sheet2? Enter 1 for Sheet1 or 2 for Sheet2", "Delete Rows")

If myUserSelect = 1 Then
    'Delete forws from Sheet1
    'Last row in column A
    Lrow = cnGetToday.Cells(Rows.Count, 1).End(xlUp).Row
   
    'Loop through rows
    For i = Lrow To 4 Step -1
        'Find sratus "Done" in Status column
        If cnGetToday.Cells(i, myCol).Value = "Done" Then
            'Delete Rows
            cnGetToday.Cells(i, myCol).EntireRow.Delete
        End If
    Next i
Else
    'Delete rows from Sheet2
    'Last row in column A
    Lrow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row
   
    'Loop through rows
    For i = Lrow To 4 Step -1
        'Entire row is empty
        If Application.WorksheetFunction.CountA(cnMoveTasks.Rows(i)) = 0 Then
            'Delete Rows
            cnMoveTasks.Rows(i).Delete
        End If
    Next i
End If

End Sub

For your last question, I cannot tell since I don't have your entire workbook or formulas that you use. To create the calculations inside the code sounds good but still I cannot be sure. Another way is to have your code replace the correct formulas in the lines that return an error.

George

Thank you - Yes, I understood what you were doing with hard coding the sheet names as I most likely will change the names of the sheets down the track. That is a such a great learning curve for me! I love that concept of Genesis naming (what I call it), like ... "In the beginning ... it is called..." You can then name it whatever but it doesn't matter because the true source is already there! (Don't know if I made sense much there, but I love that).

So, pasting this new code unfortunately, gave me a Compile error. the removing of empty rows see attached image.

I am so grateful for this as it will be a game changer in my job because keeping track of reminders to patients to a) to remember to get their investigations done, then remind them of the TYPE of appointment they have coming up, then reminding them again the day prior .... MANY different reminders for MANY different appointment types and surgeries .... It's so easy to forget and then the next thing you know .... "Oh shoot! I have call all these people individually cause I didn't remind them!".

 

[GK039]

Thank you - Yes, I understood what you were doing with hard coding the sheet names as I most likely will change the names of the sheets down the track. That is a such a great learning curve for me! I love that concept of Genesis naming (what I call it), like ... "In the beginning ... it is called..." You can then name it whatever but it doesn't matter because the true source is already there! (Don't know if I made sense much there, but I love that).

So, pasting this new code unfortunately, gave me a Compile error. the removing of empty rows see attached image.

I am so grateful for this as it will be a game changer in my job because keeping track of reminders to patients to a) to remember to get their investigations done, then remind them of the TYPE of appointment they have coming up, then reminding them again the day prior .... MANY different reminders for MANY different appointment types and surgeries .... It's so easy to forget and then the next thing you know .... "Oh shoot! I have call all these people individually cause I didn't remind them!".

Hello Anny,

Insert a new module and paste the code I provided. Did you do that?

Option Explicit

Sub RemoveEmptyRows()

'Declare Constant for Status column
Const myCol As Integer = 5

Dim i As Integer, Lrow As Integer
Dim myUserSelect As String

'Select which rows to delete
myUserSelect = InputBox("Delete empty rows from Sheet1 or Sheet2? Enter 1 for Sheet1 or 2 for Sheet2", "Delete Rows")

If myUserSelect = 1 Then
    'Delete forws from Sheet1
    'Last row in column A
    Lrow = cnGetToday.Cells(Rows.Count, 1).End(xlUp).Row
    
    'Loop through rows
    For i = Lrow To 4 Step -1
        'Find sratus "Done" in Status column
        If cnGetToday.Cells(i, myCol).Value = "Done" Then
            'Delete Rows
            cnGetToday.Cells(i, myCol).EntireRow.Delete
        End If
    Next i
Else
    'Delete rows from Sheet2
    'Last row in column A
    Lrow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row
    
    'Loop through rows
    For i = Lrow To 4 Step -1
        'Entire row is empty
        If Application.WorksheetFunction.CountA(cnMoveTasks.Rows(i)) = 0 Then
            'Delete Rows
            cnMoveTasks.Rows(i).Delete
        End If
    Next i
End If

End Sub

Working in a hospital?
I had the experience for almost 3 years before i moved on to my current employer
George

 

[AnnyCav]

Hello Anny,

Insert a new module and paste the code I provided. Did you do that?

Option Explicit

Sub RemoveEmptyRows()

'Declare Constant for Status column
Const myCol As Integer = 5

Dim i As Integer, Lrow As Integer
Dim myUserSelect As String

'Select which rows to delete
myUserSelect = InputBox("Delete empty rows from Sheet1 or Sheet2? Enter 1 for Sheet1 or 2 for Sheet2", "Delete Rows")

If myUserSelect = 1 Then
    'Delete forws from Sheet1
    'Last row in column A
    Lrow = cnGetToday.Cells(Rows.Count, 1).End(xlUp).Row
   
    'Loop through rows
    For i = Lrow To 4 Step -1
        'Find sratus "Done" in Status column
        If cnGetToday.Cells(i, myCol).Value = "Done" Then
            'Delete Rows
            cnGetToday.Cells(i, myCol).EntireRow.Delete
        End If
    Next i
Else
    'Delete rows from Sheet2
    'Last row in column A
    Lrow = cnMoveTasks.Cells(Rows.Count, 1).End(xlUp).Row
   
    'Loop through rows
    For i = Lrow To 4 Step -1
        'Entire row is empty
        If Application.WorksheetFunction.CountA(cnMoveTasks.Rows(i)) = 0 Then
            'Delete Rows
            cnMoveTasks.Rows(i).Delete
        End If
    Next i
End If

End Sub

Working in a hospital?
I had the experience for almost 3 years before i moved on to my current employer
George

I actually work for a Surgeon and run his practice. Just him and me but very busy and He does both Consults and Surgery. Weeks come around quickly and I need to keep on top of all the SMS reminders including my one off, daily and fortnightly tasks. Its something that I work off constantly. I am a bit obsessive about it but there is a lot of manual handling.

I realised that I did not post the code into a module. So I have done this and it now works perfectly love. Thank you. I do have one further question .. which I can likely add myself if you can assist me with the code for a "Paste Special, as text". two of the cells that I copy over are formulas, and they fo back into Sheet 2 as #REF!.

This is the code : cnGetToday.Range("F" & i).Cut Destination:=cnMoveTasks.Range("A" & LastMoveTaskRow)
I need to change this to cut and paste special as text. How would I covert this line to do that? I think it will work perfectly then!!

 

[GK039]

I actually work for a Surgeon and run his practice. Just him and me but very busy and He does both Consults and Surgery. Weeks come around quickly and I need to keep on top of all the SMS reminders including my one off, daily and fortnightly tasks. Its something that I work off constantly. I am a bit obsessive about it but there is a lot of manual handling.

I realised that I did not post the code into a module. So I have done this and it now works perfectly love. Thank you. I do have one further question .. which I can likely add myself if you can assist me with the code for a "Paste Special, as text". two of the cells that I copy over are formulas, and they fo back into Sheet 2 as #REF!.

This is the code : cnGetToday.Range("F" & i).Cut Destination:=cnMoveTasks.Range("A" & LastMoveTaskRow)
I need to change this to cut and paste special as text. How would I covert this line to do that? I think it will work perfectly then!!

I am glad that I helped you Anny
Well, before I give you the answer try to do it yourself the way you described it by recording a macro. Developer tab - Record a macro copy the range and then paste it as values. Look at the code and voila

 

[AnnyCav]

Ok, So I have just spent the last 2hrs of my boss' time but have to stop and catch up on work now. The problem I see is .... We are cutting and pasting, but from what I see is you cannot CUT then paste special? You can COPY and Paste special, but not the other way around(?)

The marcro for copy, paste special, values and format = Paste:=xlPasteValuesAndNumberFormats. I have tried to put this code at different places in this code " cnGetToday.Range("F" & i).Cut Destination:=cnMoveTasks.Range("A" & LastMoveTaskRow)" to no avail. I feel so close but so far. I am trying to find the "Logical" spot to put this code but nothing works.

Even in the code row I can see .cut, but where does is say paste in Range A ... The word "Paste" does not appear anywhere and I think that is what is throwing me?

Annyx