Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste in all closed files in a folder
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste in all closed files in a folder
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste in all closed files in a folder
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Data from Multiple Closed Files in Folder | Excel Programming | |||
Compare name and copy from closed .csv files | Excel Worksheet Functions | |||
Copy folder and paste to another folder | Excel Programming | |||
How to copy 30 csv files from a folder to another folder | Excel Programming | |||
VBA to copy and paste folder | Excel Programming |