Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to create a poor man's MS Project file without having MS Project. This is using conditional formatting to create the gantt chart.

    For starters, my multiple conditions don't seem to be working N8:FD8 and N10:FD10. I am trying to have one of the conditions be reflected if it matches today's date in $A$4.

    My second effort is to create a macros that will create as export as reflected in the Export_for_Dashboard worksheet, but as a separate Workbook that opens.

    I have used the VBA and Macros for Microsoft Excel as a reference to piece together some of the VBA that would be necessary, but it is more or less a hodgepodge of code that needs to be put together properly. It is in the Module1. Very ugly.

    Thirdly, I am trying to begin the calendar of the Project Start Date in B3. Would the WEEKDAY function somehow be appropriate so that the value of 7 cell merge N6 match the weeks of the calendar correctly?

    Any and all help would be greatly appreciated. The formulas are the least of my worries, since I'm sure to find the right tweeking with further research, but the code is beyond me.

    Thanks
    Amy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    For the last "used" row in column B, you can use

    Dim m As Long
    m = wsSheet.Cells(wsSheet.Rows.Count, 2).End(xlUp).Row

    You can then change

    Set rnData = .Range("B6:B134", "C6:C134", ...

    to

    Set rnData = .Range("B6:B" & m, "C6:C" & m, ...

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    About the conditional formatting: the first condition is

    =AF$5=$A$4

    This should be

    =AF$5=$B$4

    of course, since B4 contains today's date, not A4.

  4. #4
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    Hans,
    Thank you for pointing out the bonehead reference on the conditional formatting. It's always the obvious first

    As I am piecing this code together more with your suggestion, please confirm my logic

    Sub Export_For_Dashboard()

    Dim wdApp As Excel.Application
    Dim wkBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnData As Range
    Dim vaData As Variant
    Dim m As Long

    m = wsSheet.Cells(wsSheet.Rows.Count, 2).End(xlUp).Row

    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Project_Plan")

    'identifies and copies the records in the "Project_Plan" worksheet
    With wsSheet
    Set rnData = .Range("B6:B" & m, "C6:C" & m, "D6" & m, "F6:F" & m, "H6:H" & m, "I6:I" & m, "J6:J" & m, "K6:K" & m)
    End With


    'opens new workbook instance and names the file "Project_today's date".xls
    Workbook.Open
    Worksheets.Activate
    strSaveName = "Project_" & Format(Now, "yyyy-mm-dd") & ".xls"

    'paste the identified range from "Project_Plan" worksheet to Sheet1 of new opened workbook
    Range("B6:B" & m, "C6:C" & m, "D6" & m, "F6:F" & m, "H6:H" & m, "I6:I" & m, "J6:J" & m, "K6:K" & m).PasteSpecial xlPasteValues & xlPasteNumberFormat

    'clean up records by deleting all exported records that have no value

    'Rest the clipboard
    With Application
    .CutCopyMode = False
    .ScreenUpdating = False
    End With

    End Sub


    I'll get there. What logical steps am I missing? I recognize some compile errors at this point too

    Thanks for your help.
    Amy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does this help?

    Code:
    Sub Export_For_Dashboard()
      Dim wkBook As Workbook
      Dim wsSheet As Worksheet
      Dim rnData As Range
      Dim m As Long
      Dim wkNew As Workbook
      Dim wsNew As Worksheet
      Dim strSaveName As String
    
      Set wkBook = ThisWorkbook
      Set wsSheet = wkBook.Worksheets("Project_Plan")
    
      ' Identify the records in the "Project_Plan" worksheet
      With wsSheet
    	m = .Cells(.Rows.Count, 2).End(xlUp).Row
    	Set rnData = .Range("B6:B" & m & ",C6:C" & m & ",D6:D" & m & _
    	",F6:F" & m & ",H6:H" & m & ",I6:I" & m & ",J6:J" & m & ",K6:K" & m)
      End With
    
      ' Open new workbook instance
      Set wkNew = Workbooks.Add(xlWBATWorksheet)
      Set wsNew = wkNew.Worksheets(1)
    
      ' Paste the identified range from "Project_Plan" worksheet to new workbook
      rnData.Copy
      wsNew.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
      ' Save new workbook as Project_today's date.xls
      strSaveName = "Project_" & Format(Now, "yyyy-mm-dd") & ".xls"
      wkNew.SaveAs strSaveName
    
      ' Clean up records by deleting all exported records that have no value
      ' rnData.ClearContents
    
      ' Reset the clipboard
      With Application
    	.CutCopyMode = False
    	.ScreenUpdating = True
      End With
    End Sub

  6. #6
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='775178' date='14-May-2009 15:00']Does this help?

    Code:
    Sub Export_For_Dashboard()
      Dim wkBook As Workbook
      Dim wsSheet As Worksheet
      Dim rnData As Range
      Dim m As Long
      Dim wkNew As Workbook
      Dim wsNew As Worksheet
      Dim strSaveName As String
    
      Set wkBook = ThisWorkbook
      Set wsSheet = wkBook.Worksheets("Project_Plan")
    
      ' Identify the records in the "Project_Plan" worksheet
      With wsSheet
    	m = .Cells(.Rows.Count, 2).End(xlUp).Row
    	Set rnData = .Range("B6:B" & m & ",C6:C" & m & ",D6:D" & m & _
    	",F6:F" & m & ",H6:H" & m & ",I6:I" & m & ",J6:J" & m & ",K6:K" & m)
      End With
    
      ' Open new workbook instance
      Set wkNew = Workbooks.Add(xlWBATWorksheet)
      Set wsNew = wkNew.Worksheets(1)
    
      ' Paste the identified range from "Project_Plan" worksheet to new workbook
      rnData.Copy
      wsNew.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
      ' Save new workbook as Project_today's date.xls
      strSaveName = "Project_" & Format(Now, "yyyy-mm-dd") & ".xls"
      wkNew.SaveAs strSaveName
    
      ' Clean up records by deleting all exported records that have no value
      ' rnData.ClearContents
    
      ' Reset the clipboard
      With Application
    	.CutCopyMode = False
    	.ScreenUpdating = True
      End With
    End Sub
    [/quote]
    Hans,
    Thank you. That got the export function working. It seems the default save is to My Documents, is that correct?

    Here is the next steps. Cleaning up the export worksheet by deleting records that were exported that have no value for 'WBS'

    I started with:

    ' Clean up records by deleting all exported records that have no value
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Sheet1")

    Application.ScreenUpdating = False

    j = 100

    With wsSheet

    For Each cell In Range("A2:A" & FinalRow).SpecialCells(xCellTypeVisible)
    Ctr = Ctr + 1
    Next cell
    If IsEmpty(.Cells(i, 1)) Then
    .Cells(i, 1).EntireRow.Delete
    End If
    Next i
    End With

    Application.ScreenUpdating = True


    This is obviously not working well. Any help with it is appreciated.

    I will try to string together changing the header names on the export next and send for confirmation and editing.

    Thanks
    Amy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you don't specify a path, the workbook will be saved in whatever is the "current" folder in Excel - probably the default workbook folder specified in the General tab of Tools | Options...
    If you want to save the workbook elsewhere, you can specify a path or ask the user to do so.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You're using completely unrelated items such as wsSheet, i, j , cell, FinalRow and Ctr. You can't just throw together some random lines of VBA and expect it to work.

    Try this:
    Code:
    Dim r As Long
    Dim m As Long
    
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = m To 1 Step -1
      If Cells(r, 1) = "" Then
    	Cells(r, 1).EntireRow.Delete
      End If
    Next r

  9. #9
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation

    My apologies for using completely unrelated items such as wsSheet, i, j , cell, FinalRow and Ctr. I'll try not to throw together some random lines of VBA and expect it to work. Please forgive me. I am merely a novice trying to slug through reference material and seeking out wisdom and correction.

    The code you provided worked out fine. I was able to put the new headers in place with the following code:

    ' Copy new headers from "LOOKUP_Table"
    Set wkBook = ThisWorkbook
    Set wsSheet = wkBook.Worksheets("LOOKUP_Table")

    With wsSheet
    m = .Cells(.Rows.Count, 2).End(xlUp).Row
    Set rnData = .Range("B2:B9")
    End With

    ' Paste the identified range from "LOOKUP_Table" worksheet to new workbook
    rnData.Copy
    wsNew.Range("B1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True


    How would I change the Export workbook worksheet name from its default (Sheet1) to "Task_Table1" like an MS Project file default? That seems to be final problem that is preventing a smooth import into my web-enabled application.

    Thanks,
    Amy

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use

    wsNew.Name = "Task_Table1"

    You must do this below the line

    Set wsNew = ...

    but above the line that saves wkNew.

  11. #11
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='AmyN' post='775237' date='14-May-2009 20:18']My apologies for using completely unrelated items such as wsSheet, i, j , cell, FinalRow and Ctr. I'll try not to throw together some random lines of VBA and expect it to work. Please forgive me. I am merely a novice trying to slug through reference material and seeking out wisdom and correction.

    The code you provided worked out fine. I was able to put the new headers in place with the following code:

    ' Copy new headers from "LOOKUP_Table"
    Set wkBook = ThisWorkbook
    Set wsSheet = wkBook.Worksheets("LOOKUP_Table")

    With wsSheet
    m = .Cells(.Rows.Count, 2).End(xlUp).Row
    Set rnData = .Range("B2:B9")
    End With

    ' Paste the identified range from "LOOKUP_Table" worksheet to new workbook
    rnData.Copy
    wsNew.Range("B1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True


    How would I change the Export workbook worksheet name from its default (Sheet1) to "Task_Table1" like an MS Project file default? That seems to be final problem that is preventing a smooth import into my web-enabled application.

    Thanks,
    Amy[/quote]
    I figured out the answer to my own question

    ActiveSheet.Name = "Task_Table1"

    I am also trying to round the pasted value in column I. I am selecting the column and copying and pasting, but I don't know the Round code:

    I need to identify the column I values of the export workbook (I2:I), copy and paste it over itself after rounding the value to 0 decimal places.

    Any help would be appreciated.

    Amy

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of copying and pasting, loop through the cells and round each value in turn using the Round function:

    oCell.Value = Round(oCell.Value, 0)

  13. #13
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is what I attempted, but failed:

    Dim oCell As Range

    'Loop to round cell values of any cell that has a value in range I2:I
    For Each oCell In Range("I2:I")
    If oCell.Value > 0 Then
    oCell.Value = Round(oCell.Value, 0)
    End If
    Next

    That is causing an error. What have I done wrong?

    Amy

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    For Each oCell In Range("I2:I")

    does not list the last row of the range.

    It should be something like (if you want to do it for 10 rows, change the last row as desired)

    For Each oCell In Range("I2:I10")


    Steve

  15. #15
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='775380' date='15-May-2009 17:28']For Each oCell In Range("I2:I")

    does not list the last row of the range.

    It should be something like (if you want to do it for 10 rows, change the last row as desired)

    For Each oCell In Range("I2:I10")


    Steve[/quote]
    Steve,
    Thanks. I don't want to limit the loop to I2:I10. I would like to loop down the column for a records that have a value in that column starting below the header in Row 1. I thought "I2:I" would affect. How should it be written?

    Amy

Page 1 of 2 12 LastLast

Posting Permissions

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