Very inexperienced self taught VBA in excel user needs help... PLEASE.
I've written a macro in excel 2003 which looks down a list of workgroups, finds the first and then creates a new workbook. It then looks down another list of people referenced to workgroups and, when it finds one from the right workgroup, it copies an existing template from the first workbook, fills it with a report about that person (from data in the first workbook) and then moves it to the new workbook for that workgroup. Once it has found all the people in one workgroup it saves (as a predefined name) and closes the workbook and moves to the next workgroup. There are 8 workgroups and around 90 people.
It all seems to work swimmingly which, as a self taught amateur, I am well chuffed about. The only thing is that after a while the move sheet operation stops working and you just get an error. If I open up excel and then run it, it does around 20 sheets. If I run it again It only does two. If I close it down and run it again it does 20 again.This leads me to believe that there is a Cache somewhere which I am filling up and a) I don't know where it is and b) I want to be able to clear it.
Obviously I can always run the report for 1 group at a time (which works by the way) but I would feel beaten and very depressed
Re: Very inexperienced self taught VBA in excel user needs help... PLEASE.
it maybe you are getting too many workbooks open and running out of resources
without seeing your code, hard to take a guess
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Very inexperienced self taught VBA in excel user needs help... PLEASE.
I am creating workbooks and adding sheets to them but then I close them before I start the next one. I was wondering if they were being cached somewhere and if that Cache could be cleared somehow after closing the workbook.
Thanks.
I've included the code below.
Sub createreports1()
Dim targgrp As Range, targdat As Long, targnum As Long
Dim lookupcell As Range
Dim inputbk As Workbook
Dim outputbk As Workbook
Dim repmth As Integer, repyr As Integer
Dim mansheet As Worksheet
Dim visitnum As Range, visitwono As String, wocell As Range
Dim dropcell As Range, daytabcell As Range, lookdaycell As Range
Dim mthdays As Integer
Dim shtname As String
Dim chtrng As Range
'turn off calc before save and make sure autocalc is off
With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
End With
ActiveWorkbook.PrecisionAsDisplayed = False
'find group name and create workbook for group and save
Do Until targgrp = 0
Set outputbk = Workbooks.Add
With outputbk
.SaveAs (inputbk.Path & "/" & targgrp & " " & MonthName(repmth) & " Group Monthly Report.xls")
End With
'name sheet 1
Sheets(1).Name = targgrp & " - " & targgrp.Offset(0, 1)
inputbk.Activate
Set lookupcell = Sheets("lookup").Cells(2, 4)
Do Until lookupcell = 0
'create sheet in first workbook for each person in group
If lookupcell = targgrp Then
Sheets("Man template").Select
Sheets("Man template").Copy After:=Sheets(5)
Set mansheet = ActiveSheet
targnum = lookupcell.Offset(0, -2)
mansheet.Name = lookupcell.Offset(0, -3)
End If
End If
Set visitnum = visitnum.Offset(1, 0)
johnbags = visitnum.Row
Loop
Range(dropcell, dropcell.Offset(0, 18)).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
' fill daily table in.
Set daytabcell = Range("V6")
For i = 0 To mthdays - 1
Set lookdaycell = Range("A6")
Range(daytabcell, daytabcell.Offset(0, 8)).Copy
daytabcell.Offset(i, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
daytabcell.Offset(i, 0) = targdat + i
Do Until lookdaycell = 0
If daytabcell.Offset(i, 0) = lookdaycell Then
daytabcell.Offset(i, 1) = daytabcell.Offset(i, 1) + lookdaycell.Offset(0, 12) ' Normal Time
daytabcell.Offset(i, 2) = daytabcell.Offset(i, 2) + lookdaycell.Offset(0, 13) ' Overtime
daytabcell.Offset(i, 3) = daytabcell.Offset(i, 3) + lookdaycell.Offset(0, 14) ' Travel time
If lookdaycell.Offset(0, 9) = "I " Then daytabcell.Offset(i, 4) = daytabcell.Offset(i, 4) + lookdaycell.Offset(0, 15) ' Cyclic
If lookdaycell.Offset(0, 9) = "A " Then daytabcell.Offset(i, 5) = daytabcell.Offset(i, 5) + lookdaycell.Offset(0, 15) ' Admin
If lookdaycell.Offset(0, 9) = "M " Then daytabcell.Offset(i, 6) = daytabcell.Offset(i, 6) + lookdaycell.Offset(0, 15) ' Modification
If lookdaycell.Offset(0, 9) = "P " Then daytabcell.Offset(i, 7) = daytabcell.Offset(i, 7) + lookdaycell.Offset(0, 15) ' Proactive
If lookdaycell.Offset(0, 9) = "R " Then daytabcell.Offset(i, 8) = daytabcell.Offset(i, 8) + lookdaycell.Offset(0, 15) ' Reactive
End If
Set lookdaycell = lookdaycell.Offset(1, 0)
Loop
Range(daytabcell.Offset(mthdays, 0), daytabcell.Offset(mthdays, 8)).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Next i
Charts.Add
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Sheets(shtname).Range( _
"V5:V36,Z5:AD36"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=shtname
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Adam Work Type Breakdown"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hours"
End With
'move sheet to new workbook
mansheet.Move After:=outputbk.Sheets(outputbk.Sheets.Count)
inputbk.Activate
End If
Set lookupcell = lookupcell.Offset(1, 0)
Loop
'save and close workbook
outputbk.Save
outputbk.Close
Set targgrp = targgrp.Offset(1, 0)
Loop
'turn calc before save back on
With Application
.MaxChange = 0.001
.CalculateBeforeSave = True
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Re: Very inexperienced self taught VBA in excel user needs help... PLEASE.
i do not see anything obvious
there again with no code tags and formatting, it would be pretty hard to see anything
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Very inexperienced self taught VBA in excel user needs help... PLEASE.
Sorry,
I hadn't realised that the indents hadn't copied over the first time. I have attached it in a text file this time. I don't know what a code tag is I'm afraid. I've taught myself VBA from the help files, recording macros and a bit of online hunting. I have put some stuff at the end of the lines after apostrophes but they haven't copied over as green.
I'm not sure that there is too much wrong with the code. It seems to work. The only thing is that If I run the macro when I open Excel it will error out whilst doing the third workgroup (after around 30 worksheet copies and moves). If I try to run it again it goes during the first workgroup ( about 5 copies and moves). It always fails when it is trying to move the completed sheet to the second workbook. This would lead me to believe that something is caching all the stuff I am doing and filling up. It resets when I close Excel and reopen. I want to be able to reset it every time I finish one of the workbooks and close it which would allow the macro to complete all 8 reports without crashing.
2 things here which could cause issues, you do not specify which workbook contains sheets(1) if it is outputbk, you could move up into the with block, else specify the workbook object
you should as far as possible avoid selecting or activating anything, also avoid working with selection or other default objects, always use fully qualified ranges, (which mostly you already are doing)
neither of these may be causing your issue, but he only way to find out is to fix any undefined objects first, here is another example to fix
If lookupcell = targgrp Then
Sheets("Man template").Select
Sheets("Man template").Copy After:=Sheets(5)
Set mansheet = ActiveSheet
change to like
Code:
set mansheet = workbookonbject.sheets("Man template")
mansheet.copy after:=workbookpbject.sheets(5)
in both cases change workbookobject to appropriate workbook
don't make excel guess, sometimes it will get it right, other times not, especially when multiple books are opened and closed, references like these may hold books open, though not visible, these are common problems for running out of resources
Last edited by westconn1; Aug 19th, 2013 at 04:40 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Very inexperienced self taught VBA in excel user needs help... PLEASE.
Is it possible that you are not clearing the object references to these workbooks, within your loops? I see you save & close them. Try adding: set outputbk = nothing at the end of your loop
I also agree about never selecting or activating anything. Thats more of a user thing. or if you want to end the program at a certain worksheet & cell, for the users benefit. Just have the program scan through rows of data without selecting anything. All of the functions and settings still work on a proper range, without selecting or activating it.