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?

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?
>
>

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

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

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?
> >
> >

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?
> > >
> > >

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?
> > > >
> > > >