I was wondering if there was anyway to automate this process. Say take all the files in this folder copy visible cells on a specific tab and paste them into a new worksheet/model we already have built. They shouldn't be consolidated rather pasted below the next. Anyway to do this?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Automating tedious copy & pasting
- Thread starter Jokes
- Start date
I have limited experience in VB but do understand it somewhat. I started recording macros and recently starting adding to macros by using code, adding more steps, and changing the tab name/where the info was to be pasted etc. But have yet to write code from scratch. Location doesn't matter to me, I was thinking I create a new folder let's name it "X" the new worksheet I would imagine would not be in that folder, but just one level up. Lets call that "Y". I want to take tab "Z" of every file in folder "X" and put them all successively in the new file "Y". Location can change depending on how we need it. Would you happen to be able to come up with some code I could work with? Also need to paste special values on visible cells on said files. Help would e greatly appreciated. Thanks!
Upvote
0
Have a good weekend Jokes.I've tested this, and it works on my system, see how you go on yours. You will need to adjust the MAIN file name, or the file name of where you're pasting, and the directory of where you are capturing all of the files.
Sub autocopy()
Dim filesystem As Object, myfolder As Object
Dim myfiles As Object, myfile As Object, Pastefile As Object
On Error Resume Next
Set filesystem = CreateObject("Scripting.filesystemobject")
Set myfolder = filesystem.getfolder("D:\LOCATION OF FILES YOU WANT TO OPEN")
Set myfiles = myfolder.Files
Set Pastefile = filesystem.getfile("D:\LOCATION\PASTEFILENAME.xlsx")
Workbooks.Open Filename:="D:\D:\LOCATION\PASTEFILENAME.xlsx"
For Each myfile In myfiles
Workbooks.Open Filename:=myfolder & "\" & myfile.Name
Workbooks(myfile.Name).Worksheets("Sheet1").Cells.Copy
Windows(Pastefile.Name).Activate
Worksheets.Add().Name = myfile.Name
ActiveSheet.Paste
Windows(myfile.Name).Activate
Application.CutCopyMode = False
ActiveWindow.Close
Next
End Sub
Sub autocopy()
Dim filesystem As Object, myfolder As Object
Dim myfiles As Object, myfile As Object, Pastefile As Object
On Error Resume Next
Set filesystem = CreateObject("Scripting.filesystemobject")
Set myfolder = filesystem.getfolder("D:\LOCATION OF FILES YOU WANT TO OPEN")
Set myfiles = myfolder.Files
Set Pastefile = filesystem.getfile("D:\LOCATION\PASTEFILENAME.xlsx")
Workbooks.Open Filename:="D:\D:\LOCATION\PASTEFILENAME.xlsx"
For Each myfile In myfiles
Workbooks.Open Filename:=myfolder & "\" & myfile.Name
Workbooks(myfile.Name).Worksheets("Sheet1").Cells.Copy
Windows(Pastefile.Name).Activate
Worksheets.Add().Name = myfile.Name
ActiveSheet.Paste
Windows(myfile.Name).Activate
Application.CutCopyMode = False
ActiveWindow.Close
Next
End Sub
Upvote
0
Similar threads
- Question
