copy paste from multiple workbooks to master workbook

[ravinder_tigh]

Hi Guys

Plz Help me out with this situation I’m working on a project in which I had to copy data from child files (*.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 ) 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 ) 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

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

‘’’’’’Want to apply if check if file is already open
Workbooks.Open Filename:= _
“ ' ThisWorkbook.Path & " \child file name.xls" “
For i = 1 To Sheets.Count - 2
ShtNames(i) = Sheets(i).Name
Worksheets(ShtNames(i)).Activate
Range("c8:e68").Select
Selection.Copy
Workbooks("master.xls").Activate
With Workbooks("master.xls")
Worksheets(ShtNames(i)).Activate
Worksheets(ShtNames(i)).Range("c8:e68").Select
ActiveSheet.PasteSpecial

//combo box generates here

End With
‘a = ShtNames(i)
‘MsgBox (a)
Next i
Application.CutCopyMode = False
ActiveWorkbook.Save
Windows("Ankita").Close savechanges:=False
End Sub

plz tell me how to deal with this that by macro itself that yes is being clicked and macro executes to another sheet





My 2nd problem is I had to apply this macro on number of files at a time I’m only able to open one

I do have a solution but don’t know if its work or not if I can store the file names in an array other than master file and make a loop which runs from 1st to ubound(of that array)


For this solution I don’t know how to store those files names in that array as every file has same path and folder

Other than that I had to select different range for each of the file that also my concern how to choose dynamic range

Although it could be done with the help of

If(file name ) then range for each file but that going to require a lotz of ifff

Cud u plz help me with this

Problem

Ur Help Would be Gr8ly appreciated

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!