Copy values from a table to another tab

Closed
10FOOT - Jan 8, 2015 at 07:04 PM
vcoolio
Posts
1352
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 19, 2022
- Jan 10, 2015 at 06:02 AM
Hello,

I have an excel spreadsheet.

How can I copy values from a table to another tab by placing an "x" in the right column.

I only want the information with an "x" in the right column to be copied over to the next tab.

regards, Anton

1 reply

vcoolio
Posts
1352
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 19, 2022
250
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:-

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.
0