copying trough lock cells

[danovkos]

Hi,
pls. is it possible to do this action?

I have huge table. This table contain data about clients for 12 month. Month are devided with headers. So januar is in row 4:500, feb 503:1000, march 1003:1503 and so....
Each month has header (3 line), which i dont want to change. For filling new data to this table (to whole table for all month) i use vlookup and it always overwrite my headers for each month because i copy it in row 4:5000. I tried to lock cells but if i select this locked cells with copying my formula it return error (of course).

Is it possible to copy over the locked cells, withou it return error and without overwriting my headers? And it will fill formula only to unlocked cells, and doesnt change any format in my headers?

I dont know if i am clear because my bad englis, but i hope, that you will understand.
thx for all help

[SamT]

Can you share a workbook with us.

We all speak VBA.

SamT

[danovkos]

My new file what i want to upload to this thread is only 500 KB big.
I tried to upload a sample, but it wrote to me, that

"Exceeds your quota by 1.40 MB"

i searched option to delete/remove my old atach. but, i was not successfull. I found only list of my atach, but there was not possibility to delete it. Am i stupid or there is no way to upload a file?

[geekgirlau]

Have you tried zipping the file first? Alternatively, create a cut-down version with just enough information to show us the structure and the problem, but without all the data attached.

[danovkos]

yes, i tried, but it doesnt help
still show me error message, -
Exceeds your quota by 1.03 MB
i want to delete/remove my old data but i dont know how

i it strange that i exceededs my quota, but i can not get it down :(
or?
maybe i am finish with uploading on this forum for ever :(

[GTO]

My new file what i want to upload to this thread is only 500 KB big.
I tried to upload a sample, but it wrote to me, that

"Exceeds your quota by 1.40 MB"

i searched option to delete/remove my old atach. but, i was not successfull. I found only list of my atach, but there was not possibility to delete it. Am i stupid or there is no way to upload a file?


yes, i tried, but it doesnt help
still show me error message, -
Exceeds your quota by 1.03 MB
i want to delete/remove my old data but i dont know how

i it strange that i exceededs my quota, but i can not get it down :(
or?
maybe i am finish with uploading on this forum for ever :(

Hi danovkos,

It is not your fault, there are some fluky issues happening with the postings. Yesterday, I tried like eight times to post (no attachment) and anything longer than a few sentences would just time-out.

I tried w/an attachment, and I think I might have seen a similar message. Leastwise I don't ever recall some red bar at the top of the manage attachments window with someting close to "sum of all attachments owned by GTO 1.04 mb".

Weird. Anyways, keep trying :igiveup: ...

Mark

[GTO]

BTW, which version of Internet Explorer do you have? Just curious, as was wondering whether this has anything to do with it...

Mark

[danovkos]

thx for info GTO,
i use ie 7
because i am at work :)

[danovkos]

OK, but without atached sample nobody can helps me?
I try it explain one more time.

I have table with e.g. 1000 rows and 30 columns.
After e.g. each 100 rows is header. This header devide data of table - it is devide year to months.
1 row - header january (contain, ID, product, name...30 other values)
...
100 row header febr (contain, ID, product, name...30 other values)
...
200 row header march (contain, ID, product, name...30 other values)
...

and monthly i update data with vlookup, but i want to copy this formula to row 1 and copy to row 1000 without changing headers (jan, feb, mar...)

i dont want to overwrite my headers

how can i do this? I tried lock, but then it return error.
any other suggestions?

[GTO]

Did you try geekgirl's suggestion of zipping the workbook and attaching?

Mark

[danovkos]

Did you try geekgirl's suggestion of zipping the workbook and attaching?

Mark

Yes i tried it.
I can not post atach. I will return error message as i wrote. :(

[SamT]

danovkos,

Your english is good enough.

Your explanations are not good.

In two posts you say:

1) "So januar is in row 4:500, feb 503:1000, march 1003:1503"
2) "100 rows is header. This header devide data of table - it is devide year to months."

1) "i copy it in row 4:5000."
2) "copy this formula to row 1 and copy to row 1000"

I don't know what Rows to write help for.

SamT

[danovkos]

Yes, i know, that my examples are different. It is because i think it doesnt matter if is table about 1000 row or 5000 rows big.

Most important is, that there are headers betwen data, which i dont want to overwrite with coping formulas.

It doesnt matter if is the header on row 100 or row 400 or if is there 2 or 5 headers between data. Because i try to find solution, which solve overwriting this header during copiing my formula trough header in row 100 or 400 or 900 or other.

If you post me any suggestions for any example e.g. for table (1:1000) i will change it, that it will pass to my real data.

Do you know what i mean? :doh:

[SamT]

>>It doesnt matter if is the header on row 100 or row 400

Yes it does.

Option Explicit
Sub Make_Names()
ActiveSheet.Range("A1").Select
With ActiveSheet.Names
.Add Name:="Jan", _
RefersTo:="=A4:AD100"
.Add Name:="Feb", _
RefersTo:="=A104:AD200"
'Continue to December
End With
End Sub
Sub Add_formulas()
Dim nm As Name
Dim i As Long
For Each nm In ActiveSheet.Names
'Insert your formulas here
Next
End Sub

SamT

[danovkos]

ok,

ONE IMPORTANT INFO:
Number of row, where header of month is depends on count of clients, which i adding (inserting rows) during whole year. I mean, that now is header for february on row 300, but when i add new data (adding monthly) this header can be on row 350.

we can say, that my table is
1:1000 rows
20 columns
headers are on rows:
1st header (main - with this i dont have problem, because i copy my formula from 2nd row till 1000)
2nd header - on row 200
3th header on row 350
4th - 600
5 - 700
6 - 900
header consist from 20 columns (value a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,r,s,t,u)

thats all,
or any other info i should to add?

[SamT]

I told you it mattered.

Come see me when your cup is no longer full.

SamT

[danovkos]

ok thx
i hoped, that here will be any solution with protection cells or something like this..where can i choose my headers and set them to "nonoverwriting" without returning error if i try it. Or maybe some code in ThisWorkbook, which do this.

Your code solve also writing text to header, writing my formulas..but this i dont want to solve with code.
I want only protect my headers and the other things i will do, as i do it now.
But if is not possible do it this way, nevermind.
also thx a lot for try

[Aussiebear]

Cut the workbook data down. Really we probably only need to see Initial header, say 10 rows of your sanitised data, then the next header set. All this could be supplied on one sheet, with accompaning notes on what needs to be done. Leave out any fancy formatting if you use any, and post the sample workbook. I'm confident some real progress can then be achieved.

[danovkos]

Ok,
finaly i can upload some sample.
Here is something like my table.
I want, that my header (filled shadow) will be never overwrited with my formula, coping ...
Of course i will insert in the future other rows in my months.