copy and paste by VBA based on criteria

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

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

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

I do not understand
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.

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

---

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

---

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