Change to existing macro
 

Hi,
I have a macro (see below) that the copies and pastes 5
arrays of assumptions, line by line, into the assumptions
input sheet of a model. It then records the output of the
model based on that particular set of assumptions. Now, I
would like to make the following change to the macro, but
I am not certain how:

The five arrays of assumptions are in sheet1 columns N
through Q. I would like be able to manipulate the macro,
so column N variables goes into a specific cell (Let's say
B31), column O goes into another specific cell independent
of column N (Let's say B61), and so forth. Right now the
macro simply copies and pastes a range of cells.

I hope this makes sense.

Thanks,
Jerry


Sub Model()

Dim cell As Range

For Each cell In Worksheets("sheet1").Range("N6:N325")
cell.Resize(1, 5).Copy

With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub

Change to existing macro
 

Sub Model()
Dim varr as variant
Dim i as long
Dim c as Range
Dim cell As Range
varr = Array("B30","B61","C12","R32","M13")
For Each cell In Worksheets("sheet1").Range("N6:N325")
i = lbound(varr)
for each c in cell.Resize(1, 5).Copy
worksheets("sheet2").range(varr(i)).Value = c.Value
i = i + 1
next
With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub

--
Regards,
Tom Ogilvy

Jerry wrote in message
...
Hi,
I have a macro (see below) that the copies and pastes 5
arrays of assumptions, line by line, into the assumptions
input sheet of a model. It then records the output of the
model based on that particular set of assumptions. Now, I
would like to make the following change to the macro, but
I am not certain how:

The five arrays of assumptions are in sheet1 columns N
through Q. I would like be able to manipulate the macro,
so column N variables goes into a specific cell (Let's say
B31), column O goes into another specific cell independent
of column N (Let's say B61), and so forth. Right now the
macro simply copies and pastes a range of cells.

I hope this makes sense.

Thanks,
Jerry


Sub Model()

Dim cell As Range

For Each cell In Worksheets("sheet1").Range("N6:N325")
cell.Resize(1, 5).Copy

With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub

Change to existing macro
 

Thanks for your assistance. When I run the code, I get the
following error message: Run-time error '424': Object
required
What causes this?
Thank you for your help.

-----Original Message-----
Sub Model()
Dim varr as variant
Dim i as long
Dim c as Range
Dim cell As Range
varr = Array("B30","B61","C12","R32","M13")
For Each cell In Worksheets("sheet1").Range("N6:N325")
i = lbound(varr)
for each c in cell.Resize(1, 5).Copy
worksheets("sheet2").range(varr(i)).Value =
c.Value
i = i + 1
next
With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub

--
Regards,
Tom Ogilvy

Jerry wrote in
message
...
Hi,
I have a macro (see below) that the copies and pastes 5
arrays of assumptions, line by line, into the
assumptions
input sheet of a model. It then records the output of
the
model based on that particular set of assumptions. Now,
I
would like to make the following change to the macro,
but
I am not certain how:

The five arrays of assumptions are in sheet1 columns N
through Q. I would like be able to manipulate the macro,
so column N variables goes into a specific cell (Let's
say
B31), column O goes into another specific cell
independent
of column N (Let's say B61), and so forth. Right now the
macro simply copies and pastes a range of cells.

I hope this makes sense.

Thanks,
Jerry


Sub Model()

Dim cell As Range

For Each cell In Worksheets("sheet1").Range
("N6:N325")
cell.Resize(1, 5).Copy

With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub


.

Change to existing macro
 

Trying to edit your code in an email causes it.

Sub Model()
Dim varr As Variant
Dim i As Long
Dim c As Range
Dim cell As Range
varr = Array("B30", "B61", "C12", "R32", "M13")
For Each cell In Worksheets("sheet1").Range("N6:N325")
i = LBound(varr)
For Each c In cell.Resize(1, 5)
Worksheets("sheet2").Range(varr(i)).Value = c.Value
i = i + 1
Next
With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues, Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub


--
Regards,
Tom Ogilvy

Jerry wrote in message
...
Thanks for your assistance. When I run the code, I get the
following error message: Run-time error '424': Object
required
What causes this?
Thank you for your help.

-----Original Message-----
Sub Model()
Dim varr as variant
Dim i as long
Dim c as Range
Dim cell As Range
varr = Array("B30","B61","C12","R32","M13")
For Each cell In Worksheets("sheet1").Range("N6:N325")
i = lbound(varr)
for each c in cell.Resize(1, 5).Copy
worksheets("sheet2").range(varr(i)).Value =
c.Value
i = i + 1
next
With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub

--
Regards,
Tom Ogilvy

Jerry wrote in
message
...
Hi,
I have a macro (see below) that the copies and pastes 5
arrays of assumptions, line by line, into the
assumptions
input sheet of a model. It then records the output of
the
model based on that particular set of assumptions. Now,
I
would like to make the following change to the macro,
but
I am not certain how:

The five arrays of assumptions are in sheet1 columns N
through Q. I would like be able to manipulate the macro,
so column N variables goes into a specific cell (Let's
say
B31), column O goes into another specific cell
independent
of column N (Let's say B61), and so forth. Right now the
macro simply copies and pastes a range of cells.

I hope this makes sense.

Thanks,
Jerry


Sub Model()

Dim cell As Range

For Each cell In Worksheets("sheet1").Range
("N6:N325")
cell.Resize(1, 5).Copy

With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub


.

Change to existing macro
 

I suppose that this means that, unfortunately, there is no
easy solution with that code?

-----Original Message-----
Trying to edit your code in an email causes it.

Sub Model()
Dim varr As Variant
Dim i As Long
Dim c As Range
Dim cell As Range
varr = Array("B30", "B61", "C12", "R32", "M13")
For Each cell In Worksheets("sheet1").Range("N6:N325")
i = LBound(varr)
For Each c In cell.Resize(1, 5)
Worksheets("sheet2").Range(varr(i)).Value =
c.Value
i = i + 1
Next
With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub


--
Regards,
Tom Ogilvy

Jerry wrote in
message
...
Thanks for your assistance. When I run the code, I get
the
following error message: Run-time error '424': Object
required
What causes this?
Thank you for your help.

-----Original Message-----
Sub Model()
Dim varr as variant
Dim i as long
Dim c as Range
Dim cell As Range
varr = Array("B30","B61","C12","R32","M13")
For Each cell In Worksheets("sheet1").Range
("N6:N325")
i = lbound(varr)
for each c in cell.Resize(1, 5).Copy
worksheets("sheet2").range(varr(i)).Value
=
c.Value
i = i + 1
next
With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub

--
Regards,
Tom Ogilvy

Jerry wrote in
message
...
Hi,
I have a macro (see below) that the copies and
pastes 5
arrays of assumptions, line by line, into the
assumptions
input sheet of a model. It then records the output of
the
model based on that particular set of assumptions.
Now,
I
would like to make the following change to the macro,
but
I am not certain how:

The five arrays of assumptions are in sheet1 columns
N
through Q. I would like be able to manipulate the
macro,
so column N variables goes into a specific cell
(Let's
say
B31), column O goes into another specific cell
independent
of column N (Let's say B61), and so forth. Right now
the
macro simply copies and pastes a range of cells.

I hope this makes sense.

Thanks,
Jerry


Sub Model()

Dim cell As Range

For Each cell In Worksheets("sheet1").Range
("N6:N325")
cell.Resize(1, 5).Copy

With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range
("B10").Value
cell.Offset(0, -3).Value = .Range
("B11").Value
cell.Offset(0, -2).Value = .Range
("B12").Value

End With


Next

End Sub


.



.

Change to existing macro
 

Other than I corrected the error and it runs fine, I don't know what you
mean.

What I meant was I edited your code in the email and forgot to remove the
..Copy on the end of one of your statements. Of course, this would be
easily detected when the error occurred when you tried to run it and easily
corrected. So the solution was easy - remove the residual code that should
have been removed previously.
--
Regards,
Tom Ogilvy

wrote in message
...
I suppose that this means that, unfortunately, there is no
easy solution with that code?

-----Original Message-----
Trying to edit your code in an email causes it.

Sub Model()
Dim varr As Variant
Dim i As Long
Dim c As Range
Dim cell As Range
varr = Array("B30", "B61", "C12", "R32", "M13")
For Each cell In Worksheets("sheet1").Range("N6:N325")
i = LBound(varr)
For Each c In cell.Resize(1, 5)
Worksheets("sheet2").Range(varr(i)).Value =
c.Value
i = i + 1
Next
With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub


--
Regards,
Tom Ogilvy

Jerry wrote in
message
...
Thanks for your assistance. When I run the code, I get
the
following error message: Run-time error '424': Object
required
What causes this?
Thank you for your help.

-----Original Message-----
Sub Model()
Dim varr as variant
Dim i as long
Dim c as Range
Dim cell As Range
varr = Array("B30","B61","C12","R32","M13")
For Each cell In Worksheets("sheet1").Range
("N6:N325")
i = lbound(varr)
for each c in cell.Resize(1, 5).Copy
worksheets("sheet2").range(varr(i)).Value
=
c.Value
i = i + 1
next
With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub

--
Regards,
Tom Ogilvy

Jerry wrote in
message
...
Hi,
I have a macro (see below) that the copies and
pastes 5
arrays of assumptions, line by line, into the
assumptions
input sheet of a model. It then records the output of
the
model based on that particular set of assumptions.
Now,
I
would like to make the following change to the macro,
but
I am not certain how:

The five arrays of assumptions are in sheet1 columns
N
through Q. I would like be able to manipulate the
macro,
so column N variables goes into a specific cell
(Let's
say
B31), column O goes into another specific cell
independent
of column N (Let's say B61), and so forth. Right now
the
macro simply copies and pastes a range of cells.

I hope this makes sense.

Thanks,
Jerry


Sub Model()

Dim cell As Range

For Each cell In Worksheets("sheet1").Range
("N6:N325")
cell.Resize(1, 5).Copy

With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range
("B10").Value
cell.Offset(0, -3).Value = .Range
("B11").Value
cell.Offset(0, -2).Value = .Range
("B12").Value

End With


Next

End Sub


.



.