search/copy/paste macro
 

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?

search/copy/paste macro
 

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