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

buntykins

Board Regular
Joined
Apr 11, 2002
Messages
76
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
 
Right, I've been trying all the codes here but I can't get them to work. I am really, really rubbish with filepaths and don't know where to put them or anything. Thing is, the folder is called "Customer Services Debt", it's located in a subdirectory called "hbennett" which is the H Drive of our system. The summary file that I need this macro to run on is in the same folder as all these workbooks. So basically I will go into the summary workbook, press this button and it will magically (hopefully!) open all the other workbooks in the "Customer Services Debt" folder (all 60 of them) perform the copy on each workbook from a sheet called "Data" then paste each sheet's info back onto the summary sheet at 250 line increments, then close all the other 60 workbooks apart from the summary sheet.

Phew! Out of breath now!!

Janie
xx

P.S. Thanks you guys
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hiya,

I'm not quite sure I totally get what path is what but if I understand correctly, hbennet is mapped as drive H on your pc. And then Customer Services Debt is a folder in the root of drive H.
With this in mind try the following adaptation to my macro (I've removed the "processed" malarky).

Please note, while all the files you want to add together can be (and indeed have to be) all in the same directory - H:Customer Services Debt, the summary file called Statistics Collation.xls must be in a different directory of else the macro will try and call that file too to get data from.

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 "H"


'Define Path - change to whatever..
MyPath = "H:Customer Services Debt"

'Change directory to your directory
ChDir (MyPath)

'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)...
Sheets("Data").Activate
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

' 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

Are we getting any closer???

Rgds
AJ
 
Upvote 0
OH MY GOD YOU ARE A BLINKIN' FLIPPIN' GOD AJ!!!!!!!!!!!!!!!!!!!!

Janie

xxxxxxxxxxx
xxxxxxxxxxxxx
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

All for you, you absolute diamond!!!!
 
Upvote 0
Always a pleasure!
BTW, I reckon jason's approach was a pretty nice one - less code than mine to do the same job.
 
Upvote 0
It looked cool, but I have real trouble with this sort of thing and you explained it nicely, so thank you, thank you, thank you!!!!

xxxxx
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top