Hi,
Paste this macro in the first workbook. Open it manually and run the “Test”
macro.
Sub test()
Dim lgRow As Long, sh As Worksheet, c As Range, rg As Range
Const strPath As String = "D:\Donnees\Daniel\mpfe\Prathima R\" 'to be
modified
Dim inCalculationMode As Integer
Application.ScreenUpdating = False
inCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Set sh = Sheets("Data")
lgRow = [Data!B65536].End(xlUp).Row + 1
Workbooks.Open strPath & _
"Supplier_Maintenance_170_Data_without_SAP_Document_Number.xls"
With Sheets("Data")
.Range(.[Q3], .Cells(65536, 2).End(xlUp)).Copy sh.Cells(lgRow, 2)
End With
ActiveWorkbook.Close False
With sh
.Range("T1").EntireColumn.Insert
.Range("T2").Value = "Region"
.Range("T1").EntireColumn.WrapText = True
End With
'Global Company Code List (3).xls
Workbooks.Open strPath & _
"Global Company Code List (3).xls"
Set sh1 = Sheets("Markets_Comp Code")
With sh1
Set rg = .Range(.[A2], .[C65536].End(xlUp))
For Each c In sh.Range(sh.[S3], sh.[S65536].End(xlUp)).Offset(, 1)
c.Value = Application.VLookup(c.Offset(, -1) * 1, rg, 3, 0)
Next c
End With
ActiveWorkbook.Close False
Application.Calculation = inCalculationMode
Application.ScreenUpdating = True
End Sub
Regards.
Daniel
De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de Prathima R
Envoyé : lundi 4 avril 2011 13:16
À : excel-macros@googlegroups.com; ayush jain; ashish koul
Objet : $$Excel-Macros$$ Fwd: Need Help
Hi experts,
I am not good at excel vba, i just tried recording macro but i am
unsuccessful.. please help me i have two excel work books
1) SM_MV170R_Q0001_170_Data_with_SAP_Doc_Number
2) Supplier_Maintenance_170_Data_without_SAP_Document_Number
i will first open the SM_MV170R_Q0001_170_Data_with_SAP_Doc_Number
and go to end of the blank row then open the second work book
Supplier_Maintenance_170_Data_without_SAP_Document_Number
copy the entire data and paste it in first work book which is
SM_MV170R_Q0001_170_Data_with_SAP_Doc_Number
Then insert one column and name the column as Region after the company code
format the excel sheet using wrap text and grid lines.
Then using the company code will be pulling the Region name from Global
Company Code List with help of Vlookup.
Please help me in preparing the macro
Thanks alot in advance.
Regards,
Prathima
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
