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
"Patrick Molloy" wrote:
> 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" wrote:
>
> > 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
|
Similar Threads
