Copy values from a table to another tab
Closed
10FOOT
-
Jan 8, 2015 at 07:04 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 10, 2015 at 06:02 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 10, 2015 at 06:02 AM
Related:
- Copy values from a table to another tab
- School time table software free download full version - Download - Organisation and teamwork
- How to delete a row in a table in word - Guide
- Java update tab missing - Guide
- Html table fit to screen - Guide
- Opera incognito tab - Guide
1 response
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jan 10, 2015 at 06:02 AM
Jan 10, 2015 at 06:02 AM
Hello Anton,
To keep things simple, if your table has two columns (say Columns A and B), you will need to place the criterion "x" in another column, say Column C (or insert a new column in front of column A). This will then act as a unique identifier. If it is a table that you have, it will expand to include the new column. Then the following code should do as you want:-
The code assumes that your criterion "x" is in column C. To see how the code works, you can peruse my test work book here:-
https://www.dropbox.com/s/bd8xgvtzg06igco/10FOOT.xlsm?dl=0
When you run the code (by clicking on the "Transfer Data" button), an input box will appear asking you to input your criterion "x". When you have done this, click OK and the relevant data should be transferred to Sheet2. Input the criterion exactly as you have it in your table (lower case or upper case etc.). For your actual work book, you will need to change Sheet1 and Sheet2 in the code to match your tab names exactly.
I hope this helps you.
Kind regards,
vcoolio.
To keep things simple, if your table has two columns (say Columns A and B), you will need to place the criterion "x" in another column, say Column C (or insert a new column in front of column A). This will then act as a unique identifier. If it is a table that you have, it will expand to include the new column. Then the following code should do as you want:-
Sub TransferIt()
Dim lRow As Long
Dim Criteria As String
Sheets("Sheet1").Select
lRow = Range("A" & Rows.Count).End(xlUp).Row
Criteria = InputBox("Please enter search criteria.")
For Each Cell In Range("C2:C" & lRow)
If Cell = Criteria Then
Cell.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next Cell
Sheets("Sheet2").Range("A1:C" & Rows.Count).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
MsgBox "Data transferred!", vbExclamation
Sheets("Sheet2").Select
End Sub
The code assumes that your criterion "x" is in column C. To see how the code works, you can peruse my test work book here:-
https://www.dropbox.com/s/bd8xgvtzg06igco/10FOOT.xlsm?dl=0
When you run the code (by clicking on the "Transfer Data" button), an input box will appear asking you to input your criterion "x". When you have done this, click OK and the relevant data should be transferred to Sheet2. Input the criterion exactly as you have it in your table (lower case or upper case etc.). For your actual work book, you will need to change Sheet1 and Sheet2 in the code to match your tab names exactly.
I hope this helps you.
Kind regards,
vcoolio.
