Whoever answers this will be a god!!!!!!!!

[buntykins]

I have this folder you see containing about 60 different workbooks. I have this macro that goes into each one and copies certain data and pastes it into a summary workbook. The thing is, I have only just finished doing it for TWO people!! And that's taken me ages to record!!! Is there any way of telling the macro what to do, and commanding it to do it to all the workbooks in the folder as it's the same operation I need over and over again. The code is here:

Workbooks.Open FileName:= _
"bluehbennettCustomer Services DebtJoe Bloggs.xls"
Range("A2").Select
ActiveWindow.SmallScroll Down:=228
Range("A2:J250").Select
Selection.Copy
Windows("Statistics Collation.xls").Activate
Range("A4").Select
ActiveSheet.Paste
Range("A4").Select
Windows("Joe Bloggs.xls").Activate
Application.CutCopyMode = False
ActiveWindow.LargeScroll Down:=-8
Range("A1").Select
Sheets("Sheet1").Select
ActiveWindow.Close

I will be sooooooooo grateful if someone can help as I am completely stumped (as usual!). Thank you, thank you, thank you in advance

Janie
xxxxxxx

[ This Message was edited by: buntykins on 2002-04-25 07:48 ]

[jrnyman]

Do you always paste it to A4?

[buntykins]

No, I actually paste it to the next available row, but I know how to record that "Go to A65536 and then press CTRL Up" thing so I thought I could insert that somewhere. I know I sound really stupid it's just that this is something I have been working on for months now, and it's nearly completed, so I'm getting all flustered and excited about it!

if it's possible I'd like it to paste the first one to A4, then the next one to A254, then the next one to A504, then to A754 and so on, going up in 250's. I don't suppose anyone knows how to do that as well do they?

Janie
xx

[ This Message was edited by: buntykins on 2002-04-25 08:05 ]

[jrnyman]

My manuAl method would be to manually open the workbook, run this code, then open the next workbook, etc. Should only take a few minutes for 60 workbooks. I made the assumption that you are copying the data to the next empty row.

ThisSheet = ActiveWindow.name
Range("A2:J250").Copy
Windows("Statistics Collation.xls").Activate
Range("A65536").End(x1Up).Offset(1,0).Select
ActiveSheet.Paste
Windows(ThisSheet).Activate
ActiveWindow.Close

[jasonking]

You need to make use of the 'Microsoft Scripting Runtime' reference, if you have not already. (Tools->References...)

You need to use the following:

Dim objFSO As New FileSystemObject
Dim fol As Folder
Dim fls As Files
Dim fil As File

Set fol = objFSO.GetFolder()

Set fls = fol.Files

For Each fil In fls

If (fil.Type = "Microsoft Excel Worksheet") Then


Range("A4").Select
ActiveSheet.Paste
Range("A4").Select
>

End If

Next

Set fil = Nothing
Set fls = Nothing
Set fol = Nothing
Set objFSO = Nothing

Note: You need to specify the folder somehow. You could enter the path in a cell perhaps.

-----
Jason

[AJ]

Just because I can't resist a post with a subject line like this one(!) try the below. It moves each file to a processed subdirectory after it's finished with the file which is something I've found helpful...

Sub getdata()

Dim MyPath As String
Dim MyFile As String
Dim MyIncrement As Long

'Set start row for pasting.
MyIncrement = 4

'Change the drive to whatever drive you're on
ChDrive "C"


'Define Path - change to whatever..
MyPath = "C:temp"

'Change directory to your directory
ChDir (MyPath)

' See if a subdirectoy called "Processed" exists
MyCheckDir = Dir(MyPath & "Processed", vbDirectory)

'If it doesn't exist then create it
If MyCheckDir = "" Then
MkDir MyPath & "Processed"
End If

' Now go looking for Excel files to process
ChDir MyPath & ""
MyFile = Dir("*.xls", vbNormal)

Do While MyFile <> ""

Workbooks.Open Filename:=MyFile

'Do your stuff here (have shortened the code a bit - there were some unnecessary lines)...

Range("A2:J250").Copy
Windows("Statistics Collation.xls").Activate
Range("A" & MyIncrement).Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Switch back to the open Excel file and close it, without saving
Windows(MyFile).Activate
ActiveWorkbook.Close SaveChanges:=0

'Copy the file we've processed to the Processed Directory
FileCopy MyPath & "" & MyFile, MyPath & "Processed" & MyFile
'Delete original
Kill MyFile

' Call Dir again without arguments to return the next *.XLS file in
' the same directory.
MyFile = Dir

'Increment the value for pasting
MyIncrement = MyIncrement + 250

Loop
End Sub


Rgds
AJ

[buntykins]

You'll have to forgive me, both of you but I've never seen anything like either of these things before, so I'm not exactly sure how to implement them. Both of them sound intriguing but how do I get the going?

Jrnyman - How do I specify in your code which folder to look in to open all the workbooks?

Jasonking - When you say specify the folder, could I put this in the code somewhere, and if so, where?

Sorry to be so blonde! Thanks heaps to both of you!

Janie
xxxxx

[buntykins]

AJ, this sounds perfect, but when you say about the processed subdirectory, where would this be? What would it be called?

Janie

[AJ]

Hiya,

In the example it would be C:tempprocessed. Basically, it creates a processed directory off of whatever path you put in in the define path bit near the top of the macro so you can change that bit however you want.

Rgds
AJ


(P.S. remember, anywhere you see two backslashes on this board always swap them for just one - the board seems to automagically add a backslash before a lot of characters in postings, one of which characters is a backslash! Hence the doubling up.)

[jasonking]

Apologies, my posted message messed up when sending.

Btw, Your code will go within the 'If' statement. (That's one bit that messed up).

You can specify the folder's path in the code by entering:

Set fol = objFSO.GetFolder("c:workbooks")

Or you could enter the path in a cell of the current workbook (say cell 'A1'). Then the code would be:

Set fol = objFSO.GetFolder(Range("A1")

-----
Jason