Printable View
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 )
Worksheet: anything
Row\Col A 1 SYMBOL 2 ACC 3 ADANIPORTS 4 AMARAJABAT 5
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Worksheet: anything
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 3 NSE ADANIENT EQ 4 NSE ADANIPORTS EQ 5 NSE ADANIPOWER EQ 5, 4 5, 5 5, 6 5, 7 5, 8 6 NSE AMARAJABAT EQ 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
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 )
Worksheet: anything
Row\Col B 2 ACC 3 ADANIENT 4 ADANIPORTS 5 ADANIPOWER 6 AMARAJABAT 7 AMBUJACEM 8 APOLLOHOSP
In sample data are 3 FndCels
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Worksheet: anything
Row\Col B 2 ACC
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Worksheet: anything
Row\Col ACC
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Worksheet: anything
Row\Col B 4 ADANIPORTS
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Worksheet: anything
Row\Col ADANIPORTS
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Worksheet: anything
Row\Col B 6 AMARAJABAT
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Worksheet: anything
Row\Col AMARAJABAT
( 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
Full Macro here: http://www.excelfox.com/forum/showth...ll=1#post13015Code: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
After result:-
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Worksheet: anything
Row\Col SYMBOL ACC ADANIPORTS AMARAJABAT
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.
What is the problem with the complete macro that i gave you allready ??Quote:
Originally Posted by fixer
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
That makes no difference to the macros that I have written for you...Quote:
Originally Posted by fixer
Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\SAMPLE2.xlsm") this line has errors bcoz sample2.xlsm is already openedCode: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)
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
I have given you the full code alreadyQuote:
Originally Posted by DocAElstein
Full Macro here: http://www.excelfox.com/forum/showth...ll=1#post13015
Quote:
Originally Posted by fixer
Quote:
Originally Posted by 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!
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