New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

autocollate pastespecial workbooks from a folder to masterworkbook

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

autocollate pastespecial workbooks from a folder to masterworkbook

Postby pawandyawa » Thu Jun 16, 2016 5:01 am

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
  • 0

pawandyawa
Rookie
 
Posts: 2
Joined: Feb 29, 2016
Reputation: 0
Excel Version: excel 2010

Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 23 guests

cron