VBA code for updating master workbook with data in raw file

VarunV · Jul 29th 2012

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.

Code

Sub unique_ids()
Dim NewFN As String, MasterFN As String
Dim lrow As Long, i As Long, drow As Long, j as Long
Dim rngf As Range, rngv As Range
Dim SName As Variant
Dim FoundDup As Range


'Open the Master file
proceed:
MasterFN = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*",       Title:="Please open the Master File")
If MasterFN = "" Then
MsgBox "You have not selected a file."
GoTo proceed
Else
Workbooks.Open Filename:=MasterFN
End If


MasterFN = ActiveWorkbook.Name


'Open the raw file
proceed1:
NewFN = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the raw File")
If NewFN = "" Then
MsgBox "You have not selected a file."
GoTo proceed1
Else
Workbooks.Open Filename:=NewFN
End If


'Save backup file
ActiveWorkbook.SaveAs Filename:="D:\Counts-" & Format(Date, "dd-mmm-yy") & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
Workbooks("Counts-" & Format(Date, "dd-mmm-yy") & ".xlsx").Close
Workbooks.Open Filename:=NewFN
NewFN = ActiveWorkbook.Name


'Delete the "to be removed" IDs
Sheets("counts").Select
For Row = Range("A65536").End(xlUp).Row To 2 Step -1


Set FoundDup = Sheets("To be deleted").Range("A:A").Find(Cells(Row, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not FoundDup Is Nothing Then
Cells(Row, 1).EntireRow.Delete
End If
Next Row


‘Update Data
For j = 2 To lrow
SName = Workbooks(NewFN).Worksheets("counts").Range("K" & i).Value
On Error GoTo new_tab
Workbooks(NewFN).Worksheets("Counts").Range("A" & i & ":I" & i).Copy     Workbooks(MasterFN).Worksheets(SName).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Workbooks(MasterFN).Worksheets(SName).Range("A" & Rows.Count).End(xlUp).Value = Format(Date, "dd-mmm-yy")
drow = Workbooks(MasterFN).Worksheets(SName).Range("K" & Rows.Count).End(xlUp).Offset(-1, 0).Row
Workbooks(MasterFN).Worksheets(SName).Range("K" & drow - 1 & ":S" & drow - 1).Copy Workbooks(MasterFN).Worksheets(SName).Range("K" & drow & ":S" & drow)
Next j


new_tab:
MsgBox "New ID encountered", vbCritical
Workbooks(MasterFN).Sheets.Add(after:=Workbooks(MasterFN).Sheets(Worksheets.Count)).Name = SName
Workbooks(NewFN).Worksheets("counts").Range("A" & i & ":I" & i).Copy       Workbooks(MasterFN).Worksheets(SName).Range("B" & Rows.Count).End(xlUp)
Workbooks(MasterFN).Worksheets(SName).Range("A" & Rows.Count).End(xlUp).Value = Format(Date, "dd-mmm-yy")
drow = Workbooks(MasterFN).Worksheets(SName).Range("K" & Rows.Count).End(xlUp)
Workbooks(MasterFN).Worksheets(SName).Range("K" & drow - 1 & ":S" & drow - 1).Copy  Workbooks(MasterFN).Worksheets(SName).Range("K" & drow & ":S" & drow)


MsgBox "This work is now complete, new sheet added - " & SName


End Sub

Display More

Thanks

Richie(UK) · Jul 29th 2012

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

Hi,

I don't know what sort of formatting you were aiming for but ... it's not working. Please just re-post your code remembering to add code tags around it (and nothing else). Thanks.

VarunV · Jul 29th 2012

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

Hey Richie,

Sorry for the formatting issues...the code has now been updated.

Many Thanks for pointing it out.

Richie(UK) · Jul 29th 2012

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.

Code

Sub test_ws_add()
    Dim strWSname As String
    Dim wsNew As Worksheet
    
    strWSname = "Sheet4"
    If Not SheetExists(strWSname) Then
        Set wsNew = Worksheets.Add
        wsNew.Name = strWSname
    End If
    
End Sub


Function SheetExists(strWSname As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSname)
    On Error GoTo 0
    If Not ws Is Nothing Then SheetExists = True
End Function

Display More

VarunV · Aug 2nd 2012

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

Richie(UK) · Aug 2nd 2012

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.)

Code

Sub test_ws_add()
    Dim rngRaw As Range, rngCell As Range
    Dim wsRaw As Worksheet
    Dim wsNew As Worksheet
     
    Set wsRaw = ActiveWorkbook.Worksheets("Counts")
    Set rngRaw = wsRaw.Range("A2:A" & wsRaw.Cells(wsRaw.Rows.Count, "A").End(xlUp).Row)
    'set range of unique IDs
    
    For Each rngCell In rngRaw
        If Not SheetExists(rngCell.Value) Then
            Set wsNew = Worksheets.Add
            wsNew.Name = rngCell.Value
        End If
    Next rngCell
    'loop through each unique ID and add sheet if doesn't already exist
    
End Sub
 
Function SheetExists(strWSname As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSname)
    On Error GoTo 0
    If Not ws Is Nothing Then SheetExists = True
End Function

Display More

VarunV · Aug 10th 2012

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

Hey Richie,

Many thanks for helping me out on this, I will give this a go and let you know what the outcome is.

VBA code for updating master workbook with data in raw file

Participate now!

Share