VBA code for updating master workbook with data in raw file

  • Hi All,


    I am trying to work on a code that would enable me to copy data from the raw file and update the individual sheets in the master workbook for each value in column A of raw file.Background: There are many unique IDs mentioned in column A of the raw file, other columns contain corresponding data to each unique id. Each unique ID has a separate sheet in the master workbook.Requirement:


    • Remove unwanted unique ids mentioned in the to be removed sheet in raw file
    • Copy the entire row from the raw file, locate the relevant unique ID sheet in the master workbook and paste the data in the last row.
    • In case the unique id sheet is not there in the master workbook then to create it and paste the data.

    Problem:


    • The code that I have gets stuck on locating the correct sheet in the master, it is unable to locate the sheet and when it goes to create a new sheet with the name it gives the error that the sheet name already exists.
    • In case there is a requirement to create a new sheet for a unique id, it should continue looping and paste data for other ids as well.
    • It should give a message box in the end giving details of all new sheets that were created.



    Thanks

  • Re: VBA code for updating master workbook with data in raw file


    Hi again and thanks for amending the post.


    I'd suggest a couple of things that may help make things a little easier.


    1. Make use of a worksheet variable. It's much easier to make reference to the intended object that way.
    2. Try using simple functions to undertake tests like seeing whether a worksheet already exists.


    Combing the above might give us something like the code below. It makes use of a worksheet variable, wsNew, and a function that tests for the existence of a worksheet - in this case, if the sheet name doesn't already exist then a new sheet is added and given that name.

  • Re: VBA code for updating master workbook with data in raw file


    Hey Richie,
    I am already using a worksheet variable Sname in my code. However, when the code assigns a value to Sname (from Column A of raw file) it is unable to locate the same sheet in the master file. It goes to the New_tab section and creates a new sheet, however is unable to rename the sheet to the Unique ID value since a sheet with that name already exists in the master.


    There is some issue in the logic used to locate the Sname sheet in the master i guess.


    Thanks

  • Re: VBA code for updating master workbook with data in raw file


    Hi VarunV


    Actually you are not using a worksheet variable - Sname is a Variant variable that you are using to hold details of the unique ID data. Try using Workbook variables for the workbooks that you are working with and Worksheet variables for the sheets within them. If I get time I'll re-work your routine to show what I mean but you should give it a go for yourself.


    In the meantime, the following should help to illustrate what I mean. (Note that I've used Activeworkbook in the illustration just for speed after downloading your example workbook - I would normally open it by assigning it to a workbook variable. For example, declare a variable like this : Dim wbkRaw As Workbook and then open the Raw workbook like this : Set wbkRaw = Workbooks.Open(Filename:=NewFN) - much easier to reference this way.)




Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!