I am a VBA beginner and very eager to learn this stuff. What a great forum!
I have a Worksheet named "Global Production Schedule" in Workbook1 that has
several rows with Sales Order numbers Column A and there ship dates in Column
K. When the order is shipped the user highlights the ship date cell yellow.
At the end of the day I want the user to be able to click a button, then the
highlighted cell rows are cut and then pasted into Sheets("sheet1") Workbook2
named "Archive". The code I have is not working and I'm not sure why. Feel
free to change anything, because this code could be seriously incorrect.
NOTE: The button to call this macro is in a custom toolbar I made and the
macro is located in my personal workbook because the global schedule file
name changes everyday. Here is what I have so far:

Sub SendToArchive()

Dim LastRow As Long, InsertRow As Long, FinalRow As Long
Dim wsArchive As Worksheet, wsGlobal As Worksheet
Dim wbArchive As Workbook, wbGlobal As Workbook
Dim fName As Variant

'Current worksheet with highlighted cells
wsGlobal = ThisWorkbook.Sheets("Global Production Schedule")

'Message to prompt user to select the Archive file
Msg1 = MsgBox("Choose the Archive file you want the selected Sales Orders to
go too.")

'User selected Archive File name
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls")

If fName1 = False Then
Exit Sub
Else
Set wbArchive = Workbooks.Open(fName)
End If

Msg2 = MsgBox("Are you sure you want to move highlighted Sales Orders from "
& NAME OF GLOBAL WORKBOOK & " to " & NAME OF ARCHIVE WORKBOOK & " ?",
vbOKCancel)
If Msg1 < vbOK Then Exit Sub

Set wsArchive = wbArchive.Sheets("Archive")
LastRow = wsArchive.Cells(Rows.Count, "A").End(xlUp).Row
InsertRow = LastRow + 1
FinalRow = wsGlobal.Cells(Rows.Count, "A").End(xlUp).Row

For i = 3 To FinalRow
If Cells(i, 11).Interior.ColorIndex = 6 Then 'Yellow Cells
Cells(i, 11).EntireRow.Cut Destination:=wsArchive.Rows(InsertRow)
End If
Next i

Windows(fName).Close SaveChanges:=Save

End Sub

Just took a quick glance at the code. Comments below.

1.
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls")

Looks like a typo.

2.
Msg2 = MsgBox("Are you sure you want to move highlighted Sales Orders from "
& NAME OF GLOBAL WORKBOOK & " to " & NAME OF ARCHIVE WORKBOOK & " ?",
vbOKCancel)
If Msg1 < vbOK Then Exit Sub

Should this not be If Msg2 < vbOK Then...?

3.
Windows(fName).Close SaveChanges:=Save

I would use:

Workbooks(fName).Close SaveChanges:=True

"RyanH" wrote:

I am a VBA beginner and very eager to learn this stuff. What a great forum!
I have a Worksheet named "Global Production Schedule" in Workbook1 that has
several rows with Sales Order numbers Column A and there ship dates in Column
K. When the order is shipped the user highlights the ship date cell yellow.
At the end of the day I want the user to be able to click a button, then the
highlighted cell rows are cut and then pasted into Sheets("sheet1") Workbook2
named "Archive". The code I have is not working and I'm not sure why. Feel
free to change anything, because this code could be seriously incorrect.
NOTE: The button to call this macro is in a custom toolbar I made and the
macro is located in my personal workbook because the global schedule file
name changes everyday. Here is what I have so far:

Sub SendToArchive()

Dim LastRow As Long, InsertRow As Long, FinalRow As Long
Dim wsArchive As Worksheet, wsGlobal As Worksheet
Dim wbArchive As Workbook, wbGlobal As Workbook
Dim fName As Variant

'Current worksheet with highlighted cells
wsGlobal = ThisWorkbook.Sheets("Global Production Schedule")

'Message to prompt user to select the Archive file
Msg1 = MsgBox("Choose the Archive file you want the selected Sales Orders to
go too.")

'User selected Archive File name
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls")

If fName1 = False Then
Exit Sub
Else
Set wbArchive = Workbooks.Open(fName)
End If

Msg2 = MsgBox("Are you sure you want to move highlighted Sales Orders from "
& NAME OF GLOBAL WORKBOOK & " to " & NAME OF ARCHIVE WORKBOOK & " ?",
vbOKCancel)
If Msg1 < vbOK Then Exit Sub

Set wsArchive = wbArchive.Sheets("Archive")
LastRow = wsArchive.Cells(Rows.Count, "A").End(xlUp).Row
InsertRow = LastRow + 1
FinalRow = wsGlobal.Cells(Rows.Count, "A").End(xlUp).Row

For i = 3 To FinalRow
If Cells(i, 11).Interior.ColorIndex = 6 Then 'Yellow Cells
Cells(i, 11).EntireRow.Cut Destination:=wsArchive.Rows(InsertRow)
End If
Next i

Windows(fName).Close SaveChanges:=Save

End Sub