hi all,
below is the code which iam using to autocollate the workbook from folder to master workbook but while pasting it is showing too many alerts and
iam not able understand the codding for paste special,
Sub finalcollation()
Dim myfile As String
Dim filepath As String
Dim erow
filepath = "i:\CAP_Profile\Desktop\collation\"
'myfile = Dir("i:\CAP_Profile\Desktop\collation\")
myfile = Dir(filepath)
Do While Len(myfile) > 0
If myfile = "FINAL COLLATION.xlsm" Then
Exit Sub
End If
Workbooks.Open (filepath & myfile)
Range("b4:w351").Copy
'Range("f4:w351").Copy
ActiveWorkbook.Close
erow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(erow, 2), Cells(erow, 23))
myfile = Dir
Application.DisplayAlerts = False
Application.CutCopyMode = False
Loop
End Sub
please help where iam going wrong and also some one plese help the code to paste special
and instead of specified range i want copy used range please help me with that also.
thanks alot for your help