copy and paste by VBA based on criteria

all files are located same place

vba is placed in a seperate file sample1.xlsm

copy all the data from column A TO COLUMN H complete data from sample2.xls and paste it to sample3.xlsb in same columns

save and close the sample2.xls and sample3.xlsb

i need to do the same by vba so plz help me in slving this problem sir

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hi,


To copy all columns is not usually good to do. In whole columns you maybe do not heed all rows

But this is what you asked for

vba is placed in a separate file sample1.xlsm
File is attached : FileAttatchedToPost.jpg : https://imgur.com/xFLyqLQ

copy all the data from column A TO COLUMN H complete data from sample2.xls
Columns("A:H").Copy


paste it to sample3.xlsb in same columns
I recommend using the Range PasteSpecial Method
http://eileenslounge.com/viewtopic.p...=34112#p264458
http://www.eileenslounge.com/viewtop...=25002#p193871
https://docs.microsoft.com/en-us/off...e.pastespecial

You can chose the way you want to Paste, and formats
https://docs.microsoft.com/en-us/off...el.xlpastetype
.PasteSpecial Paste:= xlPasteValuesAndNumberFormats
.PasteSpecial Paste:= xlPasteFormats
.PasteSpecial Paste:= xlPasteColumnWidths



save and close the sample2.xls and sample3.xlsb
You can
Save
Or
Save As

I did do macro recording to check syntax:
http://www.excelfox.com/forum/showth...ll=1#post12531

You can just Save That is enough in your case.
( You can also SaveAs )

Code:

---

Sub CopyAllColumns()
' copy all the data from column A TO COLUMN H complete data from sample2.xls
 Workbooks("sample2.xls").Worksheets.Item(1).Columns("A:H").Copy '
' paste it to sample3.xlsb in same columns
 Workbooks("sample3.xlsb").Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                                                                                            'Workbooks("sample3.xlsb").Worksheets.Item(1).Range("A1:H65536").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 Workbooks("sample3.xlsb").Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteFormats
 Workbooks("sample3.xlsb").Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteColumnWidths
 
' save and close the sample2.xls and sample3.xlsb  '  http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12531&viewfull=1#post12531
 Workbooks("sample2.xls").Save
' or
 Workbooks("sample2.xls").SaveAs Filename:=ThisWorkbook.Path & "\sample2.xls", FileFormat:=xlExcel8

 Workbooks("sample3.xlsb").Save
' or
 Workbooks("sample3.xlsb").SaveAs Filename:=ThisWorkbook.Path & "\sample3.xlsb", FileFormat:=xlExcel12

 Workbooks("sample2.xls").Close: Workbooks("sample3.xlsb").Close
End Sub


---

Alan

Code:

---

Sub STEP4()
 Dim w1 As Workbook, w2 As Workbook
 Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") ' change the file path
 Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
w1.Worksheets.Item(1).Columns("A:H").Copy
w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
w2.Save
w2.Close
w1.Close
End Sub

---

I made this code correct doc sir if there is an error sir

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724
https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Your macro is good
Your macro is working
There are no errors in it.
I did test on files enclosed , with this following macro. ( Macro is in sample1.xlsm )

Code:

---

Sub STEP4()
 Dim w1 As Workbook, w2 As Workbook
Set w1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") ' change the file path
Set w2 = Workbooks.Open(ThisWorkbook.Path & "\FundsCheck.xlsb") '  Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
w1.Worksheets.Item(1).Columns("A:H").Copy
w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
w2.Save
w2.Close
w1.Close
End Sub


---

Alan

---

1.xls : https://app.box.com/s/th2xzmkh7rnfr4qf4dho1kpgudndm073

Thnx Doc Sir for ur great help and for ur great guidance

Hi,

Some extra info:
For Copy and Paste values, is sometimes like this: , Rng1.Value = Rng2.Value better…

To explain:-
We already saw Sub Vixer9b() ' demo for rng.Value = rng.Value
http://www.excelfox.com/forum/showth...ll=1#post11479
http://www.excelfox.com/forum/showth...ll=1#post11485

That was Rng.Value = Rng.Value – We did use .Value of Rng in two ways

We can also do rng2.Value = rng1.Value
' Or
do rng1.Value = rng2.Value


So we have Alternative for:-
Rng.Copy
Rng.PasteSpecial Paste:= xlPasteValues

It works like this:-
We can use .Value Property two ways for any range, We can do this for Rng1 , Rng2 , Rngx …. Etc…

Way 1 Put values in ( for example, Rng2 ) :-
Rng2.Value = ‘ < -------------------------------- ‘ put values in Rng2

Way 2 Get values out ( for example for Rng1 ) :-
< -------------- = Rng1.Value ‘ Get values from Rng1

So we can take values out of a range and put them in another different range: -
Rng2.Value = Rng1.Value



Macro is in
Process.xlsm

Code:

---

Sub STEP4b() ' Rng1.Value = Rng2.Value
 Dim w1 As Workbook, w2 As Workbook
 Set w1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")                ' w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")          ' change the file path
 Set w2 = Workbooks.Open(ThisWorkbook.Path & "\FundsCheck.xlsb") ' w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
Dim Rng1 As Range, Rng2 As Range
' If first column and first row is used , then this will work only
 Set Rng1 = w1.Worksheets.Item(1).UsedRange '  or Set Rng1 = w2.Worksheets.Item(1).Range("A1:H" & Rng1.Rows.Count & "")
 Set Rng2 = w2.Worksheets.Item(1).Range("A1:H" & Rng1.Rows.Count & "")

'w1.Worksheets.Item(1).Columns("A:H").Copy
'w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 Let Rng2.Value = Rng1.Value

w2.Save
w2.Close
w1.Close
End Sub

---

Alan

---

1.xls : https://app.box.com/s/th2xzmkh7rnfr4qf4dho1kpgudndm073

Thnx Doc Sir for providing the Great info

Doc Sir i have a similar problem like this

Code:

---

Sub STEP4()
 Dim w1 As Workbook, w2 As Workbook
 Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") ' change the file path
 Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
w1.Worksheets.Item(1).Columns("A:H").Copy
w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
w2.Save
w2.Close
w1.Close
End Sub

---

this code paste the data to sheet1 what i need is to paste the data in sheet2 so what i should do for the same sir plz help me sir in solving this problem sir

Hello

Worksheets.Item(1) is first worksheet
Worksheets.Item(2) is second worksheet
Worksheets.Item(3) is Third worksheet
Workshe…….etc.

First4Worksheets.JPG : https://imgur.com/v0h9CaU
Attachment 2793

Try
w2.Worksheets.Item(2).Columns("A:H").Past……………

Thnx Alot Doc Sir i changed the same in the code but i forgot to create a second sheet
Done Doc Sir thnx alot Sir

vba is placed in sample2.xlsm
all files are located in a different places
sheet name can be anything
there are two files sample1.xlsx & sample2.xlsm
match column B of sample1.xlsx matches with column A of sample2.xlsm
if it matches then copy paste the data from column D to column H to sample2.xlsm from column B
plz help me in solving this problem Sir
plz see the result i have attached the pic of the same

Before:

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
1	SYMBOL
2	ACC
3	ADANIPORTS
4	AMARAJABAT
5

Worksheet: anything

_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1000	1030	955.5	998.45	957.4
3	NSE	ADANIENT	EQ	27.35	27.75	25.65	25.65	25.85
4	NSE	ADANIPORTS	EQ	259	259.6	244	248.2	251.3
5	NSE	ADANIPOWER	EQ	5, 4	5, 5	5, 6	5, 7	5, 8
6	NSE	AMARAJABAT	EQ	459.8	482.25	445.1	439.35	455.35
7	NSE	AMBUJACEM	EQ	7, 4	7, 5	7, 6	7, 7	7, 8
8	NSE	APOLLOHOSP	EQ	8, 4	8, 5	8, 6	8, 7	8, 8
9

Worksheet: anything


The range to be copied is always offset by 0 rows and +2 column from the cell found, FndCel , in column B of sample1.xlsx . Its size will be 1 row and 5 columns
The range to be searched for , rngSrch is B2:B8 in sample1.xlsx , or B2:B & Lr

rngSrch :

_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	B
2	ACC
3	ADANIENT
4	ADANIPORTS
5	ADANIPOWER
6	AMARAJABAT
7	AMBUJACEM
8	APOLLOHOSP

Worksheet: anything

In sample data are 3 FndCels

_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	B
2	ACC

Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
2	ACC

Worksheet: anything
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	B
4	ADANIPORTS

Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
3	ADANIPORTS

Worksheet: anything
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	B
6	AMARAJABAT

Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
4	AMARAJABAT

Worksheet: anything
( FndCels are in sample2.xlsx )


match column B of sample1.xlsx matches with column A of sample2.xlsx
if it matches then copy paste the data from column D to column H to sample2.xlsx from column B

Code:

---

Dim Cnt As Long
    For Cnt = 2 To Lr2
    Dim rngFnd As Range  '  http://www.excelfox.com/forum/showthread.php/2436-conditionally-delete-or-replace-entire-row?p=13007&viewfull=1#post13007
    Dim rngSrch As Range '
    Set rngSrch = Ws1.Range("B2:B" & Lr1 & "")
    Set rngFnd = rngSrch.Find(What:=Ws2.Range("A" & Cnt & "").Value, After:=Ws1.Range("B2"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) '  https://stackoverflow.com/questions/49094391/excel-vba-range-findnext-v-range-find-what-have-i-missed/49181464#49181464
    ' The range to be copied is always offset by 0 rows  and +2 column from the cell found, FndCel,  in column B of sample1.xlsx . Its size will be 1 row and 5 columns
    rngFnd.Offset(0, 2).Resize(1, 5).Copy '    copy column D to column H
    ' paste the data from column D to column H to sample2.xlsx from column B
    Ws2.Range("A" & Cnt & "").Offset(0, 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Next Cnt

---

Full Macro here: http://www.excelfox.com/forum/showth...ll=1#post13015




After result:-

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G
1	SYMBOL
2	ACC	1000	1030	955.5	998.45	957.4
3	ADANIPORTS	259	259.6	244	248.2	251.3
4	AMARAJABAT	459.8	482.25	445.1	439.35	455.35
5

Worksheet: anything



Alan

Sorry Doc Sir i made a mistake in the question plz relook the question Doc Sir
Sorry for the trouble sir

---

Moderator Translation:

I cant speak , write or understand a word of English I haven’t a clue what is written in this Thread, most of the time I haven’t got a clue what I wont and rarely even understand what I write myself, so I will post any rubbish and hope magically a code appears here that when I run it will do what I want. If not fuck thee twat helping me here: I have duplicated cross posted all over the place so maybe from there something will come of it.

:confused:
I do not understand :confused:
What is the problem?
What have you changed?
What was the mistake you made?
What is now different to your original question?

I can see no difference in question or answer.:confused:

vba is placed in sample2.xlsm
and i am unable to write the code for this so its my request Doc sir for this problem plz provide me the complete code
I got many examples of code from u, but in this problem vba will not be placed in a seperate file, macro will be placed in sample2.xlsm
so plz provide me the complete code sir

---

Moderator Translation:

I cant speak , write or understand a word of English I haven’t a clue what is written in this Thread, most of the time I haven’t got a clue what I wont and rarely even understand what I write myself, so I will post any rubbish and hope magically a code appears here that when I run it will do what I want. If not fuck thee twat helping me here: I have duplicated cross posted all over the place so maybe from there something will come of it.

Quote:

---

Originally Posted by fixer

so plz provide me the complete code sir

---

What is the problem with the complete macro that i gave you allready ??

ReadTheFukingPost.JPG : https://imgur.com/6UThPaD
Attachment 2824

Please always read all that I post.
If you can’t / won’t / don’t then there is no point in me posting anything for you

---

Quote:

---

Originally Posted by fixer

vba is placed in sample2.xlsm...., macro will be placed in sample2.xlsm...

---

That makes no difference to the macros that I have written for you...

Code:

---

Sub STEP6d()
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\SAMPLE1.xlsx")
Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\SAMPLE2.xlsm")
Dim Ws1 As Worksheet, Ws2 As Worksheet
 Set Ws1 = Wb1.Worksheets.Item(1)
 Set Ws2 = Wb2.Worksheets.Item(1)

---

Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\SAMPLE2.xlsm") this line has errors bcoz sample2.xlsm is already opened
plz let me know what i have to right in this line

Do you understand anything that I write???
Have you looked at the full macro that I already gave you???

Quote:

---

Originally Posted by fixer

and i am unable to write the code for this so its my request Doc sir for this problem plz provide me the complete code

so plz provide me the complete code sir

---

Quote:

---

Originally Posted by DocAElstein

What is the problem with the complete macro that i gave you allready ??

ReadTheFukingPost.JPG : https://imgur.com/6UThPaD
Attachment 2824
http://www.excelfox.com/forum/showth...ll=1#post13016
Full Macro here: http://www.excelfox.com/forum/showth...ll=1#post13015

Please always read all that I post.
If you can’t / won’t / don’t then there is no point in me posting anything for you
..

---

I have given you the full code already
Full Macro here: http://www.excelfox.com/forum/showth...ll=1#post13015

---

Quote:

---

Originally Posted by fixer

vba is placed in sample2.xlsm
I got many examples of code from u, but in this problem vba will not be placed in a seperate file, macro will be placed in sample2.xlsm

---

Quote:

---

Originally Posted by DocAElstein

That makes no difference to the macro that I have written for you...

---

I try again to explain..

You can Open and Set a workbook like these. These all open a workbook. But they will error if the workbook is already open - That is obvious: You cannot open if it is already open!

Code:

---

'  http://www.excelfox.com/forum/showthread.php/2421-copy-and-paste?p=13029&viewfull=1#post13029
Sub OpenAndSetWorkbook()
Dim Wb As Workbook
 Workbooks.Open Filename:="C:\ ........ MyFile.xls"              '  or Workbooks.Open "C:\ ........ MyFile.xls"

' Workbook "MyFile.xls" is now open ( and active*** )
 Set Wb = ActiveWorkbook ' The workbook just opened will be the active*** workbook
End Sub

Sub SetAndOpenWorkbook()
Dim Wb As Workbook
' Open and Set in same code line:
 Set Wb = Workbooks.Open("C:\ ........ MyFile.xls") ' this will Open and Set in same code line
End Sub

Sub OpenAndSetWorkbook_()
Dim Wb As Workbook
 Workbooks.Open Filename:="C:\ ........ MyFile.xls"          '  or Workbooks.Open "C:\ ........ MyFile.xls"
 
' Workbook "MyFile.xls" is now open ( and active )                                          If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb = Workbooks("MyFile.xls") ' Use workbooks collection object of open workbooks,  Workbooks(" ")  to reference already open workbooks
End Sub

---

---

If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks, Workbooks(" ")

Code:

---

Sub SetToAlreadyOpenWorkbook()
' For this to work,  MyFile.xls  must already be open
Dim Wb As Workbook
 
' If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb = Workbooks("MyFile.xls") ' Uses workbooks collection object of open workbooks,  Workbooks(" ")  to reference already open workbooks
End Sub

---

No Doubt Doc Sir u gave me the correct code with no errors in it
Actually i missed a small point in it and i dont know what to mentioned in that line, after u provided me the info of the same i putted that line and its 101% perfect
Thnx Alot Doc Sir and Sorry For the trouble
Thnx Alot for ur Great Support Doc Sir
Have a Great Day

Thnx Alan Elston Sir (Doc Sir)

vba is placed in a seperate file macro.xlsm
there are two files 1.xlsx & 2.xlsx
all files are located in a different place
2.xlsx file is blank file it doesn't have any data
in 1.xlsx i have data (i have attached the sample pic of the same)
now what i want is see the yellow highlighted colour data and if yellow highlighted colour data is greater than 5 or equal to 5 then copy the stock name and paste it to 2.xlsx
i have attached the sample pic of the result it will be pasted to 2.xlsx from 1.xlsx
so plz have a look sir and help me out in solving this problem sir

Hi
There are very many different ways to do something like this.
So this solution would be just one of many ways.

Example:

Before:

_____ Workbook: 1.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Stock Name	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
2	ACC	800	700	600	500	400	300	200	100	90	80	70
3	ADANIENT	800	700	600	500	400	300	200	100	90	80	70
4	ADANIPORTS	800	700	600	500	400	300	200	100	90	80	70
5	ADANIPOWER	800	700	600	500	400	3	200	100	90	80	70
6	AMARAJABAT	800	700	600	500	400	300	200	100	90	80	70
7	AMBUJACEM	800	700	600	500	400	300	200	100	90	80	70
8	ONGC	800	700	600	500	400	300	200	100	90	80	70
9

Worksheet: Sheet1 (2)

run macro here: http://www.excelfox.com/forum/showth...ll=1#post13059

Output results After running macro

_____ Workbook: 2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1	ACC	500
2	ADANIENT	700
3	AMARAJABAT	400
4

Worksheet: Tabelle2

---

macro is here : https://imgur.com/Rs0EaVf , and also in uploaded file.
Attachment 2838

Thnx Alot Doc Sir for helping me in solving this problem

All files are located in different path
vba will be placed in a macro.xlsm
i have a file name 1.xls & 2.xls
match column I of 1.xls with column B of 2.xls
If column I of 1.xls matches with column B of 2.xls then do nothing &
if column I of 1.xls doesnt matches with column B of 2.xls then copy and paste the column B & Column I of 1.xls to column A & column B of sheet2 of 2.xls
sheet name can be anything
plz see the sample pic & help me in solving this problem by vba

the bigger pic is 1.xls
the smallest pic is sheet2 of 2.xls (result)

---

Moderator notice...
Yet again another cross post
https://www.excelguru.ca/forums/show...-condition-met

I think you have not explained correctly what you want.
Your question explanation does not match you sample data.
Once again you have incorrectly explained what you want.

This is wrong!!!
If column I of 1.xls matches with column B of 2.xls then do nothing &
if column I of 1.xls doesnt matches with column B of 2.xls then copy and paste the column B & Column I of 1.xls to column A & column B of sheet2 of 2.xls
It is rubbish. It does not explain your test data.

Once again I must try to guess what you want!

This is my guess:
Consider the value in each row of column I of 1.xls, starting from row 2
If the value from that row of column I of 1.xls is also in any row of column B of the first worksheet in 2.xls , then
do nothing.
Else If the value from that row of column I of 1.xls is not to be found in any row of column B of the first worksheet in 2.xls, then do the following:
Copy the value from columns B and I for that row of 1.xls and paste them to columns A and B of the second worksheet of 2.xls

Before:
_____ Workbook: 2.xls ( Using Excel 2007 32 bit )

Row\Col	A	B
1	Exchange
2	NSE	25
3	NSE	10583
4	NSE	17388
5	NSE	100

Worksheet: Sheet1 (2)
_____ Workbook: 2.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1
2
3

Worksheet: Sheet2

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ADANIENT	EQ	1087	1030	955.5	998.45	1079.9	25
3	NSE	ACC	EQ	148.05	27.75	25.65	25.65	146.5	22
4	NSE	DLF	EQ	265	419.7	350.05	387.25	267.15	10583
5	NSE	AMBUJACEM	EQ	30.4	155.8	142.55	145.85	29.95	17388
6	NSE	AMARAJABAT	EQ	502	514.85	502	499.05	507.45	100

Worksheet: 1-Sheet1

After results
_____ Workbook: 2.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1	ACC	22
2

Worksheet: Sheet2

Macro:

Code:

---

Sub Step11() '    http://www.excelfox.com/forum/showthread.php/2421-copy-and-paste-by-VBA-based-on-criteria?p=13110&viewfull=1#post13110      http://www.excelfox.com/forum/showthread.php/2458-Copy-and-paste-the-data-if-condition-met
Rem 1 Worksheets info
Dim Wb1 As Workbook, Wb2 As Workbook  '                          If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb1 = Workbooks("1.xls")        ' Workbooks("1.xlsx")        '          Workbooks("sample1.xlsx")  '                                                Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")                ' w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")          ' change the file path  If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb2 = Workbooks("2.xls")        ' Workbooks("2.xlsx")        '          Workbooks("sample2.xlsx")  '                                                Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\FundsCheck.xlsb")      ' w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path      If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws22 As Worksheet
 Set Ws1 = Wb1.Worksheets.Item(1)      '                                                                            Set Ws1 = Wb1.Worksheets("anything")  '    sheet name can be anything
 Set Ws2 = Wb2.Worksheets.Item(1)      '                                                                          ' Set Ws2 = Wb2.Worksheets("anything")
 Set Ws22 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long, Lr As Long, Lr22 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Row      '  http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
 Let Lr2 = Ws2.Range("A" & Ws1.Rows.Count).End(xlUp).Row
' Let Lr = IIf(Lr2 > Lr1, Lr2, Lr1)
Rem 2 do it
Dim cnt
    For cnt = 2 To Lr2
    Dim VarMtch As Variant
    Let VarMtch = Application.Match(CStr(Ws1.Range("I" & cnt & "").Value), Ws2.Range("B2:B" & Lr2 & ""), 0) ' We look for the string value from each row in column I of Ws1 in the range of column B in Ws2
        If Not IsError(VarMtch) Then ' If we have a match, then  Application.Match  will return an integer of the position along(down) where the match is found
        ' do nothing
        Else '  Application.Match  will return a VB error string if no match could be found
        Ws1.Range("B" & cnt & ",I" & cnt & "").Copy  ' if ranges are "in line" - that is to say have the same "width" ( in this example a single row width ) , then VBA lets us copy this to the clipboard
        Let Lr22 = Lr22 + 1 ' next free row in second worksheet of 2.xls
        Ws22.Range("A" & Lr22 & "").PasteSpecial Paste:=xlPasteValues ' Pasting of copied values which were "in line" allows us to paste out, but the missing in between bits ( columns in this example ) are missed out - the ranges are put together. Co incidentally we want this output in this example
        End If
    Next cnt
End Sub

---

Minor changes are there in this post

Code:

---

Sub STEP9()
Dim Wb1 As Workbook, Wb2 As Workbook, Wb3 As Workbook
 Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
 Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")
 Set Wb3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\AlertCodes.xlsx")

Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
 Set Ws1 = Wb1.Worksheets.Item(1)
 Set Ws2 = Wb2.Worksheets.Item(1)
 Set Ws3 = Wb3.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long, Lr As Long, Lr3 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Row
 Let Lr2 = Ws2.Range("A" & Ws1.Rows.Count).End(xlUp).Row

Dim Cnt
    For Cnt = 2 To Lr3
    Dim VarMtch As Variant
    Let VarMtch = Application.Match(CStr(Ws1.Range("I" & Cnt & "").Value), Ws2.Range("B2:B" & Lr2 & ""), 0)
        If Not IsError(VarMtch) Then
       
        Else
        Ws1.Range("B" & Cnt & ",I" & Cnt & "").Copy
        Let Lr3 = Lr3 + 1
        Ws3.Range("A" & Lr3 & "").PasteSpecial Paste:=xlPasteValues
        End If
    Next Cnt
   
Wb1.Save
Wb1.Close
Wb2.Save
Wb2.Close
Wb3.Save
Wb3.Close

   
End Sub

---

the result was pasted in Ws22
but we have to paste the data(result to Ws3)
i changed the code and i tried to edit the same but i was unsuccessful in doing so plz see the code and change the vba code so that the result should be pasted in Ws3

If the only change is to paste the data to Ws3, then I see just one error in your macro ,
Why have you changed to
For Cnt = 2 To Lr3 ?
It should still be
For Cnt = 2 To Lr2
The macro is going down rows in worksheet Ws2 from row 2 until the last row which is Lr2
My Lr22 = your Lr3 is the row count for data being pasted out : For each new data is needed a new row - the next row - the next row will be .. + 1

If the only change is to paste to Ws3 , then my original macro is only needed to be changed in 5 places

Code:

---

Sub Step11b() '  http://www.excelfox.com/forum/showthread.php/2421-copy-and-paste-by-VBA-based-on-criteria?p=13110&viewfull=1#post13110  http://www.excelfox.com/forum/showthread.php/2458-Copy-and-paste-the-data-if-condition-met
Rem 1 Worksheets info
Dim Wb1 As Workbook, Wb2 As Workbook, Wb3 As Workbook  '                          If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb1 = .......  Workbooks("1.xls")        ' Workbooks("1.xlsx")        '          Workbooks("sample1.xlsx")  '                                                Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")                ' w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")          ' change the file path  If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb2 = .......    Workbooks("2.xls")        ' Workbooks("2.xlsx")        '          Workbooks("sample2.xlsx")  '                                                Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\FundsCheck.xlsb")      ' w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path      If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb3 = .......
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet ' Ws22 As Worksheet
 Set Ws1 = Wb1.Worksheets.Item(1)      '                                                                            Set Ws1 = Wb1.Worksheets("anything")  '    sheet name can be anything
 Set Ws2 = Wb2.Worksheets.Item(1)      '                                                                          ' Set Ws2 = Wb2.Worksheets("anything")
' Set Ws22 = Wb2.Worksheets.Item(2)
Set Ws3 = Wb3.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long, Lr As Long, Lr22 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Row      '  http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
 Let Lr2 = Ws2.Range("A" & Ws1.Rows.Count).End(xlUp).Row
' Let Lr = IIf(Lr2 > Lr1, Lr2, Lr1)
Rem 2 do it
Dim Cnt
    For Cnt = 2 To Lr2
    Dim VarMtch As Variant
    Let VarMtch = Application.Match(CStr(Ws1.Range("I" & Cnt & "").Value), Ws2.Range("B2:B" & Lr2 & ""), 0) ' We look for the string value from each row in column I of Ws1 in the range of column B in Ws2
        If Not IsError(VarMtch) Then ' If we have a match, then  Application.Match  will return an integer of the position along(down) where the match is found
        ' do nothing
        Else '  Application.Match  will return a VB error string if no match could be found
        Ws1.Range("B" & Cnt & ",I" & Cnt & "").Copy  ' if ranges are "in line" - that is to say have the same "width" ( in this example a single row width ) , then VBA lets us copy this to the clipboard
        Let Lr22 = Lr22 + 1 ' next free row in second worksheet of 2.xls
        'Ws22.Range("A" & Lr22 & "").PasteSpecial Paste:=xlPasteValues ' Pasting of copied values which were "in line" allows us to paste out, but the missing in between bits ( columns in this example ) are missed out - the ranges are put together. Co incidentally we want this output in this example
        Ws3.Range("A" & Lr22 & "").PasteSpecial Paste:=xlPasteValues
        End If
    Next Cnt
End Sub

---

or if you prefer to use a different variable for the row count in Ws3 , Lr3 , then

Code:

---

Sub Step11b() '  http://www.excelfox.com/forum/showthread.php/2421-copy-and-paste-by-VBA-based-on-criteria?p=13110&viewfull=1#post13110  http://www.excelfox.com/forum/showthread.php/2458-Copy-and-paste-the-data-if-condition-met
Rem 1 Worksheets info
Dim Wb1 As Workbook, Wb2 As Workbook, Wb3 As Workbook  '                          If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb1 = .......  Workbooks("1.xls")        ' Workbooks("1.xlsx")        '          Workbooks("sample1.xlsx")  '                                                Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")                ' w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")          ' change the file path  If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb2 = .......    Workbooks("2.xls")        ' Workbooks("2.xlsx")        '          Workbooks("sample2.xlsx")  '                                                Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\FundsCheck.xlsb")      ' w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path      If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb3 = .......
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet ' Ws22 As Worksheet
 Set Ws1 = Wb1.Worksheets.Item(1)      '                                                                            Set Ws1 = Wb1.Worksheets("anything")  '    sheet name can be anything
 Set Ws2 = Wb2.Worksheets.Item(1)      '                                                                          ' Set Ws2 = Wb2.Worksheets("anything")
' Set Ws22 = Wb2.Worksheets.Item(2)
Set Ws3 = Wb3.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long, Lr As Long, Lr3 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Row      '  http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
 Let Lr2 = Ws2.Range("A" & Ws1.Rows.Count).End(xlUp).Row
' Let Lr = IIf(Lr2 > Lr1, Lr2, Lr1)
Rem 2 do it
Dim Cnt
    For Cnt = 2 To Lr2
    Dim VarMtch As Variant
    Let VarMtch = Application.Match(CStr(Ws1.Range("I" & Cnt & "").Value), Ws2.Range("B2:B" & Lr2 & ""), 0) ' We look for the string value from each row in column I of Ws1 in the range of column B in Ws2
        If Not IsError(VarMtch) Then ' If we have a match, then  Application.Match  will return an integer of the position along(down) where the match is found
        ' do nothing
        Else '  Application.Match  will return a VB error string if no match could be found
        Ws1.Range("B" & Cnt & ",I" & Cnt & "").Copy  ' if ranges are "in line" - that is to say have the same "width" ( in this example a single row width ) , then VBA lets us copy this to the clipboard
        Let Lr3 = Lr3+ 1 ' next free row in second worksheet of 2.xls
        'Ws22.Range("A" & Lr22 & "").PasteSpecial Paste:=xlPasteValues ' Pasting of copied values which were "in line" allows us to paste out, but the missing in between bits ( columns in this example ) are missed out - the ranges are put together. Co incidentally we want this output in this example
        Ws3.Range("A" & Lr3 & "").PasteSpecial Paste:=xlPasteValues
        End If
    Next Cnt
End Sub

---

Problem Solved
Thnx Doc Sir for helping me in solving this problem Sir
Have a Great Day Sir