I am no master of VBA and tried to record macro’s to do the job, but couldn’t get the job done. Couldn’t even make sense of putting together some macro’s found on the forums. I need help in doing the following. After many fruitless hours, can someone come to the rescue.
The following is the basic info I think you will need and what I want done.
Much appreciated
“OPEN” = Open Workbook (C:…/PNF Trading.xlsm); Sheet 1
“Closed” = Closed Workbook (C:…/Today.csv); Today [Have only one sheet]
In “OPEN”
1. Select A1
2. Insert row
3. Copy A2:N2 and Paste to A1:N1
In “Closed”
1. Find the value of Cell B1 in “Open”, here in Column A of “Closed”
2. From address of result of Find (A*), Copy Adjacent Cells in Column F, G, H, I, J, K in the same row (*)
In “Open”
Paste Copied Values in C1
Find value from open workbook in closed workbook and copy adjacent cells



Re: Find value from open workbook in closed workbook and copy adjacent cells
Try this macro. Be sure to back up your files. You also need to change the filePath variable to the correct file path.
Code
Display MoreOption Explicit Sub macro() Dim ex As Excel.Application Dim wrkbk As Workbook Dim sht As Worksheet Dim row As Integer Dim filePath As String Dim a As Variant 'Path to closed file filePath = "C:\Users\user\Documents\Today.csv" 'Stop updating the screen Application.ScreenUpdating = False 'Insert new row Rows(1).Insert 'Copy a = Range("A2:N2").Value 'Paste Range("A1:N1").Value = a 'Create new instance of excel Set ex = New Excel.Application 'Open the workbook Set wrkbk = ex.Workbooks.Open(filePath) 'Grab the first sheet in wrkbk Set sht = wrkbk.Sheets(1) 'Find value in B1 in colum A of closed workbook row = sht.Range("A:A").Find(Range("B1").Value, , xlValues, xlWhole).row 'Copy a = sht.Range("F" & row, "K" & row).Value 'Paste Range("C1").Resize(, 6).Value = a 'Close workbook wrkbk.Close 'Exit excel ex.Quit 'Set the screen to update Application.ScreenUpdating = True End Sub

Re: Find value from open workbook in closed workbook and copy adjacent cells
dbrown14
Thank you! You beauty!
Much appreciated.
Pieter Jordaan 
Re: Find value from open workbook in closed workbook and copy adjacent cells
dbrown14
I found that only the value is copied, which changes the date from 07/05/2012 to 05/07/2012. I presume it has to do with my misuse of jargon in my explanation, and that formatting isn't copied. Probably to do with ".value". I have no idea to change this. Is it possible?
Thanx Pieter 
Re: Find value from open workbook in closed workbook and copy adjacent cells
Try setting the format of the cells to the correct format again, then run the macro. If my macro changes the formatting of the cells again contact me here again.


Re: Find value from open workbook in closed workbook and copy adjacent cells
I tried but it did the same.

Re: Find value from open workbook in closed workbook and copy adjacent cells
Which cells are date formatted that are being changed?

Re: Find value from open workbook in closed workbook and copy adjacent cells
Sorry, Lunch
Open  Column "C"
Closed  Column "F" 
Re: Find value from open workbook in closed workbook and copy adjacent cells
Try this
Code
Display MoreOption Explicit Sub macro() Dim ex As Excel.Application Dim wrkbk As Workbook Dim sht As Worksheet Dim row As Integer Dim filePath As String Dim a As Variant 'Path to closed file filePath = "C:\Users\user\Documents\Today.csv" 'Stop updating the screen Application.ScreenUpdating = False 'Insert new row Rows(1).Insert 'Copy a = Range("A2:N2").Value 'Paste Range("A1:N1").Value = a 'Create new instance of excel Set ex = New Excel.Application 'Open the workbook Set wrkbk = ex.Workbooks.Open(filePath) 'Grab the first sheet in wrkbk Set sht = wrkbk.Sheets(1) 'Find value in B1 in colum A of closed workbook row = sht.Range("A:A").Find(Range("B1").Value, , xlValues, xlWhole).row 'Copy a = sht.Range("F" & row, "K" & row).Value 'Paste Range("C1").Resize(, 6).Value = a sht.Range("F:F").NumberFormat = "dd/mm/yyyy" 'Close workbook wrkbk.Close 'Exit excel ex.Quit Range("C:C").NumberFormat = "dd/mm/yyyy" 'Set the screen to update Application.ScreenUpdating = True End Sub

Re: Find value from open workbook in closed workbook and copy adjacent cells
Nope, as soon as i run it, it gives me a OLE path error?


Re: Find value from open workbook in closed workbook and copy adjacent cells
You might want to open you CSV file to see if the formats stay there.
Try this though.Code
Display MoreOption Explicit Sub macro() Dim ex As Excel.Application Dim wrkbk As Workbook Dim sht As Worksheet Dim row As Integer Dim filePath As String Dim a As Variant 'Path to closed file filePath = "C:\Users\user\Documents\Today.csv" 'Stop updating the screen Application.ScreenUpdating = False 'Insert new row Rows(1).Insert 'Copy a = Range("A2:N2").Value 'Paste Range("A1:N1").Value = a 'Create new instance of excel Set ex = New Excel.Application 'Open the workbook Set wrkbk = ex.Workbooks.Open(filePath, , True) 'Grab the first sheet in wrkbk Set sht = wrkbk.Sheets(1) 'Find value in B1 in colum A of closed workbook row = sht.Range("A:A").Find(Range("B1").Value, , xlValues, xlWhole).row 'Copy a = sht.Range("F" & row, "K" & row).Value 'Paste Range("C1").Resize(, 6).Value = a 'Close workbook wrkbk.Close 'Exit excel ex.Quit Range("C:C").NumberFormat = "dd/mm/yyyy" 'Set the screen to update Application.ScreenUpdating = True End Sub

Re: Find value from open workbook in closed workbook and copy adjacent cells
Hello,
It would be great if you can provide the sample files.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!