transfer a row of data from one sheet to another same workbookSolved/Closed

Question

Hello, 
I am trying to transfer a row of data from one sheet to another in the same workbook but my code is not working could anyone help me 
thanks Jess 

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim rngDest As Range 
Set rngDest = Worksheets("Worksheet 1").Range("rngDest") 

' Limit the trap area to range of cells in which completed dates are entered as defined above 
If Not Intersect(Target, Range("rngTrigger")) Is Nothing Then 

' Only trigger if the value entred is a date or is recognizable as a valid date 
If IsDate(Target) Then 
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop! 
Application.EnableEvents = False 
Target.EntireRow.Select 
Selection.Cut 
rngDest.Insert Shift:=xlDown 
Selection.Delete 
' Reset EnableEvents 
Application.EnableEvents = True 
End If 
End If 

End Sub

vcoolio · Answer

Hello Jess,

See if you can adapt the code in the following thread to suit your needs:-


https://ccm.net/forum/affich-897790-auto-update-data-between-sheets

You'll obviously only need to use it in one sheet (your Input sheet).

Let us know how it works out for you.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Jess,

Its ok. Don't panic!

Upload a sample of your work book (be careful with any sensitive data) to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here.

We'll then have a rummage around it and sort it out for you.

Cheerio,
vcoolio.

Jess123! · Answer

Hello vcoolio

Here is the Link
http://speedy.sh/qTzrJ/order-location1.xls

And thank again for all the help I am receiving it is greatly appreciated 
Thanks
Jess123!

vcoolio · Answer

Hello Jess,

Here we go:-


Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Dim lCol As Long
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

If Target.Count > 1 Then Exit Sub
If Not Application.Intersect(Target, Range("H:H")) Is Nothing Then

If IsDate(Target) Then
        Range(Cells(Target.Row, "A"), Cells(Target.Row, lCol)).Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
        Target.EntireRow.Delete
        End If
  End If
  
Sheet2.Columns.AutoFit
Sheet2.Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I've made the following assumptions as to what you are attempting to do:-

- Sheet1 is your "input" sheet where you enter your daily data.
- When an order is completed you then enter the completion date into the relevant cell in Column H.
- When the date is entered in Column H, you would like the entire relevant row of data immediately transferred to sheet2 as a type of archive.
- You would like the same row of data to be then cleared from sheet1.

The above code should then do the task for you. It is a Worksheet Change event which means that the code will execute as soon as something happens in Column H. In this case, only dates will work in Column H. Any other values that may be inadvertantly placed in Column H will just show a blank result in sheet2.

Just to explain the code a little for you, mainly lines 11 - 14:-

 If the target value is a date (the target being all cells in Column H), it will copy the row of the target cell from Column A to the last used column (lCol) which in this case is Column H and transfer it to sheet2 into the next available row. The target row is then deleted from sheet1.

Using lCol instead of a fixed column reference will give you some flexibility should your data ever need to expand further out to the right. Column H can remain as your "trigger" column. You won't need to adjust the code (unless you change the "trigger" column, Column H). 

Following is the link to my test work book, based on the sample that you supplied, for you to peruse:-

https://www.dropbox.com/s/6huvrw8lbjpfl6c
/Jess%28Worksheet_Change%20event%29.xlsm?dl=0

Enter a date in any cell in Column H and click away (or press enter or down arrow) and the relevant row of data will be transferred to sheet2. The code will take you directly to sheet2 on each transfer. Should this become annoying (and it just might!) just remove line 19 from the code.

In the test work book, I have removed the sheet which you had named "Codes Used".

To implement the code into your work book:-

- Righ click on the sheet1 tab.
- Select "view code" from the menu that appears.
- In the big white field that then appears, paste the above code.
- Go back to your Input sheet (sheet1) and test it out.

It would be a good idea to test the code in a copy of your work book first.

If you have any more questions, just come back anytime.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Jess,

You're welcome. I'm glad that I was able to help you.

Cheerio,
vcoolio.

Jess123! · Answer

Hi Vcoolio
 Sorry to bother you again but can ew put a 20 second delay in or be able to stay on sheet 1 until i enter everything on the same line every time I make an entry and hit tab to go from column a to column b the code takes me to sheet 2 I go back to sheet 1 enter in column c and I go to sheet two any way for it not to change until I put in Complete Date  I really can't use it the way it is now after I started enter data

Thanks for all your help
Jess123!

vcoolio · Answer

Hello Jess,

The simplest way is to remove line 19 from the code, but a more correct method would be to:-

1) Replace line 9 with the following:-

If Intersect(Target, Columns("H:H")) Is Nothing Then Exit Sub

2) Add the following line just above it:-

If Target.Value = vbNullString then Exit Sub

3) Remove one of the End IF statements.

Another method would be to place basically the same code in a standard module and assign the  code to a button. Absolutely nothing will then happen until you click on the button. Using a button will allow you to enter as much data as you like without interruption. Data will only transfer when you click on the button.

Let me know what you decide.

Cheerio,
vcoolio.

vcoolio · Answer

Hey Jess,

Here is the link to the updated test file showing how it all works using steps 1, 2 & 3 from above.

https://www.dropbox.com/s/6huvrw8lbjpfl6c/Jess%28Worksheet_Change%20event%29.xlsm?dl=0

Cheerio,
vcoolio

Jess123! · Answer

Hi vcoolio

I think the button would be the better idea if it is not to much trouble
Thanks,
Jess123!

vcoolio · Answer

Hi Jess,

Here we go:-


Sub TransferData()

Application.ScreenUpdating = False

Dim lr As Long
        Dim lCol As Long
        Dim cell As Range
lr = Range("H" & Rows.Count).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

For Each cell In Range("H2:H" & lr)
        If IsDate(cell.Value) Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
        'Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Delete
   End If
Next

Sheet2.Columns.AutoFit
Sheet2.Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


As you can see, the above code is slightly different than the other one. It is a "normal" sub routine and needs to be placed in a standard module. To implement the code into your work book:-

- Press Alt + F11 which will take you to the Visual Basic Editor.
- Up the top, select Insert and then select Module from the menu that appears.
- In the big white field that then appears, paste the above code.

Go back to your main sheet (sheet1), create a button somewhere on the sheet. Right click on the button and select "assign macro" from the menu that appears. In the Assign Macro window that appears, select the macro name in the larger field (in this case its named "TransferData") then click OK. Done!

Following is the link to the updated test work book for you to play with:-

https://www.dropbox.com/s/bw8f0ccbt18cma8/Jess%282%29.xlsm?dl=0

In case you were wondering, I usually create my own button as follows:-

- In your main sheet, select the Insert tab up the top and, in the Illustrations Group, select Shapes.
- A window will appear showing many different shapes. I usually select a rectangular one.
- When you have selected a shape, your cursor will turn into a "cross-hair" type cursor. Pick a spot on your sheet and draw the shape with the cursor.
- When you have drawn the shape, you'll notice that a Format tab will light up which will give you many options on how you would like your button to look. Click on the down arrow in the Shape Styles Group and have fun!

Have a play with this function in a blank sheet first just to get used to what you can do or to see how creative you can be. The above steps may seem somewhat drawn out but it actually only takes a few seconds to create a button once you have the routine under control.

When you are satisfied, assign the macro to the button as mentioned further up this post.

You could also use the inbuilt function to create a button by going to the Developer tab, selecting Insert in the Controls group and then selecting the Form Control button to which you can assign the macro to.

As mentioned in my post#9, you'll be able to input data undisturbed until you are ready to transfer data to sheet2 by clicking on the button.

One more thing. If you look at line #15 in the above code (in green font), you'll notice an apostrophe at the begining of the line. If you wish to delete  data from sheet1 once it has been transferred to sheet2, then just remove the apostrophe.

I hope that this sorts it all out for you. Let us know how it all works out for you.

Cheerio,
vcoolio.

Jess123! · Answer

Hey Vcoolio
 when I try the sample it moves to the next page but stays on the first too

Jess123!

vcoolio · Answer

Hello Jess,

Do you mean that the data is being transferred to sheet 2 but your view stays on sheet1? If so, check that you haven't removed line 20 from the code.

The sample at the DropBox link works just fine with the view shifting to sheet2 when the data is transferred. The same with my personal sample file.

Check again and let us know.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Jess,

Any issues that you are having are directly related to the fact that you are using a Mac and not a PC. If you have access to a PC, then try the code on that. You'll see that it works just fine. The code at the DropBox link and in my personal file works just as it should.

I have absolutely no experience with Macs but am aware of the continuing compatibilty issues that Excel on a Mac experiences.

With every change of version of Excel( 2000 to 2003, 2010 to 2013 and now to 2016) there are always changes made to the vba coding methods. On Macs, this is an even a bigger issue as they have added and reduced the functionalities over and over again. Much of the code that works on Office for Windows does not work on Macs as the functionality of the Mac OS and the Windows OS are completely different. Basically, the compatibility issues provide a large resource for errors. Example: A path separator in Windows is "\"  and ":" for Macs. So, you can see that the simplest of differences between the two systems will give you untold heart-ache. 

I cannot guide you any further as my experience with Macs is virtually non-existent and I would just be leading you "up the garden path". You may need to have your IT Department (should you work with one) work through this with you (or change to a PC!!??!!).

Also, just referring back to your post#6, its odd that it worked OK then but now it has all fallen in a heap! Could you not go back to that code with the modifications I suggested in post#9?

My apologies Jess.

Cheerio,
vcoolio.

Jess123! · Answer

Thanks for all your help
 Jess123!

vcoolio · Answer

Hi Jess,

Sorry I couldn't sort the last bit out for you. If you eventually do, please post back and let us know the result. Any insight into these type of compatibility issues will be a bonus for anyone who drops in here over time.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Jess,

Are you using the code from post #12 above (the one which is assigned to the button)?

Do you mean that the data is being transferred to sheet 2 but the view stays on sheet 1?

or

Do you mean that the data is being  transferred to sheet2 but not being deleted from sheet 1 once transferred?

Cheerio,
vcoolio.

vcoolio · Answer

Hello Jess,

If you go back to the code in post#12, you'll see that line 15 is in green font. At the beginning of the line, you'll see an apostrophe. Remove the apostrophe and all will work as it should. If you have already pasted the code into your work book, make sure that the apostrophe has been removed.

Let us know if this has sorted it for you.

Cheerio.
vcoolio.

vcoolio · Answer

Hi Jess,

That's excellent! Nothing like a temperamental Mac!

No, you haven't been a pain. As volunteers here, we do what we can to help.

So, have a good day (or night) and all the best.

Cheerio,
vcoolio.

Jess123! · Answer

Hi vcoolio

How hard would it be add columns to the spreadsheet what code do I have to change

Thanks,
Jess123!

vcoolio · Answer

Hi Jess,

If you mean that you just want to use or fill in more columns to the right of your current last column, then you don't need to do anything. If you intend to change the current criteria column (Column H), then you'll need to change the column reference in line 11 of the code (post#12).

From my post#5 above:-

Using lCol instead of a fixed column reference will give you some flexibility should your data ever need to expand further out to the right. Column H can remain as your "trigger" column. You won't need to adjust the code (unless you change the "trigger" column, Column H). 

I hope that this answers your query.

Cheerio,
vcoolio.

Transfer a row of data from one sheet to another same workbook

20 replies

Subscribe To Our Newsletter!