Macro

[shahji]

I am trying to create a macro which could copy information from a datasheet and past it into sheets which are named as one of the Cells in column A.

I have a datasheet which contains five columns full of data. Column A contains the Client references. There are 200 sheets and each client reference has a seperate sheet which has datafields.

I need a mcro which could lookup client reference in datasheet column A and find the sheet named with the client reference then copy information from datasheet row B:E and paste it in column A:E of reference sheet.

thank you for your help,

shah

[Tu22]

I am trying to create a macro which could copy information from a datasheet and past it into sheets which are named as one of the Cells in column A.

I have a datasheet which contains five columns full of data. Column A contains the Client references. There are 200 sheets and each client reference has a seperate sheet which has datafields.

I need a mcro which could lookup client reference in datasheet column A and find the sheet named with the client reference then copy information from datasheet row B:E and paste it in column A:E of reference sheet.

thank you for your help,

shah

Hi Shahji,
Find the below code which will help in getting the values from the spread sheet... (the code just shows you to retrive the valuce from a cell. So, Modify as per your requirement, using Loop...Construct)

Set objexcel = CreateObject("Excel.Application")
dtsheet = objexcel.GetOpenFilename("All Files (*.*),*.*")
Set objdtsheet = objexcel.Workbooks.Open(dtsheet)
Set objsheet = objdtsheet.ActiveSheet

'Initializations
vRowPointer = 1
vColPointer = 1

vCellValue = objsheet.Cells(vRowPointer, vColResult).Value)

objdtsheet.Save
objdtsheet.Close
Set objexcel = Nothing
Set objsheet = Nothing

Tip:Always follow proper Indentation for better look and maintenance of the code

[shahji]

thank you very much Tu22,

i am very new to VBA and it seems very difficult for me to understand the code you have provided me with. i have attached an example of my database which is a cut down version.

On the Datasheet i have columns B, C, D and E which are named and same names are used on other sheets as well. I need to copy the data in from the Datasheet and past is in relevant sheets which correspond to the Trust No..

there is one problem though that there are more than one row which will correspond to the same sheet. i do not want to over write the information so the macro should offset/ go to blank row each time before pasting the information.

once again thank you for your help.

shah

[Tu22]

thank you very much Tu22,

i am very new to VBA and it seems very difficult for me to understand the code you have provided me with. i have attached an example of my database which is a cut down version.

On the Datasheet i have columns B, C, D and E which are named and same names are used on other sheets as well. I need to copy the data in from the Datasheet and past is in relevant sheets which correspond to the Trust No..

there is one problem though that there are more than one row which will correspond to the same sheet. i do not want to over write the information so the macro should offset/ go to blank row each time before pasting the information.

once again thank you for your help.

shah

Shah thats good to know that you want to learn. even i am also new to marco... user

I will complete the code and will attach the sheet for you in a simpler way and genric way so that you can reUse the code...

prasant

[Tu22]

Shah thats good to know that you want to learn. even i am also new to marco... user

I will complete the code and will attach the sheet for you in a simpler way and genric way so that you can reUse the code...

prasant

I have attached the Code.
The Code Descripton:
1) Copy the Code in Notepad to a new Excel sheet and save that.
2) Now Open the new worksheet and run the code. it will ask you for the datasheet/spreadsheet that you want to update.
3) select the spreadsheet from the Open Browser displayed.
4) then it will populate a msg saying Updation completed at the End.
5) now see the spreadsheet, I hope now u r done with the updation.

NOTE: The Code also checks for the sheets that are present or not in the spreadsheet, if not it will create a new sheet as required.
Only Limitation to the code is the no. of columns and the format of the sheets that are there in the attached datasheet that u have sent is fixed. I mean they are not dynamic..:)
if u still have few more columns in ur original sheet, then modify the code accordingly.

Hope u r done now. If still u have few more doubts feel free to contact through my personal email id that is there in my Signature.

regards.

Tips: Follow a proper Indentation for better maintenance