Copy, Paste, Then Save

[jleve1974]

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 [because of vlookups already in place on sheet 2],
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 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

[Simon Lloyd]

It may just be me but this action:

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 [because of vlookups already in place on sheet 2],
3) Copy all data on sheet 2 of 2nd workbook then "paste special" it back to the same sheet 2 as "values" only

will only ever work the once as you state that sheet 2 has "Vlookups" yet you want to copy the information gained by them and paste back to the same sheet as values which will wipe out your formulae!

As you may well know this:

save this 2nd workbook with the same name as the 1st workbook

is only possible if you save to a different path or overwrite the original first workbook......

[jleve1974]

actually overwriting the original workbook would be fine. Is this doable then?

[jleve1974]

.

[Simon Lloyd]

JLeve, it is possible, however, how will you reference the workbooks to decide which is the first to be maipulated and which is the second out of your 100's of workbooks? which then also begs the question if the 3rd and final workbook is saved in the same folder how will you differentiate between that and the others to be manipulated?

In order to help you further we would require more information and a little more explanation of what you would like to achieve!

[jleve1974]

well I can say that each workbook within the Lab 1 folder has a # as a name (e.g. 301, 302, etc.) and they are in numerical ascending sequence.

So to answer your question, I'd like to have the macro basically select the 1st workbook to be manipulated to be the one named with the lowest numerical value and 2nd to be the next one up and so on.
Does this answer your question?
Please let me know if there's anything further I can provide you to clarify what I need accomplished, thanks again.

[Simon Lloyd]

JLeve, i understand what your perception is but as i see it it is purely a cascade through all of your workbooks, i.e Wb301 gives values to Wb302 make data in sheet 2 Wb302 values only, SaveAs Wb301....Wb302 (now 1st Wb) gives values to Wb303 make data in sheet 2 Wb303 values only, SaveAs Wb302....Wb303 gives values to Wb304.....etc, it seems highly confusing and very labour intensive just to change the values of the final workbook Wbxnn!

I have to admit i am having trouble working out what your final goal is.

[jleve1974]

would it at all be possible to email you:

1. a sample of one of the 100's of workbooks I'm copying data from
2. a copy of the workbook I'm pasting the data to
3. and finally a copy of what the final file would look like

I think if you could see what I'm working with, you'd have a clearer picture of what I'm trying to accomplish.
By the way, I can't email you through this site since apparently I don't have enough # of "posts" to do so so if your willing to maybe you can send me your email, thanks again.