Please can someone improve my current code - opens a box for the user to select a file and when the correct file is selected it pastes the data to correct tab. However, I am not sure how to improve it as follows:
1) Can i specify the name of the target file (costings) in the code so it only works on that file? At the moment the user can select any file and it looks for the sheet name "SAP" and if it can't find it doesn't work - goes to debug mode - i would like it to give the message "Macro will end now because you failed to select the correct file".
2) Same as 1 above but the message now is "Macro will end now because there is no SAP sheet in the file". This message will come up after selecting the correct file but there's no SAP sheet in it.
2) Clear the existing data in the target "SAP" sheet in the given range (do i have the correct code at the moment?)
3) When the user clicks cancel (because they don't want to proceed) a message appears and sub ends - "The macro will end now because you did not select the file"
Thank you!
'Set source workbook
Set wb = ActiveWorkbook
'Open the target workbook
vFile = Application.GetOpenFilename("Excel-files,*.xlsm", 1, "Please select file", , False)
Set wb2 = ActiveWorkbook
'clear existing values form target book
wb2.Worksheets("SAP").Range("A:N").ClearContents
'Copy/paste the visible data to the new workbook
wb.Worksheets("sheet1").Cells.SpecialCells(xlCellTypeVisible).Copy
wb2.Worksheets("SAP").Range("A1").PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("A1").Select
Application.CutCopyMode = False
Windows("Format MCP6 Final.xlsm").Close
I think you could have solved most of the questions for your self by searching the specific Problem, but i gonna try to help you anyway.
1 If you know the Name of the file, why do you let the user choose it ? Anyway you can check the Name with "workbook.name" and test whatever you want
2 That's easy done by this:
If (Worksheets("SAP").Name = "") Then
MsgBox "Macro will end now because there is no SAP sheet in the file"
Exit Sub
End If
2.2? Depends on what you try to delete. ClearContents will not delete any formatings.
3 You have to define vFile as Variant and check it for false:
vFile = Application.GetOpenFilename("Excel-files,*.xlsm", 1, "Please select file", , False)
if vFile = false then
MsgBox "The macro will end now because you did not select the file"
Exit Sub
End If
But for the future, split the 4 question in 4 Posts, and try do describe what you have tried, and where you have Problems. And Questions like: "Is it correct what I have done?" are not suitable for SO. Try it and if you have Problems ask why These Problems occur.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments