Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

need your help

  Asked By: Bastet    Date: Jan 18    Category: MS Office    Views: 962
  

I need your help for writing VBA code for the following purpose:

1) I have certain recipe; say about 10 of them
2) Each of them run a few lines, one after another. I am putting them in
distinct blocks, duly separated from each other by a few rows. Within
the block the rows come one after another without any blank rows in between
3) I define name for each block as say, recipe1, recipe2, recipe3 etc.
upto recipe10
4) The above blocks are in one sheet.
5) In another sheet, I want a block my choice to be copied, just by
giving the name of the block. Say, in A1 I will give the name "recipe1".
I realise the copying action has to be triggered. So, I select B1 and
invoke a macro.
6)The code of the macro should be such that it should reference/read the
name given in A1, select the block defined by this name specified in
A1(which is located in another sheet in the same workbook), copy& paste
it in B1 (i.e., Starting from B1 downwards to as many number of rows as
required in that named block)
7)What is the VBA code for carrying out the above macro/function ?
8)Next time, if I change the A1 content to, say, "recipe3", select B1
and invoke the macro/function, the content of the block named as
"recipe3" should get pasted in the rows B1 downward.
8)Is there any other better way to achieve the same results .

I request the experts in this group to advise the code.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Kawkab Mansour     Answered On: Jan 18

If I understand correctly, it shouldn't be difficult.
Now, there's probably better ways of doing this, but given the criteria
you specified... here's what I "heard":
1) Sheet1 has named  ranges (What the range CONTAINS is irrelevant)
2) you want to enter the Range name into cell A1 and have the contents
of the range from Sheet1 copied  to cells A2 through ...

Simple enough.
Right click on the tab name for Sheet2, and select  View Code.
On this sheet, create a change  Event:
'---------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$A$1") Then
Application.EnableEvents = False
Sheets("Sheet2").Range("A2:A20").ClearContents
blkname = Target.Value
Sheets("Sheet1").Range(blkname).Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.EnableEvents = True
End If
End Sub
-----------------------------------
This event looks to see if you changed the contents of A1, then
disables future change events (so it doesn't have to keep executing
this macro  when you're pasting into the sheet!),
then clears the contents and copies the named range to this sheet.

Look like what you want?

I used the PasteSpecial so that you can change the display
on Sheet2 to anything you want, and the whole list can remain simple.

 
Answer #2    Answered By: Christina Ramirez     Answered On: Jan 18

Many thanks for your prompt help. When I read the code  (with my limited
understanding) it looks to me you have addressed my need. But I don't
know how to run  it in my excel file and see whether it fully addresses
my problem. If you don't mind, can you create a sample excel file and
send me.

 
Answer #3    Answered By: Charlie Smith     Answered On: Jan 18

When I read the code  (with my limited
understanding) it looks to me you have addressed my need. But I don't
know how to run  it in my excel file and see whether it fully addresses
my problem. If you don't mind, can you create a sample excel file and
send me.

Can you also advise me on the other better ways you have said possible ?

 
Didn't find what you were looking for? Find more on need your help Or get search suggestion and latest updates.




Tagged: