PEV
08-19-2011, 10:17 AM
New guy here.
I'd like the macro to work from the remaned file as well. If the user "logs" the quote from the remaned file instead of the Master file (SSI), it's obviously not working
Sub LOGandSAVEAS()
Application.ScreenUpdating = False
Dim wbkFrom As Workbook
Dim shtFrom As Worksheet
Dim rngFrom As Range
Dim wbkTo As Workbook
Dim shtTo As Worksheet
Dim rngTo As Range
strFirstFile = ActiveWorkbook.Name
strSecondfile = network path to TESTLOG mentioned below
Set wbkFrom = Workbooks("SSI.xls") 'i'd like this to work from the renamed workbook that happens below
Set shtFrom = wbkFrom.Worksheets("QQ")
Set rngFrom = shtFrom.Range("A188:L188")
Range("A187:L187").Select
Selection.Copy
Range("A188").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Set wbk = Workbooks.Open(strSecondfile)
If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
LastRow = LastRow + 1
ThisWorkbook.Sheets("QQ").Range("A188:L188").Copy _
Destination:=Workbooks("TESTLOGforERIKdonotdelete.xls").Sheets(1).Cells(LastRow, 1)
Workbooks("TESTLOGforERIKdonotdelete.xls").Close savechanges:=True
MsgBox "Quote Logged. Thanks"
Dim Fname As String
Cancel = True
Application.DisplayAlerts = False
ChDir "C:\Documents and Settings\All Users\Desktop\"
Fname = Application.GetSaveAsFilename(ActiveSheet.Range("AA54").Value, fileFilter:="xls Files (*.xls), *.xls")
If Fname <> "False" Then
ActiveWorkbook.SaveAs Fname
Application.DisplayAlerts = True
MsgBox ("Copy Saved to your Desktop")
End If
Range("C4").Activate
Application.ScreenUpdating = True
End Sub
I'd like the macro to work from the remaned file as well. If the user "logs" the quote from the remaned file instead of the Master file (SSI), it's obviously not working
Sub LOGandSAVEAS()
Application.ScreenUpdating = False
Dim wbkFrom As Workbook
Dim shtFrom As Worksheet
Dim rngFrom As Range
Dim wbkTo As Workbook
Dim shtTo As Worksheet
Dim rngTo As Range
strFirstFile = ActiveWorkbook.Name
strSecondfile = network path to TESTLOG mentioned below
Set wbkFrom = Workbooks("SSI.xls") 'i'd like this to work from the renamed workbook that happens below
Set shtFrom = wbkFrom.Worksheets("QQ")
Set rngFrom = shtFrom.Range("A188:L188")
Range("A187:L187").Select
Selection.Copy
Range("A188").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Set wbk = Workbooks.Open(strSecondfile)
If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
LastRow = LastRow + 1
ThisWorkbook.Sheets("QQ").Range("A188:L188").Copy _
Destination:=Workbooks("TESTLOGforERIKdonotdelete.xls").Sheets(1).Cells(LastRow, 1)
Workbooks("TESTLOGforERIKdonotdelete.xls").Close savechanges:=True
MsgBox "Quote Logged. Thanks"
Dim Fname As String
Cancel = True
Application.DisplayAlerts = False
ChDir "C:\Documents and Settings\All Users\Desktop\"
Fname = Application.GetSaveAsFilename(ActiveSheet.Range("AA54").Value, fileFilter:="xls Files (*.xls), *.xls")
If Fname <> "False" Then
ActiveWorkbook.SaveAs Fname
Application.DisplayAlerts = True
MsgBox ("Copy Saved to your Desktop")
End If
Range("C4").Activate
Application.ScreenUpdating = True
End Sub