Need Help: Updating dates monthly

[Parker]

I am new to all of this, and am just starting to learn my way around VBA. Please help me.

Where I work, I publish a workbook monthly that displays our equipment list and the dates that the last maintenance was performed on. We recently upgraded to a newer version of Maximo that will give me and excel spreadsheet with the equipment numbers and the dates that I need. I just need a simpler way of importing this data into my monthly workbook.

My spreadsheet that I get has the PMNUM and the completed dates in it, I get this information monthly, so I will only be inputting one month at a time. I need marches dates in the march column and lined up with their PMNUM row.

The PMNUM's are organized througout the whole workbook, so this is not limited to just this worksheet.

I cannot post links yet sorry.


David

[mdmackillop]

Hi David,
Welcome to VBAX.
You can post workbooks using Manage Attachments in the Go Advanced reply section. Remember to remove any sensitive data.
Regards
MD

[Parker]

workbook added.

Here is the monthly updated information that I receive that has to be imported in.



Thanks MD

[mdmackillop]

The book is empty and has a link to another file.

[Bob Phillips]

There is a problem wit that second file, it won't load.

[Parker]

fixed the attached documents, everything is viewable now

[Parker]

Any Ideas?

[mdmackillop]

[vba]
Option Explicit
Sub GetData()
Dim rng As Range
Dim Col As Long, Rw As Long
Dim txt As String
Dim i As Long, sh As Long
'get column to be filled
Col = 3 + 2 * Month(InputBox("Enter Month") & "/" & Format(Date, "yyyy"))
Set rng = Workbooks("MAXIMO_WorkOrder").Sheets(1).Cells(1, 1).CurrentRegion.Columns(2)
For i = 2 To rng.Cells.Count
For sh = 1 To 1
Rw = 0
On Error Resume Next
Rw = Application.Match(rng.Cells(i), Sheets(sh).Columns(29), 0)
If Rw <> 0 Then
Sheets(sh).Cells(Rw, Col) = rng.Cells(i).Offset(, -1)
Sheets(sh).Cells(Rw, Col).Interior.ColorIndex = 8
Exit For
End If
Next
For sh = 2 To 5
Rw = 0
On Error Resume Next
Rw = Application.Match(rng.Cells(i), Sheets(sh).Columns(29), 0)
If Rw <> 0 Then
Sheets(sh).Cells(Rw, Col) = "DONE"
Sheets(sh).Cells(Rw, Col).Interior.ColorIndex = 8
Exit For
End If
Next
Next
End Sub

[/vba]