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
Hi Jokes,
You've exceeded your private message quota and I can't send you any more at the moment. You may want to consider cleaning your inbox.
He's my reply.
The first thing I noticed was one of the dims was corrupt.
Dim myfi34les As Object that needs to be changed back to myfiles
about half way down -
rownum = ActiveSheet.UsedRange.Rows.Coun
it's missing the t in count. Small I know, but important, because computers are anal little buggers
See how you go after making those changes.
If it's still not working after that, provide me with about 3 file names you want copied, how many sheets in each, ect, so a small summary of what you expect, so I can re-test it on my system to see what the issue might be.
You've exceeded your private message quota and I can't send you any more at the moment. You may want to consider cleaning your inbox.
He's my reply.
The first thing I noticed was one of the dims was corrupt.
Dim myfi34les As Object that needs to be changed back to myfiles
about half way down -
rownum = ActiveSheet.UsedRange.Rows.Coun
it's missing the t in count. Small I know, but important, because computers are anal little buggers
See how you go after making those changes.
If it's still not working after that, provide me with about 3 file names you want copied, how many sheets in each, ect, so a small summary of what you expect, so I can re-test it on my system to see what the issue might be.
Upvote
0
It was working before so file names are unimportant, it did take all the files in the folder and took the sheet I specified in lieu of your "Sheet1" those minor changes are fixed but it is still not giving me the results that I want. Like I said in the beginning I would like these all pasted successively one after another on one sheet. This did work but put them all on separate tabs I'll clear out my private message box. Thanks for all your help.
Upvote
0
Sounds good thank you, feel free to drop me a private message, I made the changes described to make them one after another I think there is just a small part missing as we know it works, now is just coming back no results no errors. It has got to be extremely close. Thanks, Jokes
Upvote
0
Hi Jokes.
To have the data all put into one sheet on the "pastefile" you need to add this code here
Windows(Pastefile.Name).Activate
Lastrow = ActiveSheet.UsedRange.Rows.Count 'This finds the last line in the worksheet on your pastefile
Lastrow = Lastrow + 1 'This adds one to the row, so it's not pasting over the bottom line
Worksheets.Add().Name = myfile.Name 'Delete this line because you're not adding a worksheet now
Cells(lastrow,1).Select
ActiveSheet.Paste
Let me know how that goes.
Sorry for the delay in finishing this for you.
If it's not 100%, re-post your entire code for me, so I can go through it, and we'll see what the problem is.
BTW, when I tried to mail this to you, it still said your mailbox was full
To have the data all put into one sheet on the "pastefile" you need to add this code here
Windows(Pastefile.Name).Activate
Lastrow = ActiveSheet.UsedRange.Rows.Count 'This finds the last line in the worksheet on your pastefile
Lastrow = Lastrow + 1 'This adds one to the row, so it's not pasting over the bottom line
Worksheets.Add().Name = myfile.Name 'Delete this line because you're not adding a worksheet now
Cells(lastrow,1).Select
ActiveSheet.Paste
Let me know how that goes.
Sorry for the delay in finishing this for you.
If it's not 100%, re-post your entire code for me, so I can go through it, and we'll see what the problem is.
BTW, when I tried to mail this to you, it still said your mailbox was full
Upvote
0
Try this macro out. I've tested it on my system.
You will need to change the file location back to yours, as well as the paste file details, but everything else works.
It opens the paste file, then opened each file in the folder you select, and copies the entire sheet into the main sheet as one sheet.
You will need to change the file location back to yours, as well as the paste file details, but everything else works.
It opens the paste file, then opened each file in the folder you select, and copies the entire sheet into the main sheet as one sheet.
Code:
Sub autocopy4()
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("Folder that holds all the files you want to open")
Set myfiles = myfolder.Files
Set Pastefile = filesystem.getfile("File that you want the data captured in")
Workbooks.Open Filename:="Data capture file"
For Each myfile In myfiles
Workbooks.Open Filename:=myfolder & "\" & myfile.Name
Rownum = ActiveSheet.UsedRange.Rows.Count
Windows(Pastefile.Name).Activate
Lastrow = ActiveSheet.UsedRange.Rows.Count
If Lastrow = 1 Then
Else: Lastrow = Lastrow + 1
End If
Workbooks(myfile.Name).Worksheets("Sheet1").Range("A1:A" & Rownum).EntireRow.Copy Destination:=Cells(Lastrow, 1)
Windows(myfile.Name).Activate
Application.CutCopyMode = False
ActiveWindow.Close
Next
End Sub
Upvote
0
Similar threads
- Question
