Renew button - Application.ScreenUpdating (Transferring Information) 1

[TBL3]

Hi,

I have a button named 'Renew' that contains a macro which transfers the information that has been already filled on the current worksheet into a new opened worksheet by providing the file name into the inputbox that's populated.

Every functionality does work fine, however, I was running through some tests and realized that when a wrong file name is inputted into the inputbox populated, the macro still runs eventhough such file doesn't exists.

Hence, my question is that, how would I code it so that, when the name of the file you input does not match any of the files opened, it will recognize that there isn't such a file and will stop the macro and ideally populate an error msg saying that 'the file you have indicated is not opened.'

Here is the code I have for this macro. Any suggestions will be apprecitated.

Private Sub Renewal2()
On Error Resume Next
Application.ScreenUpdating = False
Dim copyfrom
Dim copyto
copyfrom = ThisWorkbook.Name
copyto = InputBox("Please enter the name of the file you want to copy data to; this file must be already opened.  This is case-sensitive and cannot be the same name as the current workbook.  Do not add the .xls extension.    (Ex. Name of file = 'FileName_MonthDayYear.xls', then input 'FileName_MonthDayYear' in the box).") & ".xls"

If copyto <> ".xls" Then
ActiveSheet.Unprotect ("Ecogarage10")
Windows(copyto).Activate
Sheets("EcoRater Garage").Activate
Windows(copyfrom).Activate
    Range("$F$4").Select
    Selection.Copy
    Windows(copyto).Activate
    Range("$F$4").Select
    ActiveSheet.Paste
    Windows(copyfrom).Activate
    Range("$F$5").Select
    Selection.Copy
    Windows(copyto).Activate
    Range("$F$5").Select
    ActiveSheet.Paste
    Windows(copyfrom).Activate
    Range("$F$6:$G$6").Select
    Selection.Copy
    Windows(copyto).Activate
    Range("$F$6:$G$6").Select
    ActiveSheet.Paste
    Windows(copyfrom).Activate
    Range("$L$4:$M6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(copyto).Activate
    Range("$L$4:$M$6").Select
    ActiveSheet.Paste
    Windows(copyfrom).Activate
.
.
.
.
.
Range("F6:G6").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = True
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.merge
    Range("F4:G4").Select
    Sheet1.Protect ("Ecogarage10")
Application.ScreenUpdating = True

End If
End Sub

 

[xlbo]

never ever EVER use:

On Error Resume Next

at the top of a procedure - especially if you want to be able to trap errors. Incredibly bad practice. Should only ever be used around a statement that you know may fail and that doesn;t matter if it does (for example referring to a dynamic named range that could be empty)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

On a sheet, simply TRY to select a non-contiguous range and COPY.

Then see what happens.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]