Macro Programing Help

Try using a variable instead of hard-coding the spreadsheet name. For example:
Sub YourCode()
Dim FileName As String
'your code
FileName=ActiveWindow.Name
'more of your code
Windows(FileName).Activate
End Sub

Regards,
Barrie

Barrie, will something like this work for sheets also?

I had a similar question, but in regards to sheets and sheet names. Please see previous post 27168.

Thanks

Barrie, will something like this work for sheets also?

I had a similar question, but in regards to sheets and sheet names. Please see previous post 27168.

Thanks

Here is the entire code. I wasn't quite sure what you were trying to tell me. I tried doing what you said but it didn't work. Its the following line that doesn't work:

Windows("Quote Package.xls").Activate

So how do I incorporate what you said above into the code below?

Sub Summary()

Application.ScreenUpdating = False
Workbooks.Open FileName:="P:\Projects\Jones\Logs\quotelog.xls"
Rows("5:5").Select
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveWindow.WindowState = xlMinimized

Windows("Quote Package.xls").Activate

ActiveWindow.WindowState = xlMaximized
ActiveWindow.LargeScroll Down:=1
Range("E50:J50").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("quotelog.xls").Activate
ActiveWindow.WindowState = xlMaximized
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.LargeScroll Down:=-1
Range("C22:F22").Select

End Sub

Windows("Quote Package.xls").Activate ActiveWindow.WindowState = xlMaximized ActiveWindow.LargeScroll Down:=1 Range("E50:J50").Select Windows("quotelog.xls").Activate ActiveWindow.WindowState = xlMaximized Range("A5").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.Close ActiveWindow.LargeScroll Down:=-1 Range("C22:F22").Select End Sub

I now have a better understanding of your problem (sorry, I thought you wanted the macro to access the file that it resides in, "quotelog.xls" in this case). You could try this (which will assume you have only two files open, "quotelog.xls" and the other file, whatever you name it).

Sub Summary()
Dim FileName1 As String
Dim FileName2 As String

If Application.Windows.Count <> 2 Then
MsgBox prompt:="There can only be 2 files are open", _
Buttons:=vbCritical
Exit Sub
End If
Application.ScreenUpdating = False
Workbooks.Open FileName:="P:\Projects\Jones\Logs\quotelog.xls"
FileName1 = ActiveWorkbook.Name
Rows("5:5").Select
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveWindow.ActivateNext
FileName2 = ActiveWorkbook.Name
Range("E50:J50").Select
Selection.Copy
Windows(FileName1).Activate
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Range("C22:F22").Select

End Sub

Let me know if it works for you.

Regards,
Barrie

Will try to answer tomorrow.........

Thanks

Rob, I'm just heading out now so I'll take a look at it tomorrow and see if I can figure it out for you.

Regards,
Barrie

Why not just reference it using the Thisworkbook
This way you can have any number of books open.

eg Using your original code; BUT

Instead of
Windows("Quote Package.xls").Activate

use
Thisworkbook.Activate

Just use Thisworkbook where ever you want to
reference the workbook that has the code.

Ivan