Generic Macro

[roversam]

Let me describe my situation.

I have 2 xls files. Lets call them info<date>.xls and database<date>.xls
I have to update both files on a weekly basis.

Here are my current steps:
1. Manually update stuff in info<date>.xls and save with today's date, leaving the older file unchanged for archival purposes.
2. Copy a bunch of stuff from info<date>.xls and paste it into database<date>.xls
3. Save a new database file with today's date.

I want to automate the copy action because it takes forever to do it manually. I gave it a first shot and it worked, but the macro hard-coded info9.9.11.xls and database9.9.11.xls into it's code. (I did this yesterday, thus 9/9/11)

Now, what I want is that this is not hard-coded, so that even if the file names change, I can just run the macro and based on which file is active, and which one is passive, it does the copy on its own.

Any suggestions?

 

[Smitty]

Welcome to the Board!

Can you post your code?

 

[roversam]

Sub Copy()
'
' Copy Macro
'

'
Range("D6:D11").Select
Selection.Copy
Windows("database9.9.11.xlsx").Activate
Range("D6").Select
ActiveSheet.Paste
Windows("info9.9.11.xlsx").Activate
Range("G6:G11").Select
Windows("database9.9.11.xlsx").Activate
Range("G6").Select
ActiveSheet.Paste
Windows("info9.9.11.xlsx").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("database9.9.11.xlsx").Activate
Range("I6").Select
ActiveSheet.Paste
Windows("info9.9.11.xlsx").Activate
Range("F15:J15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("database9.9.11.xlsx").Activate
Range("E6").Select
ActiveSheet.Paste
Range("E14").Select
Windows("info9.9.11.xlsx").Activate
End Sub


As you see, I have to switch between both the files quite a lot.
But next time I do it, it will be with info9.16.11 and database9.16.11, so everything will fail.

Also, the macro is stored in a third file. And finally, other excel files, apart from the current three: info, database, macro - could also be open.

 

[shg]

This may work (completely untested), but it assumes that each workbook has the proper sheet active.

Sub Copy()
    Dim wkb         As Workbook
    Dim wksDB       As Worksheet
    Dim wksInfo     As Worksheet
 
    For Each wkb In Workbooks
        If LCase(wkb.Name) Like "database*" Then
            Set wksDB = wkb.ActiveSheet
        ElseIf LCase(wkb.Name) Like "info*" Then
            Set wksInfo = wkb.ActiveSheet
        End If
    Next wkb
 
    If wksDB Is Nothing Or wksInfo Is Nothing Then Exit Sub
 
    wksInfo.Range("D6:D11").Copy wksDB.Range("D6")
 
    wksInfo.Range("G6:G11").Copy
    wksDB.Range("G6").PasteSpecial
    wksDB.Range("I6").PasteSpecial
 
    wksDB.Range("F15:J15").Copy wksDB.Range("E6")
End Sub

 

[roversam]

Since I do not have to switch between sheets in the relevant workbooks, this works perfectly.

Thanks a lot.

 

[shg]

You're welcome, glad it helped.