Padwan
08-02-2014, 05:31 AM
Hi Guys,
I'm currently working on creating a ‘Master Reporting Tracker’ which will link to a bundle of excel worksheets which will be saved into the same folder/directory. I’m trying to link it between a few worksheets from different workbooks; linking them previously created a massive delay when trying to update the information as there were a bundle of formulas involved. So the approach I decided to use VBA to help solve this issue. First I added an Update File Sheet in my ‘Master Reporting Tracker’ which will have buttons to Insert the worksheet or update the work sheet depending on the situation (I couldn’t work out the update worksheet) I created modules and added in buttons in the ‘update files’ worksheet so I could use the button to add the Worksheet into the Workbook; I wasn’t able to create an update file script as my VBA skills aren’t that good and I couldn’t find anything online. The reason I cant use import file every time is it ruins my formulas which are present in my reporting sheets that are also present in the tracker…I don’t want to keep amending the formulas so I thought I’d take this approach.
Also is there any way I could add a weekly report feature to go through the SharePoint worksheet (Column U) and search for 2 user defined dates; I tried searching for something that would give a pop up when you select the module and ask you to enter two dates (Start and End) and it would copy all the information that falls in the criteria onto ‘Weekly Report’ adding 3 x Columns from B (B being ‘BU’, C being ‘Analysis’, D being ‘Reason’) with a formula such as:
IF A1 in ‘Weekly Report’ is present in A2 column ‘Def Tracker’ then copy same row AA and AB & paste it in Weekly Tracker ‘B2’ and ‘C3’. Whenever the script runs it leaves the worksheet as is but copies new Data that falls under the criteria
Worksheets I have are:
· Update Files – Sheet that contains buttons linking to the modules (scripts). Planning to use this as a main sheet with multiple buttons/modules.
· Master Tracker – Worksheet from a separate workbook (needs to have a feature to IMPORT/UPDATE without creating a new Worksheet)
· Def Tracker - Worksheet from a separate workbook (needs to have a feature to IMPORT/UPDATE without creating a new Worksheet)
· ConS Report - Worksheet from a separate workbook (needs to have a feature to IMPORT/UPDATE without creating a new Worksheet)
· SharePoint (.CSV)- Worksheet from a separate workbook (needs to have a feature to IMPORT/UPDATE without creating a new Worksheet)
· Weekly Report – To be generated from Update File once above criteria matches.
· Some pivot table sheets (which will be created later)
Please note worksheets that are being import contain 10000+ entities which are growing every day. this is what I've got so far; but it doesn't help updating the data as it always creates a new sheet. help would be much appreciated.
Sub ImportDefect()
Dim sImportFile As String, sFile As String
Dim sThisBk As Workbook
Dim vfilename As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sThisBk = ActiveWorkbook
sImportFile = Application.GetOpenFilename( _
FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")
If sImportFile = "False" Then
MsgBox "No File Selected!"
Exit Sub
Else
vfilename = Split(sImportFile, "\")
sFile = vfilename(UBound(vfilename))
Application.Workbooks.Open Filename:=sImportFile
Set wbBk = Workbooks(sFile)
With wbBk
If SheetExists("def tracker") Then
Set wsSht = .Sheets("def tracker")
wsSht.Copy before:=sThisBk.Sheets("SharePoint")
Else
MsgBox "There is no sheet with name :def tracker in:" & vbCr & .Name
End If
wbBk.Close SaveChanges:=False
End With
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Private Function SheetExists(sWSName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(sWSName)
If Not ws Is Nothing Then SheetExists = True
End Function
I'm currently working on creating a ‘Master Reporting Tracker’ which will link to a bundle of excel worksheets which will be saved into the same folder/directory. I’m trying to link it between a few worksheets from different workbooks; linking them previously created a massive delay when trying to update the information as there were a bundle of formulas involved. So the approach I decided to use VBA to help solve this issue. First I added an Update File Sheet in my ‘Master Reporting Tracker’ which will have buttons to Insert the worksheet or update the work sheet depending on the situation (I couldn’t work out the update worksheet) I created modules and added in buttons in the ‘update files’ worksheet so I could use the button to add the Worksheet into the Workbook; I wasn’t able to create an update file script as my VBA skills aren’t that good and I couldn’t find anything online. The reason I cant use import file every time is it ruins my formulas which are present in my reporting sheets that are also present in the tracker…I don’t want to keep amending the formulas so I thought I’d take this approach.
Also is there any way I could add a weekly report feature to go through the SharePoint worksheet (Column U) and search for 2 user defined dates; I tried searching for something that would give a pop up when you select the module and ask you to enter two dates (Start and End) and it would copy all the information that falls in the criteria onto ‘Weekly Report’ adding 3 x Columns from B (B being ‘BU’, C being ‘Analysis’, D being ‘Reason’) with a formula such as:
IF A1 in ‘Weekly Report’ is present in A2 column ‘Def Tracker’ then copy same row AA and AB & paste it in Weekly Tracker ‘B2’ and ‘C3’. Whenever the script runs it leaves the worksheet as is but copies new Data that falls under the criteria
Worksheets I have are:
· Update Files – Sheet that contains buttons linking to the modules (scripts). Planning to use this as a main sheet with multiple buttons/modules.
· Master Tracker – Worksheet from a separate workbook (needs to have a feature to IMPORT/UPDATE without creating a new Worksheet)
· Def Tracker - Worksheet from a separate workbook (needs to have a feature to IMPORT/UPDATE without creating a new Worksheet)
· ConS Report - Worksheet from a separate workbook (needs to have a feature to IMPORT/UPDATE without creating a new Worksheet)
· SharePoint (.CSV)- Worksheet from a separate workbook (needs to have a feature to IMPORT/UPDATE without creating a new Worksheet)
· Weekly Report – To be generated from Update File once above criteria matches.
· Some pivot table sheets (which will be created later)
Please note worksheets that are being import contain 10000+ entities which are growing every day. this is what I've got so far; but it doesn't help updating the data as it always creates a new sheet. help would be much appreciated.
Sub ImportDefect()
Dim sImportFile As String, sFile As String
Dim sThisBk As Workbook
Dim vfilename As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sThisBk = ActiveWorkbook
sImportFile = Application.GetOpenFilename( _
FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")
If sImportFile = "False" Then
MsgBox "No File Selected!"
Exit Sub
Else
vfilename = Split(sImportFile, "\")
sFile = vfilename(UBound(vfilename))
Application.Workbooks.Open Filename:=sImportFile
Set wbBk = Workbooks(sFile)
With wbBk
If SheetExists("def tracker") Then
Set wsSht = .Sheets("def tracker")
wsSht.Copy before:=sThisBk.Sheets("SharePoint")
Else
MsgBox "There is no sheet with name :def tracker in:" & vbCr & .Name
End If
wbBk.Close SaveChanges:=False
End With
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Private Function SheetExists(sWSName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(sWSName)
If Not ws Is Nothing Then SheetExists = True
End Function