Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping info inside cells
I'm trying to get the information inside of the cells to copy to another
workbook no matter where the info is. I have tried to the find command and that works but i need the name plus the numbers in the next 8 cells to the right to move too and those numbers are always changing. Is there a way to find a certain name and always have the 8 cells next to the name move? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping info inside cells
Look at the following macro:
Sub copy_it() Dim r As Range, r1 As Range, r2 As Range Workbooks("Book1").Activate Worksheets("Sheet1").Activate s = InputBox("Enter search value: ") For Each r In ActiveSheet.UsedRange If r.Value = s Then Set r1 = Range(r, r.Offset(0, 8)) Set r2 = Workbooks("Book2").Worksheets("Sheet1").Range("A1" ) r1.Copy r2 Exit Sub End If Next End Sub It examines all the cells in workbook Book1, worksheet Sheet1 for a value. If that value is found, then that cell and the eight cells next to it are copied to workbook Book2. -- Gary's Student "Hinojosa" wrote: I'm trying to get the information inside of the cells to copy to another workbook no matter where the info is. I have tried to the find command and that works but i need the name plus the numbers in the next 8 cells to the right to move too and those numbers are always changing. Is there a way to find a certain name and always have the 8 cells next to the name move? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping info inside cells
I'm sorry I'm new at this so r=the value i want correct?
Hinojosa wrote: I'm trying to get the information inside of the cells to copy to another workbook no matter where the info is. I have tried to the find command and that works but i need the name plus the numbers in the next 8 cells to the right to move too and those numbers are always changing. Is there a way to find a certain name and always have the 8 cells next to the name move? -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping info inside cells
see i have over 200 invoices i would have to do this for would i have to
write this down for each one? Hinojosa wrote: I'm sorry I'm new at this so r=the value i want correct? I'm trying to get the information inside of the cells to copy to another workbook no matter where the info is. I have tried to the find command and that works but i need the name plus the numbers in the next 8 cells to the right to move too and those numbers are always changing. Is there a way to find a certain name and always have the 8 cells next to the name move? -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping info inside cells
Gary,
I like that answer. I believe I can modify it to suit my similar needs. Here we have a DDE input into our spreadsheet coming in as a serial string. The format is an ID tag in cell a1 with the data in cell b1. We test for the ID to determine which of eleven discreet sensors are being looked at. We then try and move the data into one of eleven appropriate cells. We have tried to use the 'IF'command in Excel but it is burdened with the 'ELSE' feature that wipes out pre-existing data if the original If condition is not met. To get around the reseting of the last data value while testing for the new, we had to create a circular logic condition that works well enough for now but is not very elegant. Why is it that the 'IF THEN ELSE' function in EXCEL does not allow a move or copy? It seems to me that other spredsheets have been able to do this. "Gary''s Student" wrote: Look at the following macro: Sub copy_it() Dim r As Range, r1 As Range, r2 As Range Workbooks("Book1").Activate Worksheets("Sheet1").Activate s = InputBox("Enter search value: ") For Each r In ActiveSheet.UsedRange If r.Value = s Then Set r1 = Range(r, r.Offset(0, 8)) Set r2 = Workbooks("Book2").Worksheets("Sheet1").Range("A1" ) r1.Copy r2 Exit Sub End If Next End Sub It examines all the cells in workbook Book1, worksheet Sheet1 for a value. If that value is found, then that cell and the eight cells next to it are copied to workbook Book2. -- Gary's Student "Hinojosa" wrote: I'm trying to get the information inside of the cells to copy to another workbook no matter where the info is. I have tried to the find command and that works but i need the name plus the numbers in the next 8 cells to the right to move too and those numbers are always changing. Is there a way to find a certain name and always have the 8 cells next to the name move? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping info inside cells
Just update this post with the code you are trying to get to work.
I'll take a look at it Friday. -- Gary's Student "TonyJ03" wrote: Gary, I like that answer. I believe I can modify it to suit my similar needs. Here we have a DDE input into our spreadsheet coming in as a serial string. The format is an ID tag in cell a1 with the data in cell b1. We test for the ID to determine which of eleven discreet sensors are being looked at. We then try and move the data into one of eleven appropriate cells. We have tried to use the 'IF'command in Excel but it is burdened with the 'ELSE' feature that wipes out pre-existing data if the original If condition is not met. To get around the reseting of the last data value while testing for the new, we had to create a circular logic condition that works well enough for now but is not very elegant. Why is it that the 'IF THEN ELSE' function in EXCEL does not allow a move or copy? It seems to me that other spredsheets have been able to do this. "Gary''s Student" wrote: Look at the following macro: Sub copy_it() Dim r As Range, r1 As Range, r2 As Range Workbooks("Book1").Activate Worksheets("Sheet1").Activate s = InputBox("Enter search value: ") For Each r In ActiveSheet.UsedRange If r.Value = s Then Set r1 = Range(r, r.Offset(0, 8)) Set r2 = Workbooks("Book2").Worksheets("Sheet1").Range("A1" ) r1.Copy r2 Exit Sub End If Next End Sub It examines all the cells in workbook Book1, worksheet Sheet1 for a value. If that value is found, then that cell and the eight cells next to it are copied to workbook Book2. -- Gary's Student "Hinojosa" wrote: I'm trying to get the information inside of the cells to copy to another workbook no matter where the info is. I have tried to the find command and that works but i need the name plus the numbers in the next 8 cells to the right to move too and those numbers are always changing. Is there a way to find a certain name and always have the 8 cells next to the name move? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping info inside cells
Thanks for the timely response but, how 'bout I hold off 'til next Monday
instead of this Friday. I am headed out of the office for a long weekend. I think there may be an even easier approach to my particular situation after looking at you code a little closer. "Gary''s Student" wrote: Just update this post with the code you are trying to get to work. I'll take a look at it Friday. -- Gary's Student "TonyJ03" wrote: Gary, I like that answer. I believe I can modify it to suit my similar needs. Here we have a DDE input into our spreadsheet coming in as a serial string. The format is an ID tag in cell a1 with the data in cell b1. We test for the ID to determine which of eleven discreet sensors are being looked at. We then try and move the data into one of eleven appropriate cells. We have tried to use the 'IF'command in Excel but it is burdened with the 'ELSE' feature that wipes out pre-existing data if the original If condition is not met. To get around the reseting of the last data value while testing for the new, we had to create a circular logic condition that works well enough for now but is not very elegant. Why is it that the 'IF THEN ELSE' function in EXCEL does not allow a move or copy? It seems to me that other spredsheets have been able to do this. "Gary''s Student" wrote: Look at the following macro: Sub copy_it() Dim r As Range, r1 As Range, r2 As Range Workbooks("Book1").Activate Worksheets("Sheet1").Activate s = InputBox("Enter search value: ") For Each r In ActiveSheet.UsedRange If r.Value = s Then Set r1 = Range(r, r.Offset(0, 8)) Set r2 = Workbooks("Book2").Worksheets("Sheet1").Range("A1" ) r1.Copy r2 Exit Sub End If Next End Sub It examines all the cells in workbook Book1, worksheet Sheet1 for a value. If that value is found, then that cell and the eight cells next to it are copied to workbook Book2. -- Gary's Student "Hinojosa" wrote: I'm trying to get the information inside of the cells to copy to another workbook no matter where the info is. I have tried to the find command and that works but i need the name plus the numbers in the next 8 cells to the right to move too and those numbers are always changing. Is there a way to find a certain name and always have the 8 cells next to the name move? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
coping cells | Excel Programming | |||
Querying Info inside excel | Links and Linking in Excel | |||
Macro for coping cells from one worksheet to another | Excel Programming | |||
Trouble Coping Visible Cells | Excel Discussion (Misc queries) | |||
Coping all cells with data. | Excel Programming |