Solved: how to delete some range with values on another sheet

[PaSha]

hey guys... me again...

i have i question... i'm gonna explain this fast... hope you can help me...

so the problem is like this:

i have some sheets, which are geting some values from users and these sheets are protected... and what i want is that when i start a special sheet, and when the day is the 1st in month then i would like to delete those range with values...

i made some code of my one just simple but it doesn't work, i think it's becouse of the protection, or something else...

hope you understand me and can help me...

so for the hint what i was traying, here me part of code:



If dan = 1 Then

Workbooks.Open("C:\Users\user\Documents\Company\Ruckstand\Ruckstandeintrag1.xlsm")

Sheets("PONEDELJEK-Montag").Select
Sheets("PONEDELJEK-Montag").Range("i7:i14").Delete

End With



and pardon, while i was typing this i managed to make this code to work, but the deleted cells get some #REF and so on???... i don't know why ... ???

and is there any posibility to make this delete without opening the workbooks, bcz. i have to delete these ranges from 5 workbooks and every workbook has 6 sheets with same range...????

and if i would open everyone that would be a freaking mess...
hehe

so hope you can help me

[PaSha]

hey guys... me again,hehe... ok now i know there were some REF when i deleted the cells, becouse delete statement delete all the formualls and so on...

so i used If dan = 1 Then

Sheets("PONEDELJEK-Montag").Range("i7:i14").ClearContents
End With

and it works fine it deletes only the values, not the formulas...

now the only thing i need is this so please help me...

how can i make these changes to workbooks and sheets without opening them ?????

or is there a posibility to say... when this workbooks opens then make this changes... not open and make... but when it opens then make ???

[PaSha]

:( :( hey guys...

i made this code and it works great... but is there any posibility the make this code without opening thos workbooks... please tell me if yes ...


my code looks like this now..



Sub prvi()
dan = Sheets("Sheet1").Range("e11").Value
If dan = 1 Then
Workbooks.Open ("C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag1.xlsm")
Worksheets("PONEDELJEK-Montag").Unprotect Password:="myPass"
Sheets("PONEDELJEK-Montag").Range("i7:i14").ClearContents
Worksheets("PONEDELJEK-Montag").Protect Password:="myPass"
Worksheets("TOREK-Dienstag").Unprotect Password:="myPass"
Sheets("TOREK-Dienstag").Range("i7:i14").ClearContents
Worksheets("TOREK-Dienstag").Protect Password:="myPass"
Worksheets("SREDA-Mitwoch").Unprotect Password:="myPass"
Sheets("SREDA-Mitwoch").Range("i7:i14").ClearContents
Worksheets("SREDA-Mitwoch").Protect Password:="myPass"
Worksheets("ČETRTEK-Donnerstag").Unprotect Password:="myPass"
Sheets("ČETRTEK-Donnerstag").Range("i7:i14").ClearContents
Worksheets("ČETRTEK-Donnerstag").Protect Password:="myPass"
Worksheets("PETEK-Freitag").Unprotect Password:="myPass"
Sheets("PETEK-Freitag").Range("i7:i14").ClearContents
Worksheets("PETEK-Freitag").Protect Password:="myPass"
Worksheets("SAMSTAG-Sobota").Unprotect Password:="myPass"
Sheets("SAMSTAG-Sobota").Range("i7:i14").ClearContents
Worksheets("SAMSTAG-Sobota").Protect Password:="myPass"

'there are 4 more workobooks to open and to to the same with them... so that's why i wonder if that is posible -

End If
End Sub

[PaSha]

hey guys... i've done it, finally...

i searched and found a threath and replay of xld about opening, making unvisible, doing changes, closing sheets... and it wroks great...

i just had to make a little bit of code change, becouse, at the end there has to be a code for making the sheet visivle again, befor closing, becouse if not, there is a problem, hehe... so my code looks like this now:


If dan = 1 Then
Set oWB = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag1.xlsm")
Windows(oWB.Name).Visible = False
With oWB.Worksheets(1)
.Unprotect Password:="myPass"
.Range("i7:i14").ClearContents
.Protect Password:="myPass"
End With
With oWB.Worksheets(2)
.Unprotect Password:="myPass"
.Range("i7:i14").ClearContents
.Protect Password:="myPass"
End With
With oWB.Worksheets(3)
.Unprotect Password:="myPass"
.Range("i7:i14").ClearContents
.Protect Password:="myPass"
End With
With oWB.Worksheets(4)
.Unprotect Password:="myPass"
.Range("i7:i14").ClearContents
.Protect Password:="myPass"
End With
With oWB.Worksheets(5)
.Unprotect Password:="myPass"
.Range("i7:i14").ClearContents
.Protect Password:="myPass"
End With
With oWB.Worksheets(6)
.Unprotect Password:="myPass"
.Range("i7:i14").ClearContents
.Protect Password:="myPass"
End With
Windows(oWB.Name).Visible = True
oWB.Close savechanges:=True

End If




i have only one question... this is the codee for one workbook, hehe, how could i make it smaller this code?? becouse if i do +4 the same code, becouse the workbooks has only another number 1,2,3,4,5... hehe ???

I'mean how to make with does case 1 and so on ...
:cloud9:

[PaSha]

oh boy, hehe, somehow i've managed it, hehe ...
so i can say threath solved, :cloud9: ...

only what is left... that the code is to big what i asked befor... but it's ok... only i see the code,hehe...

so if anyone can see, how i could do this better what i have made... than please replay... i would be happy...

so thanks again ... :hi: