Results 1 to 9 of 9

Thread: Very inexperienced self taught VBA in excel user needs help... PLEASE.

Hybrid View

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    3

    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

    Please help.
    Ta

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Very inexperienced self taught VBA in excel user needs help... PLEASE.

    this is the vb.net forum. I've notified a moderator who will move your question to Office Development

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Very inexperienced self taught VBA in excel user needs help... PLEASE.

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

    (thanks for letting us know .paul. )

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    3

    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

    'define variables
    Set inputbk = ActiveWorkbook
    Set targgrp = Sheets("Lookup").Cells(2, 12)
    targdat = Sheets("lookup").Cells(1, 7)
    repmth = Month(targdat)
    repyr = Year(targdat)
    mthdays = Sheets("lookup").Cells(2, 7) - Sheets("lookup").Cells(1, 7) + 1

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

    'fill sheet in------

    mansheet.Cells(3, 1) = MonthName(repmth)
    mansheet.Cells(3, 6) = targnum
    mansheet.Cells(3, 7) = lookupcell.Offset(0, -3)
    mansheet.Cells(3, 8) = lookupcell.Offset(0, -1)
    mansheet.Cells(3, 9) = targgrp.Offset(0, 1)


    Set visitnum = Sheets("Visits").Cells(2, 8)
    Set dropcell = mansheet.Cells(6, 1)
    Do Until visitnum = 0
    If targnum = visitnum Then
    If Month(visitnum.Offset(0, 2)) = repmth And Year(visitnum.Offset(0, 2)) = repyr Then
    dropcell = visitnum.Offset(0, 2) ' Labour Costing date
    dropcell.Offset(0, 1) = visitnum.Offset(0, -7) ' WO No
    dropcell.Offset(0, 2) = visitnum.Offset(0, -6) ' Raised date
    dropcell.Offset(0, 7) = visitnum.Offset(0, -1) ' completion code
    dropcell.Offset(0, 9) = visitnum.Offset(0, 3) ' completion comments
    dropcell.Offset(0, 10) = visitnum.Offset(0, -4) ' Asset Area
    dropcell.Offset(0, 11) = visitnum.Offset(0, -3) ' Work Group
    dropcell.Offset(0, 12) = visitnum.Offset(0, 5) ' Normal time
    dropcell.Offset(0, 13) = visitnum.Offset(0, 6) ' Overtime
    dropcell.Offset(0, 14) = visitnum.Offset(0, 7) ' Travel time
    dropcell.Offset(0, 15) = visitnum.Offset(0, 8) ' Total Hours
    dropcell.Offset(0, 16) = visitnum.Offset(0, 39) ' Normal TT
    dropcell.Offset(0, 17) = visitnum.Offset(0, 40) ' Norm & Norm TT
    dropcell.Offset(0, 18) = dropcell.Offset(0, 15) - dropcell.Offset(0, 17) ' OT & OT TT

    visitwono = dropcell.Offset(0, 1)
    Set wocell = Sheets("Workorders").Cells(4, 1)
    Do Until wocell = visitwono
    Set wocell = wocell.Offset(1, 0)
    Loop

    dropcell.Offset(0, 3) = wocell.Offset(0, 12) ' Site Name
    dropcell.Offset(0, 4) = wocell.Offset(0, 13) ' Asset Code
    dropcell.Offset(0, 6) = wocell.Offset(0, 14) ' Job Description
    dropcell.Offset(0, 8) = wocell.Offset(0, 15) ' Comp Comments
    dropcell.Offset(0, 5) = wocell.Offset(0, 16) ' Asset Description

    Range("A6:S6").Copy
    dropcell.Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Set dropcell = dropcell.Offset(1, 0)


    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

    'create charts
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollRow = 1
    shtname = ActiveSheet.Name
    Set chtrng = Range(daytabcell.Offset(-1, 0), daytabcell.Offset(mthdays - 1, 4))
    Charts.Add
    ActiveChart.ChartType = xlColumnStacked
    ActiveChart.SetSourceData Source:=chtrng, _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:=shtname
    With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = shtname & " Hours 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

    Cells(1, 1).Select
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollRow = 12

    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

    inputbk.Save
    End Sub

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    3

    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.

    Cheers,
    john
    Attached Files Attached Files

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Very inexperienced self taught VBA in excel user needs help... PLEASE.

    I don't know what a code tag is
    nothing to do with VBA, a feature of this site [code], to overcome html issues with indenting etc, closing tag has a [/ code], no space

    'name sheet 1
    Sheets(1).Name = targgrp & " - " & targgrp.Offset(0, 1)

    inputbk.Activate
    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

  9. #9
    New Member
    Join Date
    Aug 2012
    Location
    Philly. Yo!
    Posts
    10

    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.

    Good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width