assigning cell values in formulas

M

M

Please see the posts below. I am having trouble assiging
values to (i,j). I have the desired column value in cell
maint!i5 and the row value in cell maint!i6. I changed
the formula below to reflect this...

i = maint!i5
j = maint!i6

....but am still having trouble. Can anyone help?
Thanks



**************************************************

I'm not sure how you got the location of that cell, but
once you know what it
is, you can just assign values instead of
selecting|copy|pasting.

This might give you an idea:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = 12 'however you got it
j = 10 'however you got it, too

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows,
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub
 
T

Tom Ogilvy

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows, _
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

M said:
Please see the posts below. I am having trouble assiging
values to (i,j). I have the desired column value in cell
maint!i5 and the row value in cell maint!i6. I changed
the formula below to reflect this...

i = maint!i5
j = maint!i6

...but am still having trouble. Can anyone help?
Thanks



**************************************************

I'm not sure how you got the location of that cell, but
once you know what it
is, you can just assign values instead of
selecting|copy|pasting.

This might give you an idea:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = 12 'however you got it
j = 10 'however you got it, too

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows,
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

I am trying to make a worksheet that is set up as follows:
Page 1 (INPUT)is a user input sheet, Page 2 (FORECASTS)
contains the data in database format, and Page 3 (MAINT)
contains the data for dropdown menus.

Page 1 has several drop downs that are linked to lookups,
so users from different locations can see their forecasts
for whatever month they select. I would like to be able to
create a macro that allows the users to update their
forecasts without accessing page 2.

I was able to get the cell address of where I would like
to paste the updated data (MAINT!i3), but excel treats it
as a static location whenever I use the goto function.

Here is what i have so far:

Sheets("INPUT").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'SITE NAT GAS'!R[1]C[- 5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'FORECASTS'!R[1]C[-5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FORECASTS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

Any Ideas?
Click to expand...
Click to expand...
 
M

M

I made the changes and receive a runtime error 13 - type
mistmatch??
-----Original Message-----
Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows, _
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

M said:
Please see the posts below. I am having trouble assiging
values to (i,j). I have the desired column value in cell
maint!i5 and the row value in cell maint!i6. I changed
the formula below to reflect this...

i = maint!i5
j = maint!i6

...but am still having trouble. Can anyone help?
Thanks



**************************************************

I'm not sure how you got the location of that cell, but
once you know what it
is, you can just assign values instead of
selecting|copy|pasting.

This might give you an idea:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = 12 'however you got it
j = 10 'however you got it, too

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows,
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

I am trying to make a worksheet that is set up as follows:
Page 1 (INPUT)is a user input sheet, Page 2 (FORECASTS)
contains the data in database format, and Page 3 (MAINT)
contains the data for dropdown menus.

Page 1 has several drop downs that are linked to lookups,
so users from different locations can see their forecasts
for whatever month they select. I would like to be
Click to expand...
able
to
create a macro that allows the users to update their
forecasts without accessing page 2.

I was able to get the cell address of where I would like
to paste the updated data (MAINT!i3), but excel treats it
as a static location whenever I use the goto function.

Here is what i have so far:

Sheets("INPUT").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'SITE NAT GAS'!R[1]C
Click to expand...
[-
5]"
Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'FORECASTS'!R[1]C[- 5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FORECASTS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

Any Ideas?
Click to expand...
Click to expand...


.
Click to expand...
 
T

Tom Ogilvy

I made a slight modification to a part of the macro you didn't ask about and
it worked fine for me:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub


--
Regards,
Tom Ogilvy

M said:
I made the changes and receive a runtime error 13 - type
mistmatch??
-----Original Message-----
Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows, _
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

M said:
Please see the posts below. I am having trouble assiging
values to (i,j). I have the desired column value in cell
maint!i5 and the row value in cell maint!i6. I changed
the formula below to reflect this...

i = maint!i5
j = maint!i6

...but am still having trouble. Can anyone help?
Thanks



**************************************************

I'm not sure how you got the location of that cell, but
once you know what it
is, you can just assign values instead of
selecting|copy|pasting.

This might give you an idea:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = 12 'however you got it
j = 10 'however you got it, too

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows,
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub


M wrote:

I am trying to make a worksheet that is set up as
follows:
Page 1 (INPUT)is a user input sheet, Page 2 (FORECASTS)
contains the data in database format, and Page 3 (MAINT)
contains the data for dropdown menus.

Page 1 has several drop downs that are linked to lookups,
so users from different locations can see their forecasts
for whatever month they select. I would like to be able
to
create a macro that allows the users to update their
forecasts without accessing page 2.

I was able to get the cell address of where I would like
to paste the updated data (MAINT!i3), but excel treats it
as a static location whenever I use the goto function.

Here is what i have so far:

Sheets("INPUT").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'SITE NAT GAS'!R[1]C [-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'FORECASTS'!R[1]C[- 5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FORECASTS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

Any Ideas?
Click to expand...


.
Click to expand...
Click to expand...
 
T

Tom Ogilvy

That was assuming Maint!I5 contains a number and Maint!I6 contains a
number - since you dimensioned I and J as Long, that was assumed. If not,
then you need to make each a whole number identifying the row and column to
paste to.

--
Regards,
Tom Ogilvy


Tom Ogilvy said:
I made a slight modification to a part of the macro you didn't ask about and
it worked fine for me:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub


--
Regards,
Tom Ogilvy

M said:
I made the changes and receive a runtime error 13 - type
mistmatch??
-----Original Message-----
Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows, _
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

Please see the posts below. I am having trouble assiging
values to (i,j). I have the desired column value in cell
maint!i5 and the row value in cell maint!i6. I changed
the formula below to reflect this...

i = maint!i5
j = maint!i6

...but am still having trouble. Can anyone help?
Thanks



**************************************************

I'm not sure how you got the location of that cell, but
once you know what it
is, you can just assign values instead of
selecting|copy|pasting.

This might give you an idea:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = 12 'however you got it
j = 10 'however you got it, too

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows,
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub


M wrote:

I am trying to make a worksheet that is set up as
follows:
Page 1 (INPUT)is a user input sheet, Page 2 (FORECASTS)
contains the data in database format, and Page 3 (MAINT)
contains the data for dropdown menus.

Page 1 has several drop downs that are linked to lookups,
so users from different locations can see their forecasts
for whatever month they select. I would like to be able
to
create a macro that allows the users to update their
forecasts without accessing page 2.

I was able to get the cell address of where I would like
to paste the updated data (MAINT!i3), but excel treats it
as a static location whenever I use the goto function.

Here is what i have so far:

Sheets("INPUT").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'SITE NAT GAS'!R[1]C [-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'FORECASTS'!R[1]C[- 5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FORECASTS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

Any Ideas?



.
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Tom: Thanks for fixing my typo.

M: Sorry about the typo.



Tom said:
I made a slight modification to a part of the macro you didn't ask about and
it worked fine for me:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub

--
Regards,
Tom Ogilvy

M said:
I made the changes and receive a runtime error 13 - type
mistmatch??
-----Original Message-----
Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows, _
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

Please see the posts below. I am having trouble assiging
values to (i,j). I have the desired column value in cell
maint!i5 and the row value in cell maint!i6. I changed
the formula below to reflect this...

i = maint!i5
j = maint!i6

...but am still having trouble. Can anyone help?
Thanks



**************************************************

I'm not sure how you got the location of that cell, but
once you know what it
is, you can just assign values instead of
selecting|copy|pasting.

This might give you an idea:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = 12 'however you got it
j = 10 'however you got it, too

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows,
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub


M wrote:

I am trying to make a worksheet that is set up as
follows:
Page 1 (INPUT)is a user input sheet, Page 2 (FORECASTS)
contains the data in database format, and Page 3 (MAINT)
contains the data for dropdown menus.

Page 1 has several drop downs that are linked to lookups,
so users from different locations can see their forecasts
for whatever month they select. I would like to be able
to
create a macro that allows the users to update their
forecasts without accessing page 2.

I was able to get the cell address of where I would like
to paste the updated data (MAINT!i3), but excel treats it
as a static location whenever I use the goto function.

Here is what i have so far:

Sheets("INPUT").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'SITE NAT GAS'!R[1]C [-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'FORECASTS'!R[1]C[- 5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FORECASTS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

Any Ideas?



.
Click to expand...
Click to expand...
Click to expand...
 
M

M

Tom - I am still having problems...

I designated those two cells (maint!i5 and i6) as the rows
and columns. for example, if i wanted to paste into cell
b3, the values would read:

maint i5 - 2
maint i6 - 3

the macro is copying my target range and pasting it to the
last active cell in the worksheet rather than the values
in i5 and i6.

thanks for your help, i really appreciate it!
M
-----Original Message-----
That was assuming Maint!I5 contains a number and Maint!I6 contains a
number - since you dimensioned I and J as Long, that was assumed. If not,
then you need to make each a whole number identifying the row and column to
paste to.

--
Regards,
Tom Ogilvy


Tom Ogilvy said:
I made a slight modification to a part of the macro you
Click to expand...
didn't ask about
and
it worked fine for me:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub


--
Regards,
Tom Ogilvy

I made the changes and receive a runtime error 13 - type
mistmatch??
-----Original Message-----
Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows, _
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

Please see the posts below. I am having trouble
assiging
values to (i,j). I have the desired column value in
cell
maint!i5 and the row value in cell maint!i6. I changed
the formula below to reflect this...

i = maint!i5
j = maint!i6

...but am still having trouble. Can anyone help?
Thanks



**************************************************

I'm not sure how you got the location of that cell, but
once you know what it
is, you can just assign values instead of
selecting|copy|pasting.

This might give you an idea:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = 12 'however you got it
j = 10 'however you got it, too

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows,
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub


M wrote:

I am trying to make a worksheet that is set up as
follows:
Page 1 (INPUT)is a user input sheet, Page 2
(FORECASTS)
contains the data in database format, and Page 3
(MAINT)
contains the data for dropdown menus.

Page 1 has several drop downs that are linked to
lookups,
so users from different locations can see their
forecasts
for whatever month they select. I would like to be
able
to
create a macro that allows the users to update their
forecasts without accessing page 2.

I was able to get the cell address of where I would
like
to paste the updated data (MAINT!i3), but excel
treats it
as a static location whenever I use the goto function.

Here is what i have so far:

Sheets("INPUT").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'SITE NAT GAS'!R [1]C
[-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'FORECASTS'!R[1] C[-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FORECASTS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

Any Ideas?



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Dave Peterson

Double check what's in I5 and I6 of that Maint worksheet.

Tom's corrected version of the code worked ok for me.

Did you make any other changes? If yes, you may want to post that part of the
code.
Tom - I am still having problems...

I designated those two cells (maint!i5 and i6) as the rows
and columns. for example, if i wanted to paste into cell
b3, the values would read:

maint i5 - 2
maint i6 - 3

the macro is copying my target range and pasting it to the
last active cell in the worksheet rather than the values
in i5 and i6.

thanks for your help, i really appreciate it!
M
-----Original Message-----
That was assuming Maint!I5 contains a number and Maint!I6 contains a
number - since you dimensioned I and J as Long, that was assumed. If not,
then you need to make each a whole number identifying the row and column to
paste to.

--
Regards,
Tom Ogilvy


Tom Ogilvy said:
I made a slight modification to a part of the macro you
Click to expand...
didn't ask about
and
it worked fine for me:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub


--
Regards,
Tom Ogilvy

I made the changes and receive a runtime error 13 - type
mistmatch??
-----Original Message-----
Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows, _
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

Please see the posts below. I am having trouble
assiging
values to (i,j). I have the desired column value in
cell
maint!i5 and the row value in cell maint!i6. I changed
the formula below to reflect this...

i = maint!i5
j = maint!i6

...but am still having trouble. Can anyone help?
Thanks



**************************************************

I'm not sure how you got the location of that cell, but
once you know what it
is, you can just assign values instead of
selecting|copy|pasting.

This might give you an idea:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = 12 'however you got it
j = 10 'however you got it, too

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows,
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub


M wrote:

I am trying to make a worksheet that is set up as
follows:
Page 1 (INPUT)is a user input sheet, Page 2
(FORECASTS)
contains the data in database format, and Page 3
(MAINT)
contains the data for dropdown menus.

Page 1 has several drop downs that are linked to
lookups,
so users from different locations can see their
forecasts
for whatever month they select. I would like to be
able
to
create a macro that allows the users to update their
forecasts without accessing page 2.

I was able to get the cell address of where I would
like
to paste the updated data (MAINT!i3), but excel
treats it
as a static location whenever I use the goto function.

Here is what i have so far:

Sheets("INPUT").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'SITE NAT GAS'!R [1]C
[-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'FORECASTS'!R[1] C[-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FORECASTS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

Any Ideas?



.
Click to expand...


.
Click to expand...
Click to expand...
 
M

M

Here is what I have...

Range("D10:O10").Select
Sheets("MAINT").Select

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("MAINT").Range("i5").Value
j = Worksheets("MAINT").Range("i6").Value

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

End Sub

-----Original Message-----
Double check what's in I5 and I6 of that Maint worksheet.

Tom's corrected version of the code worked ok for me.

Did you make any other changes? If yes, you may want to post that part of the
code.
Tom - I am still having problems...

I designated those two cells (maint!i5 and i6) as the rows
and columns. for example, if i wanted to paste into cell
b3, the values would read:

maint i5 - 2
maint i6 - 3

the macro is copying my target range and pasting it to the
last active cell in the worksheet rather than the values
in i5 and i6.

thanks for your help, i really appreciate it!
M
-----Original Message-----
That was assuming Maint!I5 contains a number and Maint!
Click to expand...
I6
contains a
number - since you dimensioned I and J as Long, that
Click to expand...
was
assumed. If not,
then you need to make each a whole number identifying
Click to expand...
the
row and column to
paste to.

--
Regards,
Tom Ogilvy


I made a slight modification to a part of the macro
Click to expand...
you
didn't ask about
and
it worked fine for me:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub


--
Regards,
Tom Ogilvy

I made the changes and receive a runtime error 13 - type
mistmatch??
-----Original Message-----
Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows, _
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

Please see the posts below. I am having trouble
assiging
values to (i,j). I have the desired column
Click to expand...
value
in
cell
maint!i5 and the row value in cell maint!i6. I changed
the formula below to reflect this...

i = maint!i5
j = maint!i6

...but am still having trouble. Can anyone help?
Thanks



**************************************************

I'm not sure how you got the location of that cell, but
once you know what it
is, you can just assign values instead of
selecting|copy|pasting.

This might give you an idea:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = 12 'however you got it
j = 10 'however you got it, too

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows,
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub


M wrote:

I am trying to make a worksheet that is set up as
follows:
Page 1 (INPUT)is a user input sheet, Page 2
(FORECASTS)
contains the data in database format, and Page 3
(MAINT)
contains the data for dropdown menus.

Page 1 has several drop downs that are linked to
lookups,
so users from different locations can see their
forecasts
for whatever month they select. I would like
Click to expand...
to
be
able
to
create a macro that allows the users to update their
forecasts without accessing page 2.

I was able to get the cell address of where I would
like
to paste the updated data (MAINT!i3), but excel
treats it
as a static location whenever I use the goto function.

Here is what i have so far:

Sheets("INPUT").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'SITE NAT
Click to expand...
GAS'!R
[1]C
[-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'FORECASTS'!R
Click to expand...
[1]
C[-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FORECASTS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

Any Ideas?



.





.
Click to expand...
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
D

Dave Peterson

The corrected code gets rid of those .select's and copy|paste special.

Try selecting the range you want ("d10:blush:10") on the activesheet and run Tom's
final version.

Tom's line: "Set RngToCopy = Selection"
means you don't have to change the code each time you want to copy something
else--you just have to select it.

But if you really wanted:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = ActiveSheet.Range("d10:blush:10")
'used to be
'Set RngToCopy = Selection

i = Worksheets("MAINT").Range("i5").Value
j = Worksheets("MAINT").Range("i6").Value

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value
End Sub


Here is what I have...

Range("D10:O10").Select
Sheets("MAINT").Select

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("MAINT").Range("i5").Value
j = Worksheets("MAINT").Range("i6").Value

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

End Sub
-----Original Message-----
Double check what's in I5 and I6 of that Maint worksheet.

Tom's corrected version of the code worked ok for me.

Did you make any other changes? If yes, you may want to post that part of the
code.
Tom - I am still having problems...

I designated those two cells (maint!i5 and i6) as the rows
and columns. for example, if i wanted to paste into cell
b3, the values would read:

maint i5 - 2
maint i6 - 3

the macro is copying my target range and pasting it to the
last active cell in the worksheet rather than the values
in i5 and i6.

thanks for your help, i really appreciate it!
M

-----Original Message-----
That was assuming Maint!I5 contains a number and Maint! I6
contains a
number - since you dimensioned I and J as Long, that was
assumed. If not,
then you need to make each a whole number identifying the
row and column to
paste to.

--
Regards,
Tom Ogilvy


I made a slight modification to a part of the macro you
didn't ask about
and
it worked fine for me:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub


--
Regards,
Tom Ogilvy

message
I made the changes and receive a runtime error 13 -
type
mistmatch??
-----Original Message-----
Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows, _
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

message
Please see the posts below. I am having trouble
assiging
values to (i,j). I have the desired column value
in
cell
maint!i5 and the row value in cell maint!i6. I
changed
the formula below to reflect this...

i = maint!i5
j = maint!i6

...but am still having trouble. Can anyone help?
Thanks



**************************************************

I'm not sure how you got the location of that
cell, but
once you know what it
is, you can just assign values instead of
selecting|copy|pasting.

This might give you an idea:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = 12 'however you got it
j = 10 'however you got it, too

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows,
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub


M wrote:

I am trying to make a worksheet that is set up as
follows:
Page 1 (INPUT)is a user input sheet, Page 2
(FORECASTS)
contains the data in database format, and Page 3
(MAINT)
contains the data for dropdown menus.

Page 1 has several drop downs that are linked to
lookups,
so users from different locations can see their
forecasts
for whatever month they select. I would like to
be
able
to
create a macro that allows the users to update
their
forecasts without accessing page 2.

I was able to get the cell address of where I
would
like
to paste the updated data (MAINT!i3), but excel
treats it
as a static location whenever I use the goto
function.

Here is what i have so far:

Sheets("INPUT").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'SITE NAT GAS'!R
[1]C
[-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'FORECASTS'!R [1]
C[-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FORECASTS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

Any Ideas?



.





.
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
Click to expand...
 
M

M

Hey, you guys are great! I really appreciate your help.
-----Original Message-----
The corrected code gets rid of those .select's and copy|paste special.

Try selecting the range you want ("d10:blush:10") on the activesheet and run Tom's
final version.

Tom's line: "Set RngToCopy = Selection"
means you don't have to change the code each time you want to copy something
else--you just have to select it.

But if you really wanted:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = ActiveSheet.Range("d10:blush:10")
'used to be
'Set RngToCopy = Selection

i = Worksheets("MAINT").Range("i5").Value
j = Worksheets("MAINT").Range("i6").Value

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value
End Sub


Here is what I have...

Range("D10:O10").Select
Sheets("MAINT").Select

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("MAINT").Range("i5").Value
j = Worksheets("MAINT").Range("i6").Value

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

End Sub
-----Original Message-----
Double check what's in I5 and I6 of that Maint worksheet.

Tom's corrected version of the code worked ok for me.

Did you make any other changes? If yes, you may want
Click to expand...
to
post that part of the
code.

M wrote:

Tom - I am still having problems...

I designated those two cells (maint!i5 and i6) as the rows
and columns. for example, if i wanted to paste into cell
b3, the values would read:

maint i5 - 2
maint i6 - 3

the macro is copying my target range and pasting it
Click to expand...
to
the
last active cell in the worksheet rather than the values
in i5 and i6.

thanks for your help, i really appreciate it!
M

-----Original Message-----
That was assuming Maint!I5 contains a number and
Click to expand...
Maint!
I6
contains a
number - since you dimensioned I and J as Long, that was
assumed. If not,
then you need to make each a whole number
Click to expand...
identifying
the
row and column to
paste to.

--
Regards,
Tom Ogilvy


I made a slight modification to a part of the
Click to expand...
macro
you
didn't ask about
and
it worked fine for me:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub


--
Regards,
Tom Ogilvy

message
I made the changes and receive a runtime error 13 -
type
mistmatch??
-----Original Message-----
Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = Worksheets("maint").Range("i5").Value
j = Worksheets("maint").Range("i6").Value
Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows, _
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub

message
Please see the posts below. I am having trouble
assiging
values to (i,j). I have the desired column value
in
cell
maint!i5 and the row value in cell maint! i6. I
changed
the formula below to reflect this...

i = maint!i5
j = maint!i6

...but am still having trouble. Can anyone help?
**************************************************

I'm not sure how you got the location of that
cell, but
once you know what it
is, you can just assign values instead of
selecting|copy|pasting.

This might give you an idea:

Option Explicit
Sub testme()

Dim RngToCopy As Range

Dim i As Long
Dim j As Long

Set RngToCopy = Selection

i = 12 'however you got it
j = 10 'however you got it, too

Worksheets("site nat gas").Cells(i, j) _
.Resize(RngToCopy.Rows,
RngToCopy.Columns).Value _
= RngToCopy.Value

End Sub


M wrote:

I am trying to make a worksheet that is set up as
follows:
Page 1 (INPUT)is a user input sheet, Page 2
(FORECASTS)
contains the data in database format, and Page 3
(MAINT)
contains the data for dropdown menus.

Page 1 has several drop downs that are
Click to expand...
linked
to
lookups,
so users from different locations can see their
forecasts
for whatever month they select. I would
Click to expand...
like
to
be
able
to
create a macro that allows the users to update
their
forecasts without accessing page 2.

I was able to get the cell address of where I
would
like
to paste the updated data (MAINT!i3), but excel
treats it
as a static location whenever I use the goto
function.

Here is what i have so far:

Sheets("INPUT").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto Reference:="'SITE NAT GAS'!R
[1]C
[-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SITE NAT GAS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select
Range("D10:O10").Select
Sheets("MAINT").Select
Range("I3").Select
Selection.Copy
Application.Goto
Click to expand...
Reference:="'FORECASTS'!R
[1]
C[-
5]"

Sheets("INPUT").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FORECASTS").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("INPUT").Select
Range("D16").Select

Any Ideas?



.





.


--

Dave Peterson
(e-mail address removed)
.
Click to expand...
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vArray = Sheet2.Range("A1:A" & lr) troubles ahoy 11
Displaing result in Entire worksheet 4
Change a Macro - Copy in Columns instead of copy in Rows 7
How to get the value from a variable 1
Code copies ONLY last j from sheet 9
How do I assign variables 7
Code rounds to whole numbers, need it to stay with decimals 4
Modify working code to expand the output. 5

Top