Ok, so this is my first attempt at an excel macro. I'm trying to develop a macro that will assist me in auto-populating a column in a new worksheet based on another worksheet. Recently I ran a new report at work that is 4000 lines long that I really do not want to have to edit by hand, since I already had to do it with the older one.
I can't post my data but i'll try to explain it the best I can, but basically it's set up as a part number, next column is the steps in completing that part, and then a workcenter number. I'm trying to populate that workcenter column in the new worksheet. I've put this macro together with the best of my ability along with excel help and the internet but I just can't seem to get it to work.
Sub DataPopulate()
Dim varOperNo As Variant
Dim varPartNumber As Variant
Dim x As Integer
Dim SFCTimesNew As Worksheet
Dim SFCTimesOld As Worksheet
Dim pnfind As Range
For x = 2 To 3965
Cells(x, 12).Select 'cycles through cells in New Work Center column
ActiveCell.Offset(0, -6).Select 'select corresponding operation number
varOperNo = ActiveCell.Value 'saves value of oper no
Selection.Offset(0, -5).Select
Do Until Selection.Value <> ""
If Selection.Value = "" Then
Selection.Offset(0, 1).Select 'loops until part number exists
Else: varPartNumber = ActiveCell.Value
End If
Loop
Sheets("SFC times by plan").Activate
Sheets("SFC times by plan").Select 'Open 2nd worksheet
Range("A1").Select 'looks for saved part number
Set pnfind = Cells.Find(What:=varPartNumber, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not pnfind Is Nothing Then
MsgBox "The part number" & varPartNumber & "does not exist"
Else
pnfind.Select
ActiveCell.Offset(4, 0).Select
End If
Do Until Selection.Value = varOperNo 'looks for oper no saved form before
Selection.Offset(0, -1).Select
Loop
ActiveCell.Offset(6, 0).Select 'moves to new work center number and
'copies cell value
Selection.Copy _
Destination:=SFCTimesNew(x, 12)
Next x
Application.CutCopyMode = False
End Sub
When I step through it it seems to fail at the line Range("A1").Select and I'm not sure why, any advice would be appreciated, or if my code is completely wrong and tips to point me in the right direction would be appreciated.
Thanks in advance,
J
Bookmarks