Copy and paste in all closed files in a folder

[b1llt]

I would like to perform a copy & paste of the range A1:F50 within multiple
files in a folder (that are closed) just by running some code in a separate
file. The copy & paste would need to occur within each file --meaning that
it may be different data for each file depending what is in it's own range
A1:F50. The copied data would need to be inserted into row 1 --trying to get
the new data inserted above the existing data. Also, I'd like to have the
date change within cell C5 to a value entered by the user in the file that
the macro resides in.
Please let me know if this isn't clear as I may have rambled.
Thanks,
Bill

 

[Patrick Molloy]

its not 100% clear to me. Are you copying the data from sheet1 in each
workbook. so you copy sheet1 ! A1:F50 then just insert into the same place ie
replicating the
data?

the code to copy is

With Range("A1:F50")
.Copy
.Insert xlShiftDown
End With


to scroll through a folder, you can simply use the DIR() function...

Dim sFile As String
Const sFOLDER As String = "H:\excel\test\"
sFile = Dir(sFOLDER & "*.xls")
Do While sFile <> ""
MsgBox sFile
sFile = Dir()
Loop

so, putting this together:

Option Explicit
Sub Main()
Dim wb As Workbook
Dim sFile As String
Const sFOLDER As String = "H:\excel\test\"
sFile = Dir(sFOLDER & "*.xls")
Do While sFile <> ""
Set wb = Workbooks.Open(sFOLDER & sFile)
With wb.ActiveSheet.Range("A1:F50")
.Copy
.Insert xlShiftDown
Application.CutCopyMode = False
End With
wb.Close False ' CHANGE TO TRUE TO SAVE FILE
Set wb = Nothing
sFile = Dir()
Loop
End Sub

 

[b1llt]

Yes, 'sheet1' is actually labled '2009'. This does do what I wanted it to
but, what happens when we add in another sheet labled 2010 how do I
distinguish between sheets so it only happens on the most current?
Thanks again,
Bill