Between Sheets

F

fvcyo

New Member
Joined
Sep 2, 2004
Messages
16
I have information in a cell of sheet1 and desire to place it in another cell in sheet2 I can do it by a reference but if I erase the value of the cell of sheet1 i like to keep the value in the cell of sheet2

i want to do this automatic if i delete a cell of sheet1 don't erase a cell in sheet2. i can do with copy-paste special but i try a difference way. Anyone Help me..
 
Sort by date Sort by votes
Still not clear :(
By the way congrats for your first win at world championship. I hope many to follow...
What I want to say is where EXACTLY you want data to be transfered at sheet2. Over a row, what row; overwrite data?
It would be better to give me an example.
For instance, data from a1 sheet1 will go to .......
b5 sheet1 will go to ......
d34 sheet1 will go to ......
The ranges are random as you see. The location that they will be copied at sheet2 is not.
Do you want to write them at the next empty cell of column 1 sheet2?
At previous example you will have:
a1 -> a1
b5 -> a2
d34 -> a3
e.c.t.

To write over a row could it possibly mean
a1 ->a1
b5 ->b1
d34 ->c1
e.c.t.?
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Thanks... we going to try to win world cup... I hope so.... and the second example is what i need
a1 ->a1
b5 ->b1
d34 ->c1



Still not clear :(
By the way congrats for your first win at world championship. I hope many to follow...
What I want to say is where EXACTLY you want data to be transfered at sheet2. Over a row, what row; overwrite data?
It would be better to give me an example.
For instance, data from a1 sheet1 will go to .......
b5 sheet1 will go to ......
d34 sheet1 will go to ......
The ranges are random as you see. The location that they will be copied at sheet2 is not.
Do you want to write them at the next empty cell of column 1 sheet2?
At previous example you will have:
a1 -> a1
b5 -> a2
d34 -> a3
e.c.t.

To write over a row could it possibly mean
a1 ->a1
b5 ->b1
d34 ->c1
e.c.t.?
Click to expand...
 
Upvote 0
Try this to copy at row:
Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Sheets("sheet2")
On Error Resume Next
With Target
    If .Value = "" Then Exit Sub
    If Not ws.Range("iv1").Value = "" Then
        If MsgBox("Copied Data Row is full." & Chr(10) & "Do you want to initialize?", vbCritical + vbYesNo) = vbYes Then
            ws.Rows(1).ClearContents
        Else
            Exit Sub
        End If
    End If
    If ws.Range("a1").Value = "" Then
        .Copy Destination:=ws.Range("a1")
    Else
        .Copy Destination:=ws.Range("iv1").End(xlToLeft).Offset(, 1)
    End If
End With
End Sub

Also I'll give you an alternative to copy at column (A:A):
Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Sheets("sheet2")
On Error Resume Next
With Target
    If .Value = "" Then Exit Sub
    If Not ws.Range("a65536").Value = "" Then
        If MsgBox("Copied Data Column is full." & Chr(10) & "Do you want to initialize?", vbCritical + vbYesNo) = vbYes Then
            ws.Columns(1).ClearContents
        Else
            Exit Sub
        End If
    End If
    If ws.Range("a1").Value = "" Then
        .Copy Destination:=ws.Range("a1")
    Else
        .Copy Destination:=ws.Range("a65536").End(xlUp).Offset(1, 0)
    End If
End With
End Sub

And wish you best luck at world championship. You really have a nice team.
 
Upvote 0
ok is works but if i put in the cell =sum(a1:a2) their copy to sheet2 the formula not the value and all value is copy to the row.


Try this to copy at row:
Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Sheets("sheet2")
On Error Resume Next
With Target
    If .Value = "" Then Exit Sub
    If Not ws.Range("iv1").Value = "" Then
        If MsgBox("Copied Data Row is full." & Chr(10) & "Do you want to initialize?", vbCritical + vbYesNo) = vbYes Then
            ws.Rows(1).ClearContents
        Else
            Exit Sub
        End If
    End If
    If ws.Range("a1").Value = "" Then
        .Copy Destination:=ws.Range("a1")
    Else
        .Copy Destination:=ws.Range("iv1").End(xlToLeft).Offset(, 1)
    End If
End With
End Sub

Also I'll give you an alternative to copy at column (A:A):
Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Sheets("sheet2")
On Error Resume Next
With Target
    If .Value = "" Then Exit Sub
    If Not ws.Range("a65536").Value = "" Then
        If MsgBox("Copied Data Column is full." & Chr(10) & "Do you want to initialize?", vbCritical + vbYesNo) = vbYes Then
            ws.Columns(1).ClearContents
        Else
            Exit Sub
        End If
    End If
    If ws.Range("a1").Value = "" Then
        .Copy Destination:=ws.Range("a1")
    Else
        .Copy Destination:=ws.Range("a65536").End(xlUp).Offset(1, 0)
    End If
End With
End Sub

And wish you best luck at world championship. You really have a nice team.
Click to expand...
 
Upvote 0
Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Sheets("sheet2")
On Error Resume Next
With Target
    If .Value = "" Then Exit Sub
    If Not ws.Range("iv1").Value = "" Then
        If MsgBox("Copied Data Row is full." & Chr(10) & "Do you want to initialize?", vbCritical + vbYesNo) = vbYes Then
            ws.Rows(1).ClearContents
        Else
            Exit Sub
        End If
    End If
    If ws.Range("a1").Value = "" Then
        ws.Range("a1") = .Value
    Else
        ws.Range("iv1").End(xlToLeft).Offset(, 1) = .Value
    End If
End With
End Sub

Also to clear something, where code is: if msgbox("Copied Data Row is Full... e.c.t. , if your row is full, by pressing yes it will CLEAR all the row, from data.
 
Upvote 0
Ok i understand but all cell what i need to copy have a this formula =sum(a1:a2), =sum(a3:a4), etc. so the macro copy the formula not value.

Thanks ... Mexico is going to win in friday Arriba Mexico...


Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Sheets("sheet2")
On Error Resume Next
With Target
    If .Value = "" Then Exit Sub
    If Not ws.Range("iv1").Value = "" Then
        If MsgBox("Copied Data Row is full." & Chr(10) & "Do you want to initialize?", vbCritical + vbYesNo) = vbYes Then
            ws.Rows(1).ClearContents
        Else
            Exit Sub
        End If
    End If
    If ws.Range("a1").Value = "" Then
        ws.Range("a1") = .Value
    Else
        ws.Range("iv1").End(xlToLeft).Offset(, 1) = .Value
    End If
End With
End Sub

Also to clear something, where code is: if msgbox("Copied Data Row is Full... e.c.t. , if your row is full, by pressing yes it will CLEAR all the row, from data.
Click to expand...
 
Upvote 0
Have you tried the last code that i posted you? Even if there is formula at sheet 1 it copies the value to sheet2. Notice that the cell at sheet 1 has to change in order to be copied. Try that in a new workbook. Copy at Sheet1
VBA code (press alt+F11 to open VBA). Then go at sheet1 write whatever you like, whereever you like, make sums e.c.t. and then check sheet2 if everything is copied as you like.
 
Upvote 0
Thats the problem because if a put a number in any cell the number is copy to row in the sheet2 i only want to copy were i have sum range.
Thaks for all you help.

Have you tried the last code that i posted you? Even if there is formula at sheet 1 it copies the value to sheet2. Notice that the cell at sheet 1 has to change in order to be copied. Try that in a new workbook. Copy at Sheet1
VBA code (press alt+F11 to open VBA). Then go at sheet1 write whatever you like, whereever you like, make sums e.c.t. and then check sheet2 if everything is copied as you like.
Click to expand...
 
Upvote 0
ok is works but if i put in the cell =sum(a1:a2) their copy to sheet2 the formula not the value and all value is copy to the row.
Click to expand...

Could you be more clear at this?
1)If you put in the cell =sum... at sheet1 right?
2)their copy to sheet2 the formula not the value....
example: =sum(a1:a2) result=6
do you want to be copied at sheet2 the value (6)
or the formula (=sum(a1:a2))?
3)and all value is copy to the row.... ???? copy whatever you want from question 2 to entire row1 of sheet2?
 
Upvote 0
1)If you put in the cell =sum... at sheet1 right?
right.

2)their copy to sheet2 the formula not the value....
example: =sum(a1:a2) result=6
do you want to be copied at sheet2 the value (6)
or the formula (=sum(a1:a2))?
the value 6 not the formula.

3)and all value is copy to the row.... ????
with the last code you send to me if i put a number in any cell in sheet1 the macro copy the numbers in the row over the sheet2 and the value of sum rage is no copy to sheet2 i only need the value of the sum rage copied to the row in sheet2.

Thanks for the Help.


ok is works but if i put in the cell =sum(a1:a2) their copy to sheet2 the formula not the value and all value is copy to the row.
Click to expand...

Could you be more clear at this?
1)If you put in the cell =sum... at sheet1 right?
2)their copy to sheet2 the formula not the value....
example: =sum(a1:a2) result=6
do you want to be copied at sheet2 the value (6)
or the formula (=sum(a1:a2))?
3)and all value is copy to the row.... ???? copy whatever you want from question 2 to entire row1 of sheet2?
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
copy cell and cell above onto different sheet, under the next available line.
Replies
5
Views
187
Alex Blakenburg
A
R
  • Solved
Paste values in another sheet after last Cell containing Data
Replies
5
Views
170
Umma123
U
M
  • Question Question
2-Way Cell Referencing
Replies
1
Views
136
jkpieterse
jkpieterse
C
  • Solved
List Results from All Sheets Search
2
Replies
16
Views
218
MikeVol
MikeVol
U
  • Solved
Doing a search for text within a call, from entries in a defined name range
Replies
3
Views
116
Upex
U

Forum statistics

Threads
1,223,958
Messages
6,175,636
Members
452,663
Latest member
MEMEH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top