Error Generation during copypaste macro run

[ravinder_tig]

Hi Guys

Plz Help me out with this situation I’m working on a project in which I had to copy data from child woekbooks (*.xls) and paste it to the master workbook with same page to page every time when a macro is executed i had done the copy and paste part

But I'm Facing the problem in which i had to deal with

Validations as on both master and child sheet validation (column based combo box is activated )
one is worktype
2ns is time type

i jst had to copy data to the master macro works perfectly fine but the problem is that a msg box appears which signifies that i had to change the name (version ) for both types when i click yes 2 times it pastes the data

I'm attaching my macro as well as pic of that msg box with this attachment




Dim a As String
Dim ShtNames() As String
ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count)

'Child

If IsFileOpen(ThisWorkbook.Path & "\child.xls") Then
MsgBox ("File Is Open")
Else
Workbooks.Open filename:= _
ThisWorkbook.Path & "\child.xls"
End If
For i = 1 To Sheets.Count - 2
Workbooks("child.xls").Activate
ShtNames(i) = Sheets(i).Name
Worksheets(ShtNames(i)).Activate
Range("c73:e133").Select
Selection.Copy
ThisWorkbook.Activate
'Workbooks("master.xls").Activate
With ThisWorkbook
'With Workbooks("master.xls")
Worksheets(ShtNames(i)).Activate
Worksheets(ShtNames(i)).Range("c73:e133").Select
'ActiveSheet.PasteSpecial
' Application.CutCopyMode = False
End With
Workbooks("child.xls").Activate
ShtNames(i) = Sheets(i).Name
Worksheets(ShtNames(i)).Activate
Range("G73:H133").Select
Selection.Copy
Workbooks("master.xls").Activate
With Workbooks("master.xls")
Worksheets(ShtNames(i)).Activate
Worksheets(ShtNames(i)).Range("G73:H133").Select
ActiveSheet.PasteSpecial

' After THis msg box appears which is in image file

End With
Next i
Application.CutCopyMode = False
ActiveWorkbook.Save
Windows("child.xls").Close savechanges:=False
End Sub


Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum

' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
Error errnum
End Select

End Function



Cud u plz help me with this Problem
Ur Help Would be Gr8ly appreciated

I'm Attaching The Screen shot of that Msgbox
And both Dummy copies of Master And child file



Plz Just Help Me With any property of Paste Special WHich copies the data without this typeof validation check

and copies the data n move to next sheet

[Bob Phillips]

Try adding


Application.DisplayAlerts = False


just before the code that does the paste and reset back to true after.

[ravinder_tig]

thanks a lot but i do hava query if u plzzzzzzz

what its going to do weather its going to cancel tht msg box or select Yes for both worktype and date type in the sheet by default

[MaximS]

probably you can solve that problem previously changing named range 'Work' in all formulas of both Worksheets.

Excel is assuming that 'Work' named range might refere to 2 different ranges thats why is showing this error message.

Other thing you can try is :


Application.DisplayAlerts = False

[Bob Phillips]

thanks a lot but i do hava query if u plzzzzzzz

what its going to do weather its going to cancel tht msg box or select Yes for both worktype and date type in the sheet by default

It will be equivalent to selecting yes.

[mdmackillop]

Hi Ravinder,
When you post code here, select it and click the green VBA button to format it as shown.
Regards
MD