All
My macro is not working and im at a loss
I have 2 workbooks “FORM” and “EXPORT MASTER” and essentially FORM is for entering data and EXPORT MASTER is for collecting it
Unfortunately because all users need to print FORM and get a signature on it before sending it on to a different department to then bring the data back up using the unique reference number that’s created this is the only way it can be done and explaining the ins and outs of the 20 departments it affects would take me a long time so your gonna have to trust me theres no way around it.
When users have completed all information in FORM and start the macro then FORM is saved as a new filename which changes every time (and is taken from Cell AV36) using the below code……
'Save form as filename CRN+AMOUNT
If ActiveSheet.Range("AW30") = 0 Then
SAVENAME1 = ActiveSheet.Range("AV36")
SAVENAME2 = "S:\Finance\Financial Operations\CHQ REQ\PAYREQs\" & SAVENAME1
ActiveWorkbook.SaveAs Filename:=SAVENAME2
Else
If ActiveSheet.Range("AW30") > 0 Then
Exit Sub
End If
End If
Then EXPORT MASTER workbook is activated and a unique reference number created which is then copied…….
' OPEN MASTER SHEET
Workbooks.Open Filename:= _
"S:\Finance\Financial Operations\CHQ REQ\EXPORT MASTER.xls"
Windows("EXPORT MASTER.xls").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
ActiveCell.Select
Selection.Copy
This is where my problem occurs…..
Windows("SAVENAME2").Activate
Range("U2:X2").Select
ActiveSheet.Paste
As I try to refer back to FORM under its new filename I do not know how to refer to it correctly so I can paste the unique number back
I have tried many different versions of
Windows("SAVENAME2").Activate
But none seem to work
After I have got back to it then the rest of the macro worsk fine…
' COPY DATA LINE TO MASTERWORKBOOK
Sheets("UPLOAD LINE").Select
Rows("4:5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("EXPORT MASTER.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
' SAVE AND CLOSE
ActiveWorkbook.Save
ActiveWindow.Close
Sheets("FORM").Select
Range("A1").Select
End Sub
I wish to add more but will not complicate the macro any further until ive fixed this part
Many thanks in advance for your help
Michael
Bookmarks