Thanks alot Daniel
On Mon, Apr 4, 2011 at 2:38 PM, Daniel <dcolarde...@free.fr> wrote:
> 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 d**e* 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
>
--
----------------------------------------------------------------------------------
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
