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?

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?

--

Dave Peterson