copy and paste by VBA based on criteria

[fixer]

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/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (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=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=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=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (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=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (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=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

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.php?f=30&t=34112#p264458
http://www.eileenslounge.com/viewtopic.php?f=27&t=25002#p193871
https://docs.microsoft.com/en-us/office/vba/api/excel.range.pastespecial

You can chose the way you want to Paste, and formats
https://docs.microsoft.com/en-us/office/vba/api/excel.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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12531&viewfull=1#post12531

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


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

[fixer]

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.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=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=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=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=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=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=312727#p312727)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724 (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?f=44&t=40374&p=312535#p312535)
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533 (https://www.eileenslounge.com/viewtopic.php?p=312533#p312533)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499 (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.9xhyRrsUUOM9xpn-GDkL3o)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (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)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

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 )

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

[fixer]

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

[DocAElstein]

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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11479&viewfull=1#post11479
http://www.excelfox.com/forum/showthread.php/2369-Calculation-by-vba?p=11485&viewfull=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



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

[fixer]

Thnx Doc Sir for providing the Great info

[fixer]

Doc Sir i have a similar problem like this

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

[DocAElstein]

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
2793

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

[fixer]

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

[fixer]

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

[DocAElstein]

Before:

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\ColA
1SYMBOL

2ACC

3ADANIPORTS

4AMARAJABAT

5
Worksheet: anything

_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Row\ColABCDEFGHI
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ
1000
1030
955.5
998.45
957.4

3NSEADANIENTEQ
27.35
27.75
25.65
25.65
25.85

4NSEADANIPORTSEQ
259
259.6
244
248.2
251.3

5NSEADANIPOWEREQ5, 45, 55, 65, 75, 8

6NSEAMARAJABATEQ
459.8
482.25
445.1
439.35
455.35

7NSEAMBUJACEMEQ7, 47, 57, 67, 77, 8

8NSEAPOLLOHOSPEQ8, 48, 58, 68, 78, 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\ColB
2ACC

3ADANIENT

4ADANIPORTS

5ADANIPOWER

6AMARAJABAT

7AMBUJACEM

8APOLLOHOSP
Worksheet: anything

In sample data are 3 FndCels

_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Row\ColB
2ACC
Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A

2ACC
Worksheet: anything
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Row\ColB
4ADANIPORTS
Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A

3ADANIPORTS
Worksheet: anything
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Row\ColB
6AMARAJABAT
Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A

4AMARAJABAT
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

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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13015&viewfull=1#post13015




After result:-

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G

1SYMBOL


2ACC
1000
1030
955.5
998.45
957.4


3ADANIPORTS
259
259.6
244
248.2
251.3


4AMARAJABAT
459.8
482.25
445.1
439.35
455.35


5
Worksheet: anything



Alan

[fixer]

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.

[DocAElstein]

: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:

[fixer]

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.

[DocAElstein]

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





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

[fixer]

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

[DocAElstein]

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


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

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

ReadTheFukingPost.JPG : https://imgur.com/6UThPaD
2824
http://www.excelfox.com/forum/showthread.php/2421-copy-and-paste?p=13016&viewfull=1#post13016
Full Macro here: http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13015&viewfull=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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13015&viewfull=1#post13015


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


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

[DocAElstein]

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!

' 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(" ")


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

[fixer]

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

[fixer]

Thnx Alan Elston Sir (Doc Sir)

[fixer]

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

[DocAElstein]

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

1Stock NameDataDataDataDataDataDataDataDataDataDataDataDa ta


2ACC
800
700
600
500
400
300
200
100
90
80
70


3ADANIENT
800
700
600
500
400
300
200
100
90
80
70


4ADANIPORTS
800
700
600
500
400
300
200
100
90
80
70


5ADANIPOWER
800
700
600
500
400
3
200
100
90
80
70


6AMARAJABAT
800
700
600
500
400
300
200
100
90
80
70


7AMBUJACEM
800
700
600
500
400
300
200
100
90
80
70


8ONGC
800
700
600
500
400
300
200
100
90
80
70


9
Worksheet: Sheet1 (2)

run macro here: http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13059&viewfull=1#post13059

Output results After running macro

_____ Workbook: 2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C

1ACC500


2ADANIENT700


3AMARAJABAT400


4
Worksheet: Tabelle2


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

[fixer]

Thnx Alot Doc Sir for helping me in solving this problem

[fixer]

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/showthread.php?10573-Copy-and-paste-the-data-if-condition-met

[DocAElstein]

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

1Exchange


2NSE25


3NSE10583


4NSE17388


5NSE100
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

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEADANIENTEQ
1087
1030
955.5
998.45
1079.9
25


3NSEACCEQ
148.05
27.75
25.65
25.65
146.5
22


4NSEDLFEQ
265
419.7
350.05
387.25
267.15
10583


5NSEAMBUJACEMEQ
30.4
155.8
142.55
145.85
29.95
17388


6NSEAMARAJABATEQ
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

1ACC
22


2
Worksheet: Sheet2

Macro:

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

[fixer]

Minor changes are there in this post

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.xl sx")

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

[DocAElstein]

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



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



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

[fixer]

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