Excel Help

[Rodbouy]

Im looking to see if what I am looking for is possible.


I have a sheet that have various data.


I have made macros that copy and paste certain ranges from that sheet.


Now on the sheet that I want to paste the data into, I have locked all cells bar the ones I want the data entered in too.


The problem Is when you begin to fill up the space, and say I only have 5 rows left, If i press the macros to paste the data that has more than 5 rows you will get a error as the rest of the cells are locked.


So what I want to know, is it possible for the macro to be edited so that it would then continue the pasting action in the next desired colum

So say the copied data has a range of A1:A10

I have open colums of A1:A5 and B1:B5

Could I get it to paste like that if I run out of room.

Make sense?

[Bann32]

Depends how robust you need to be in pasting, if the locked sells are absolute (stay the same constantly) -

You can interrogate each cell of the pasting range to determine if it's "Locked" property is True. You don't need to look specifically at different types of formulas. You build a subset range for all the unlocked cells within the given range (I called it SelRange).

It can be a time consuming process if the range to be analyzed is very large. If you're only working with a few thousand cells or so the range can be analyzed relatively quickly. If you're spanning all 65k rows you might run into a problem.

The code to create and copy/paste to a subset range of unlocked cells might look like this:

Sub CopyToUnlocked() 
     
    Dim cell As Range, MyRange As Range, SelRange As Range 
     
    Set MyRange = Range("B3:D40") 
     
    For Each cell In MyRange.Cells 
        If Not cell.Locked Then 
            If Not SelRange Is Nothing Then 
                Set SelRange = Union(cell, SelRange) 
            Else 
                Set SelRange = cell 
            End If 
        End If 
    Next cell 
     
    Range("A1").Copy SelRange 
     
End Sub

...now that assumes you're just copying the contents (perhaps a formula) from cell A1 to the SelRange. An interesting twist is that if A1 was not also unlocked, its locked state would travel along during the first copy/paste action and the next time you try to run the macro it would err out, because it would relock all the cells. In which case you could just use pastespecial with formulas or values instead of the all inclusive (implied in this case) paste.

I took this from -

 http://www.ozgrid.com/forum/showthread.php?t=85869

[masur123]

Wouldnt this work?

activesheet.unprotect

and once the pasting is done

activesheet.protect

[Rodbouy]

The cells need to be locked, or it will wreck the specific formula i have snaking between the 2 sections.

[masur123]

Ahh so the cells already have something in them, hence why its locked?

In that case the macro above would work if the ranges are fixed

[Rodbouy]

The open cells for the first section is B1:B49, then once that was filled I would need the data to then contiunue on to M1:M49

So once it ran out of space at B49 the rest of the data would start pasting in to cells M1 and down.

[masur123]

What I find with macros in excel, is that no one way is the right way.

For instance, I use an awful lot of Do and Loops to get the answers I want.

So while the macro above works, its not really the way I would do it. But each to their own!

[Rodbouy]

Not getting any joy, will upload it and see if anyone can see what i mean better.

Test.zip

When entering the macro the cell to start in is the route colum, this then grabs the data from the other sheet, and work the way down the sheet. then want it to move to the other section.

[masur123]

If you havent go an answer by tomorrow evening, I will have a crack at this for you, but will need some clarification what you really want so I properly understand it.

[Rodbouy]

if you look at it, its split in to 2 sections.

If you start in b1 and press the macros 950 then once that fills the data the next avail space press 951 then 953.

Once it runs out, you will get a debug error as the data being copied has no where to copy due to the locked cells,

So I need the rest of that data to copy over to m1 then carry on.

[masur123]

Not as easy as I first surmised, but will keep at it! I will not be defeated, and there is a vba guru at work who I can ask if needed

[Rodbouy]

Cheers mate, its doing my head in.

Im just trying to make it as simple as possible for others to use.

Its easy enough to just manually cut and paste where the overlap is.

I am constantly adapting the sheet till i have it right but the constraints still remain.

Cheers for sharing the headache

[masur123]

Sorry mate, moved house last friday and only just managed to get my pc up. Will look at this over the next couple of days.

[Rodbouy]

No worries mate,

I posted on a excel forum but no joy on it either. But the rate of post that it sees, it unlikely anyone sees it.

PS good luck on the move. Stress city

[dell_xps]

Hi m8, see yer from the City Of Discovery :-), my wife's a database programer, I'll ask her to have a look at it for ya :-),
EDIT:
just showed the wife yer spread sheet, she sais it can be done, but it might need a different language ?, over my head , I'll nip her head when she's got a free hour ok :-)