Solved: PasteSpecial on same sheet

[Eville]

OK, as someone told me there are no silly questions ;) and convinced me to post mine on the forums im sure you experts solved mine in no-time ^^
I have attached my sheet as example so its easier to understand what i like to see happen.

The whole sheet is ment to be a logbook and will be used for a month (one sheet each day). During this month several different productions will be made, and each new production the OrderMP3 sheet will be renewed.
In cell C7:E7 (and downwarts) there is a Vlookup formula that finds the needed data in sheet 'OrderMP3'.
However when a new order is pasted into the OrderMP3 sheet, the vlookup formula will keep looking into the OrderMP3 sheet.
If the OrderMP3 sheet is cleared, the data in C7:E7 will be lost.

So my question is:
Is it possible to remove the Vlookup formula (eg with some pastespecial function) once there is data in the cell?
So in the example: C7 has the formula "=IF(ISNA(VLOOKUP($B7;OrderMP3!$B$17:$I$9999;2;FALSE)=TRUE);"";(VLOOKUP($B7;OrderMP3!$B$17:$I$9999;2;FALSE)))" and the data "STA".
What i would like it to do is remove the formula once the STA appears.

Im not well skilled in VBA or excel, so there might be an easy sollution but i cant think of any, so i hope someone here can help me : pray2:

Thanks alot in advance!

Ev ^^

Edit: im not sure why but i had to zip the file, the forum did not allow me to post is as excel file ><

[p45cal]

You could run this macro periodically (it works on the active sheet):
Sub blah()
For Each cll In ActiveSheet.Range("A7:A60").SpecialCells(xlCellTypeConstants, 23).Cells
With cll.Offset(, 1).Resize(, 5)
.Value = .Value
End With
Next cll
End Sub

[Eville]

You could run this macro periodically (it works on the active sheet):
Sub blah()
For Each cll In ActiveSheet.Range("A7:A60").SpecialCells(xlCellTypeConstants, 23).Cells
With cll.Offset(, 1).Resize(, 5)
.Value = .Value
End With
Next cll
End Sub

I was already afraid someone would come up with a macro to run ... how can i let a macro run for example every minute? (i only know how to run a macro with a button)
And what does the macro parts do please? As i cant see any pastespecial command. I will test the code piece you suggested tomorrow but its kinda late now. Thx in advance (if it works as i like ^^)

Ev

[draco664]

I was already afraid someone would come up with a macro to run ... how can i let a macro run for example every minute? (i only know how to run a macro with a button)
And what does the macro parts do please? As i cant see any pastespecial command. I will test the code piece you suggested tomorrow but its kinda late now. Thx in advance (if it works as i like ^^)

Ev

Running it every so often isn't necessary, just put the macro on the worksheet's object page under the Worksheet_Change sub. That way it will run automatically every time the worksheet changes.

Chris

[p45cal]

Well, I'd be concerned if I used a sheet change event, or ran the macro at set intervals, that the data may not have calculated before the formulae were removed (though I think there's a way round that by waiting for the calculation to finish somehow).
You only really need to do it just before you change the OrderMP3 sheet. So if you have to be on the OrderMP3 sheet to do that (do you?) then you could get the macro to run on a sheet being deactivated, for example, you could have, in the ThisWorkbook code module (untested):
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If UCase(Left(Sh.Name, 3)) = "DAY" Then
For Each cll In Sh.Range("A7:A60").SpecialCells(xlCellTypeConstants, 23).Cells
With cll.Offset(, 1).Resize(, 5)
.Value = .Value
End With
Next cll
End If
End Sub which will check that the name of the sheet you're deactivating (coming off from) starts with "Day" (case insensitive), and if so runs the macro on that sheet.

What the code does:
It looks for cells in the range A7:A60 which are not empty.
(It's the same as selecting cells A7:A60 then pressing F5, then clicking Special… and choosing constants:
5541
With each of those cells it takes the cell to the right (cll.offset(,1)) and resizes that reference to 5 cells wide (this should be the cells on that row which need converting to constants rather than formulae), then on those cells it takes the value in that cell (not the formula) and makes the cell that value, thus removing the formula from each one if there was one (.value = .value, which is instead of paste|special|values).

[Blade Hunter]

Insert application.calculate as the first line of code

[Eville]

Thank you very much for the solution, it works for 99% ... only when i try to get away from an empty sheet it gives an error:
Run-time error '1004':
no cells where found

So as far as i can understand it needs at least one cell filled with data to let the piece of code work. However when no production is made (eg on a maintenance day) then no COIL NR is used, so the cells remain empty.
To make it clear i used this code in ThisWorkbook:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.Calculate
If UCase(Left(Sh.Name, 3)) = "DAY" Then
For Each cll In Sh.Range("A7:A60").SpecialCells(xlCellTypeConstants, 23).Cells
With cll.Offset(, 1).Resize(, 5)
.Value = .Value
End With
Next cll
End If
End Sub
The orange part is in debug mode highlighted as yellow

Ev

Well, I'd be concerned if I used a sheet change event, or ran the macro at set intervals, that the data may not have calculated before the formulae were removed (though I think there's a way round that by waiting for the calculation to finish somehow).
You only really need to do it just before you change the OrderMP3 sheet. So if you have to be on the OrderMP3 sheet to do that (do you?) then you could get the macro to run on a sheet being deactivated, for example, you could have, in the ThisWorkbook code module (untested):
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If UCase(Left(Sh.Name, 3)) = "DAY" Then
For Each cll In Sh.Range("A7:A60").SpecialCells(xlCellTypeConstants, 23).Cells
With cll.Offset(, 1).Resize(, 5)
.Value = .Value
End With
Next cll
End If
End Sub which will check that the name of the sheet you're deactivating (coming off from) starts with "Day" (case insensitive), and if so runs the macro on that sheet.

What the code does:
It looks for cells in the range A7:A60 which are not empty.
(It's the same as selecting cells A7:A60 then pressing F5, then clicking Special… and choosing constants:
5541
With each of those cells it takes the cell to the right (cll.offset(,1)) and resizes that reference to 5 cells wide (this should be the cells on that row which need converting to constants rather than formulae), then on those cells it takes the value in that cell (not the formula) and makes the cell that value, thus removing the formula from each one if there was one (.value = .value, which is instead of paste|special|values).

[Eville]

Edit:
This is the yellow line in debug screen (see attachment)


Some additional info:
As answer to your question: yes we have to use the OrderMP3 tab from time to time (eg to paste in an update from the current order or a new order when the current one is finished). Also people move around between the sheets alot.

The piece of code i have used as suggested gives also a runtime error when i make a new sheet. It leaves the hidden sheet "DAY" unhidden, unprotected and the error moves me to the VBA Debug page, so apparently its missing something but my knowledge lacks here :think:
Hope you got another brilliant suggestion to fix this, as it seems to get closer ^^

Thanks again in advance

Ev

Oh btw i was not able to Edit my last post, thats why i made a new one :wot

[p45cal]

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim TheRng As Range
If UCase(Left(Sh.Name, 3)) = "DAY" Then
On Error Resume Next
Set TheRng = Sh.Range("A7:A60").SpecialCells(xlCellTypeConstants, 23)
On Error GoTo 0
If Not TheRng Is Nothing Then
For Each cll In TheRng.Cells
With cll.Offset(, 1).Resize(, 5)
.Value = TheRng.Value
End With
Next cll
End If
End If
End Sub

You can make your NEWDAY sub slicker:
Sub NEWDAY()
ThisWorkbook.Unprotect ("XCPS6")
With Sheets("DAY")
.Visible = 0 '-1 'it only needs to move from xlveryhidden to hidden.
.Copy before:=Sheets(1)
.Visible = 2
End With
ThisWorkbook.Protect ("XCPS6")
End Sub

[p45cal]

…and of course I cock up, the copied sheet is still Hidden, so:
Sub NEWDAY()
ThisWorkbook.Unprotect ("XCPS6")
With Sheets("DAY")
.Visible = 0 '-1 'it only needs to move from xlveryhidden to hidden.
.Copy before:=Sheets(1)
Sheets(1).Visible = -1
.Visible = 2
End With
ThisWorkbook.Protect ("XCPS6")
End Sub

[Eville]

Hmmmm lol;
i have replaced the 1st VBA code piece which works without ... untill i leave the DAY(1) sheet. When i return all values that used to be formulas turned into "A1A"; same thing happens when i make a new day. And when i replace the code piece with the 'slicker' one it doesnt make any new day and also turns all formulas into "A1A". I added a ZIP'ed original file here (well without the colours and patterns as it was too big to zip it) so you can have a look whats happening.
I do appreciate the help btw, alone i wouldnt be able to get this far :rofl:

Ev

EDIT: your correction did solve the 'make new sheet' part, still gives all cells A1A tho ><

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim TheRng As Range
If UCase(Left(Sh.Name, 3)) = "DAY" Then
On Error Resume Next
Set TheRng = Sh.Range("A7:A60").SpecialCells(xlCellTypeConstants, 23)
On Error GoTo 0
If Not TheRng Is Nothing Then
For Each cll In TheRng.Cells
With cll.Offset(, 1).Resize(, 5)
.Value = TheRng.Value
End With
Next cll
End If
End If
End Sub

You can make your NEWDAY sub slicker:
Sub NEWDAY()
ThisWorkbook.Unprotect ("XCPS6")
With Sheets("DAY")
.Visible = 0 '-1 'it only needs to move from xlveryhidden to hidden.
.Copy before:=Sheets(1)
.Visible = 2
End With
ThisWorkbook.Protect ("XCPS6")
End Sub

[p45cal]

... untill i leave the DAY(1) sheet. When i return all values that used to be formulas turned into "A1A"; same thing happens when i make a new day.I don't get that here, I need to see this!


And when i replace the code piece with the 'slicker' one it doesnt make any new dayI think it does, but it's hidden, addressed in my previous post (no selecting allowed!).

Try with these two:Sub NEWDAY()
ThisWorkbook.Unprotect ("XCPS6")
With Sheets("DAY")
.Visible = 0 '-1 'it only needs to move from xlveryhidden to hidden.
.Copy before:=Sheets(1)
Sheets(1).Visible = -1 ' this is the newly added sheet now.
.Visible = 2
End With
ThisWorkbook.Protect ("XCPS6")
End Sub
and
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim TheRng As Range
If UCase(Left(Sh.Name, 3)) = "DAY" And UCase(Application.Trim(Sh.Name)) <> "DAY" Then
On Error Resume Next
Set TheRng = Sh.Range("A7:A60").SpecialCells(xlCellTypeConstants, 23)
On Error GoTo 0
If Not TheRng Is Nothing Then
For Each cll In TheRng.Cells
With cll.Offset(, 1).Resize(, 5)
.Value = TheRng.Value
End With
Next cll
End If
End If
End Sub
If you're still having problems, could we connect up remotely so that I can see what happening on your screen (eg.TeamViewer)?

[Eville]

I don't get that here, I need to see this!
...
...
If you're still having problems, could we connect up remotely so that I can see what happening on your screen (eg.TeamViewer)?
I have added an example on my previous post, however your always welcome to use teamviewer. I will send details in a PM and open TV
Here are 2 screenshots about whats happening:

[Eville]

Thank you very much for all the help Pascal, the sheet is now working exactly as i had it in mind! Your a great VBA magician :thumb
I would have never got this workign without you, this matter is solved ;)
(cant find the 'Solved' button sorry ><)

Ev

[p45cal]

this matter is solved ;)
(cant find the 'Solved' button sorry ><)
EvIt's in Thread Tools at the top of the thread.

[shrivallabha]

And all the internet browser's don't show this "SOLVED" button. Google Chrome doesn't for sure. So I switched to Internet Explorer....

[Eville]

It's in Thread Tools at the top of the thread.
It wasnt at mine, but ...

And all the internet browser's don't show this "SOLVED" button. Google Chrome doesn't for sure. So I switched to Internet Explorer....
this explains it: i use google chrome. Ive installed IE now to tick the solve button. Thx again both for the help on this ;)

Ev ^^