Creating a master tracker to copy worksheets and perform tasks

[Padwan]

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

[westconn1]

With wbBk .
If SheetExists("def tracker") Then this checks for sheet def tracker in the active workbook, as there is no provision in the sheet exists function to specify which workbook to test for an existing sheet

probably better to try like

With wbBk
for each wssht in .sheets
if wssht.name = "def tracker" then exit for
next
if not wssht is nothing then
wsSht.Copy before:=sThisBk.Sheets("SharePoint")
Else
MsgBox "There is no sheet with name :def tracker in:" & vbCr & .Name
End If

[Padwan]

Hi Westconn1,

thanks for your response; i have found a way to import sheets; but im currently trying to work out a script to copy/update information into an existing sheet (re-copy all the details again without creating a new sheet).

Also unsure if you've read my complete post; im also trying to find a way to create a weekly report....where it would display a pop up and ask my to enter Start Date a end date -> and it would search through 'sharepoint' as mentioned above in my post.

*Screenshot attached to show plan
12049

[westconn1]

if you want to copy all the data on a sheet to a position on an existing sheet, you need to copy the usedrange of sheet to the target cell on existing sheet
something like

wsSht.usedrange.Copy sThisBk.Sheets("SharePoint").range("f7")where f7 is the target position, change to suit


Also unsure if you've read my complete post; im also trying to find a way to create a weekly report....where it would display a pop up and ask my to enter Start Date a end date -> and it would search through 'sharepoint' as mentioned above in my post.there was not really enough information given in the initial post to respond to
considering the size of the data it may be an occasion to use ADO to make an SQL querty on the data by dates, to return an appropriate recordset, which can be placed in a separate worksheet or whatever the desired result is

[Padwan]

Hi Westconn1,

Thanks for your response; but my VBA skills aren't top notch; where exactly would i place that code? as you can see from my screenshot...I'm trying to work on creating multiple functions under the buttons. Would it be possible for you to advise for a script i could use for "Update SharePoint for example" Files are saved "C:\Users\Apple.Berry\Desktop\Defect Tracker"

as for the second bit; would it be possible to advise what information is required?

I've attached an Example of 'SharePoint'

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; 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

Thanks,

[westconn1]

IF A1 in ‘Weekly Report’ is present in A2 column ‘Def Tracker’you want to loop through all the cells of a column of one of the worksheets? which sheet? is A2 a column?

& paste it in Weekly Tracker ‘B2’ and ‘C3’not the same row?


I've attached an Example of 'SharePoint'

(B being ‘BU’, C being ‘Analysis’, D being ‘Reason’are these column headers of sharepoint sheet, that i do not see?

[Padwan]

Hi Westconn1.

What I'm looking to do is :

Run a script by clicking 'Create Weekly Report'

The should now check if Worksheet named SharePoint exists if not display "SharePoint doesn't exist" And If "Weekly Report" exists proceed if not create a worksheet called Weekly report.

The next thing I'm hoping to do is to be able to get a pop up to requesting for 2 dates to be put in (Start Date / End Date)

Now whatever in the 'SharePoint' worksheet that comes within this criteria to be copied into "Weekly Report" (This includes all the headings from 'SharePoint')

Once the weekly report is completely populated; create/add 3 new columns (B being ‘BU’, C being ‘Analysis’, D being ‘Reason’) - these columns don't exist in the SharePoint list and will need to be created in the weekly report.

Then would come the formula to go through the Weekly report; if A2 in Weekly Tracker is present within Column A of the Defect Tracker then copy same row (AA) and (AB) & paste it in Weekly Tracker ‘B2’ and ‘C2’. Go through each one by one.

For an example purpose; I've updated the excel sheet previously attached with 3 worksheets. Please note as this is an example the data present is just random details.

I only added one example but in reality ill have more than 1000's of entries12063

[westconn1]

The should now check if Worksheet named SharePoint exists if not display "SharePoint doesn't exist" And If "Weekly Report" exists proceed if not create a worksheet called Weekly report.
use code like in post #2, if not found then for sharepoint msgbox :exit sub, for weekly report set shtwr = sheets.add : shtwr.name = "weekly report"
if you are going to test for existing sheets a lot then put code in separate function, as post #1, but pass the workbook as well as the sheet name, so it is not the active workbook that is searched


get a pop up to requesting for 2 dates to be put in (Start Date / End Date)
you could use 1 or 2 inputboxes for user to input dates, but better to use a userform with 2 date controls, so user can select valid dates and ok button


Now whatever in the 'SharePoint' worksheet that comes within this criteria to be copied into "Weekly Report" (This includes all the headings from 'SharePoint')if any records found, copy headers first, then records below
how many rows/ columns in sharepoint?


Once the weekly report is completely populated; create/add 3 new columns (B being ‘BU’, C being ‘Analysis’, D being ‘Reason’) - these columns don't exist in the SharePoint list and will need to be created in the weekly report.sheets("sharepoint").columns("b:d").insert, then put column names, qualify with workbook if multiple workbooks are open

i will try to look at the data later

[Padwan]

Would it be possible to get some help on the above ?

[westconn1]

have you made any attempt to code this?
are you doing this for your employment, paid job, and expect all to be done for you?
no one here is paid for writing your code
people here will give all assistance for you to do, or may do some or all of the code when they have time

maybe i will look again tonight

[westconn1]

i looked at your sample workbook, but there is not enough data to make a test

while the data does not have to be in anyway real, it needs to have enough to test getting some (not all) from within a date range etc, something like 50 to 100 rows in sharepoint
also suggest valid dates from the sample data for testing weekly report