PDA

View Full Version : Solved: Part of macro to work from renamed file



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

Bob Phillips
08-19-2011, 10:35 AM
I am not sure what you mean, can you give more detail?

PEV
08-19-2011, 11:03 AM
The original file starts off named SSI
user completes the form, actions the below macro with a button
the macro sends a range from the active sheet to another workbook
it then SaveAs another filename based on some parameters in a cell

it all works fine. But if the user manually SaveAs another file name, the macro will not execute the Log portion of it. It's looking for Set wbkFrom = Workbooks("SSI.xls") which is not called SSI.xls anymore

Kenneth Hobs
08-20-2011, 01:44 AM
Where does the code reside, SSI,xls? If so, use ThisWorkbook.Name if you need the name rather than the workbook object.

PEV
09-06-2011, 05:04 AM
sorry for the late reply. i've been on vacation the last 2 weeks. This did the trick. Workbooks(thisworkbook.name). Thanks O-Great-One

Bob Phillips
09-06-2011, 05:39 AM
Why not just use ThisWorkbook?