Perhaps you can attach a file(s)
1. can be solved with a loop where the file names are listed in a2:a22
Cash Disbursement Schedule.xls"
etc
sub openfilesinlist()
mymonth="OCTOBER" 'or an inputbox asking for the month or in a cell...
for each myfile in range("a2:a22")
Workbooks.Open Filename:= _
"C:\TRABAJOKO\2011-FINANCIALS\WTB\" & mymonth & "\Schedules\"&
myfile
call autofill
next myfile
end sub
2.
Sub Autofill() ' NOT tested
Columns("f").Insert Shift:=xlToRight
range("d2:f2").Autofill Destination:=range("F2:F" & range("E"
&Rows.Count).End(xlUp).Row)
Columns("j").Insert Shift:=xlToRight
range("h2:i2").Autofill Destination:=range("J2:J" & range("I"
&Rows.Count).End(xlUp).Row)
Dim Rng As range
Dim h As range
Set Rng = range("h1:h" & range("h1").End(xlDown).Row)
Set C = range("h1").End(xlDown).Offset(1, 0)
'gets the sum of each column-H, I, & J
C.Formula = "=SUM(" & Rng.Address(False, False) & ")"
C.Copy C.Resize(1, 3)
End Sub
3. Use this idea (modify to suit)
Sub CombineFilesSAS() ' Again NOT tested for your
dfn = Range("b2")
dfs = Range("c2")
Set ds = ActiveWorkbook
For Each c In SourceRange' different source range
Workbooks.Open Filename:=c
ActiveWorkbook.Sheets("sheet1").UsedRange.Copy _
ds.Sheets(c").Range("a2")
ActiveWorkbook.Close
Next c
ds.Save
Call chgdir
End Sub
4. NOT needed as you will close each after getting data.
I deleted most of your original post to save lines.
=========
Don Guillett
SalesAid Software
dguille...@gmail.com
-----Original Message-----
From: DanJ
Sent: Monday, October 24, 2011 9:17 PM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ HELP: SHORTEN MY MACRO USING LOOP
Dear Sirs,
I have a macro which updates my monthly trial balance. But it's too
long. Takes time to process.
I want to shorten it particularly step 1, step 3 & step 4, using loop
which i am not very good at.
Please help how to shorten my codes. Below is my macro.
Thank you.
DanJ
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
