Simle Then If

[Peltz]

Hi there, this is driving me nuts.

I Changed location of a couple of files and now suddently I got an error?!

Sub Slette1()
'
' Slette1 Makro
' Makro registrert 04.08.2014 av jhetland
'
Dim Team As String

Team = "F:\Utkast\Teamarbeid (Jenskladd)\Teamarbeid.xls"
Workbooks.Open Team, UpdateLinks:=True
       If Workbooks("Pasientliste").Sheets("Behandlingsavdelingen").Cells(7, 1).Text = Workbooks("Team").Sheets("Ark3").Cells(25, 1).Text Then
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
       Else
        Application.Run "Teamarbeid.xls!Pas1"
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
End If
End Sub

Now. The code is launched from Pasientliste.xls. The code also opens Teamarbeid.xls. However, I get the subscript out of range!! Whats happening here? The debugger kicks in at the IF statement...

Thanks

 

[Peltz]

Sorry for the long time it took for me to respond. As you probably understand, programming isn't my main task at work

I've fiddled around with some minor adjustments, expanded and made a blueprint. This seems to do what I want..

 Sub Slette1()
' Slette1 Makro
' Makro registrert 04.08.2014 av jhetland

Dim TeamarbeidA As Workbook, TeamarbeidB As Workbook, TeamarbeidC As Workbook, Pasientliste As Workbook

'RAD 7
'If Range("Q7").Value = "A" Then
If Sheets("Behandlingsavdelingen").Range("O7").Value = "A" Then
Workbooks.Open ("G:\BEHANDLINGSAVDELINGEN\Teamarbeid\Teamarbeid.xls")
 
Set Pasientliste = Workbooks("Pasientliste.xls")
Set Teamarbeid = Workbooks("Teamarbeid.xls")
'Not correct future path. This is the correct future path. It will be placed another directory from Behandlingsavdelingen.xls. Now, in this example, they're in the same directory as Behandlingsavdelingen.xls.
'Set Teamarbeid = Workbooks("G:\Klientkladd\Behandlingsavdelingen\1_Felles maler\Teamarbeid\TeamA.xls")
Teamarbeid.Activate
    Rows("34:34").Select
    Selection.Insert Shift:=xlDown
    Range("A7:AR7").Select
    Selection.Copy
    Range("A34:AR34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
        Range("A7:AR7").Select
    Selection.Copy
    Range("A34:AR34").Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
        Range("A7:AR7").Select
    Selection.ClearContents
   
    
Pasientliste.Activate
 Range("A7:D7,F7:U7").Select
 Range("A7").Activate
 Selection.ClearContents
 Teamarbeid.Close SaveChanges:=True
End If
End Sub

What I want to do is saving "Teamarbeid.xls" as three differnt files ("TeamA.xls";TeamB.xls";TeamC.xls"). I will be using this part of the above code to determin which cells will be copied and deleted.

If Sheets("Behandlingsavdelingen").Range("O7").Value = "A" Then
Workbooks.Open ("G:\BEHANDLINGSAVDELINGEN\Teamarbeid\TeamA.xls")

The value at O7 determines which file will open, that is A=TeamA.xls, B=TeamB.xls, and C=TeamC.xls.

My solution to achieve this is to copy the whole code twice, and change the above part: .Value ="B" and .Value.="C". (in addition ill have to change the "set Teamarbeid" = [path to the corresponding file])
The problem is that I have 16 rows that I need to perform this operation on 16 rows, 7-24 in "Pasientliste" and in corresponding 16 rows in "Teamarbeid" 7-24. Each operation is assigned to different macros (16 macros) to do this operation. Instead of writing the code 48 times, is there a more elegant way to do this? This is more for learining purposes.

Thanks