• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to extract data from one file to another file

Anantha Krishna

New Member
Hello All,

I need VBA coding for the below requirement

On File 1 we will be having complete data, so we need only particular data to copy for file 2
E.g on File 1
ColumnAColumnBColumnC
810278952​
V090700101
20-Feb-19​
810278952​
V096025001
26-Feb-19​
810278952​
V096025002
20-Feb-19​
810278952​
V092457001
21-Feb-19​
810278952​
V093096001
4-Feb-19​
810278952​
V092382001
6-Feb-19​
810278952​
V133808001
18-Mar-19​
81027222​
V120294001
29-Jan-19​
81027222​
V117963001
29-Jan-19​
81027222​
V162921001
29-Jan-19​
81027222​
V092382001
21-Feb-19​
81027222​
V133808001
18-Feb-19​

On File 2 if we need data only for 810278952 which is on the ColumnA then
ColumnAColumnBColumnC
810278952​
V090700101
20-Feb-19​
810278952​
V096025001
26-Feb-19​
810278952​
V096025002
20-Feb-19​
810278952​
V092457001
21-Feb-19​
810278952​
V093096001
4-Feb-19​
810278952​
V092382001
6-Feb-19​
810278952​
V133808001
18-Mar-19​
If we need data for 81027222 then
ColumnAColumnBColumnC
81027222​
V120294001
29-Jan-19​
81027222​
V117963001
29-Jan-19​
81027222​
V162921001
29-Jan-19​
81027222​
V092382001
21-Feb-19​
81027222​
V133808001
18-Feb-19​

So can anyone help me on coding based on above requirement.

Regards,
Anantha
 

AlanSidman

Active Member
Code:
Option Explicit

Sub foo()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim crit As Long    'Assumes Column A are real numbers and not text
    Dim lr As Long, lr2 As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    crit = InputBox("What item to copy?")
    For i = 2 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
        If s1.Range("A" & i) = crit Then
            s1.Range("A" & i & ":C" & i).Copy
            s2.Range("A" & lr2).PasteSpecial xlPasteValues
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Action Completed"

End Sub
 

Anantha Krishna

New Member
Hi AlanSidman,

While running below code getting error as Type mismatch after giving value on the Msg box.

Code:
Option Explicit

Sub foo()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim crit As Long    'Assumes Column A are real numbers and not text
    Dim lr As Long, lr2 As Long, i As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    crit = InputBox("What item to copy?")
    For i = 2 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
        If s1.Range("A" & i) = crit Then
            s1.Range("A" & i & ":C" & i).Copy
            s2.Range("A" & lr2).PasteSpecial xlPasteValues
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Action Completed"

End Sub
Can you please advise what is going wrong

Regards,
Anantha
 
Last edited by a moderator:

AlanSidman

Active Member
Suggest you upload your file for analysis as we cannot manipulate data from pictures. I think vletm wants to know the value that you placed in the Input box for crit and also what is the last row number in column A. Additionally, there is no msgbox in this code. There is an Inputbox that asks you to supply information.

Are the values in column A real numbers or are they text formatted but look like number? Can't tell without seeing the actual file.
 

vletm

Excel Ninja
Anantha Krishna
As AlanSidman answered for Your questions (#6 Reply).
Your crit is Long.
Dim crit As Long 'Assumes Column A are real numbers and not text
If You give Long-value with Your InputBox, no error ... but same time never copies!
Your Sheet2's A-column ... isn't Long-type, it's text!
You gotta change crit or A-column values, if You would like it to work better.
Also check this: https://docs.microsoft.com/en-us/office/vba/api/excel.application.inputbox
I would do that other way ...
a) Select one row and press the button = copy selected row to needed sheet
b) Filter needed row and press the button = copy selected row to needed sheet
 
Last edited:

AlanSidman

Active Member
The issue is the picture you presented is not the same as the file you uploaded. In your picture you ask that we provide code for column A. In your file the criteria for copying is in column B. Change this line of code
Code:
If s1.Range("A" & i) = crit Then
to
Code:
If s1.Range("B" & i) = crit Then
How did you expect us to give you a solution to a problem if you don't pose your question that accurately portrays your issue? We are not mind readers here. Only Excellers with skills. Please do not do this type of questioning again.
 
Top