jleve1974
11-28-2007, 08:56 AM
Hello All,
I have several 100 Excel workbooks within a folder that I have to perform the following steps on:
1) open up a file out of the 100's & copy all data off sheet one of this 1st workbook.
2) then paste it to sheet 1 of a 2nd workbook which will reflect changes on sheet 2 of this workbook ,
3) Copy all data on sheet 2 of 2nd workbook then "paste special" it back to the same sheet 2 as "values" only
4) then delete sheet 1 of this 2nd workbook
5) and finally save this 2nd workbook with the same name as the 1st workbook where the data was originally copied from resulting in a [B]3rd and final workbook.
The following is a macro I recorded performing this task on just one of the files. I basically need this task repeated for every single file within the same "Lab 1" folder:
Sub CopyPasteSave()
'
' CopyPasteSave Macro
' Macro recorded 11/28/2007 by levanoj
'
'
ChDir "C:\Documents and Settings\levanoj\Desktop\Lab 1"
Workbooks.Open Filename:= _
"C:\Documents and Settings\levanoj\Desktop\Lab 1\315.xls"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ChDir "C:\Documents and Settings\levanoj\Desktop"
Workbooks.Open Filename:= _
"C:\Documents and Settings\levanoj\Desktop\Template.xls"
Range("A2").Select
ActiveSheet.Paste
Sheets("MICRO-MINI").Select
ActiveWindow.SmallScroll Down:=-51
Columns("A:F").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("BUTTONS").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Windows("315.xls").Activate
ActiveWindow.Close
ChDir "C:\Documents and Settings\levanoj\Desktop\Lab 2"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\levanoj\Desktop\Lab 2\315.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
End Sub
I have several 100 Excel workbooks within a folder that I have to perform the following steps on:
1) open up a file out of the 100's & copy all data off sheet one of this 1st workbook.
2) then paste it to sheet 1 of a 2nd workbook which will reflect changes on sheet 2 of this workbook ,
3) Copy all data on sheet 2 of 2nd workbook then "paste special" it back to the same sheet 2 as "values" only
4) then delete sheet 1 of this 2nd workbook
5) and finally save this 2nd workbook with the same name as the 1st workbook where the data was originally copied from resulting in a [B]3rd and final workbook.
The following is a macro I recorded performing this task on just one of the files. I basically need this task repeated for every single file within the same "Lab 1" folder:
Sub CopyPasteSave()
'
' CopyPasteSave Macro
' Macro recorded 11/28/2007 by levanoj
'
'
ChDir "C:\Documents and Settings\levanoj\Desktop\Lab 1"
Workbooks.Open Filename:= _
"C:\Documents and Settings\levanoj\Desktop\Lab 1\315.xls"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ChDir "C:\Documents and Settings\levanoj\Desktop"
Workbooks.Open Filename:= _
"C:\Documents and Settings\levanoj\Desktop\Template.xls"
Range("A2").Select
ActiveSheet.Paste
Sheets("MICRO-MINI").Select
ActiveWindow.SmallScroll Down:=-51
Columns("A:F").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("BUTTONS").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Windows("315.xls").Activate
ActiveWindow.Close
ChDir "C:\Documents and Settings\levanoj\Desktop\Lab 2"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\levanoj\Desktop\Lab 2\315.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
End Sub