Excel 97 Macro Problem

Excel 97 Macro Problem

Post by James Shingleto » Sun, 29 Nov 1998 04:00:00



Dear all,
I am trying to write a macro that will copy a specific area of a workbook
and then paste it into another sheet (which is easy) but then make it so I
can go back and change the text in the original workbook and when I press
the button with the macro attached to it, it will paste the new text onto
the other sheet but below where the last one was pasted and then go back,
change the text and the edited again text will be pasted below the two
previous blocks on the different sheet.
Unfortunately I cannot get it to move down and not paste over the old text
that has been pasted.  Has anyone got any ideas of what to insert into the
macro to tell it to paste below the previously pasted text?
Any help would be appreciated!
Many thanks
James Shingleton
 
 
 

Excel 97 Macro Problem

Post by Russell Schul » Sun, 29 Nov 1998 04:00:00



> Subject: Re: Excel 97 Macro Problem

I think we can do better than that.

Quote:> I am trying to write a macro that will copy a specific area of a
> workbook and then paste it into another sheet (which is easy) but then
> make it so I can go back and change the text in the original workbook
> and when I press the button with the macro attached to it, it will
> paste the new text onto the other sheet but below where the last one
> was pasted and then go back, change the text and the edited again text
> will be pasted below the two previous blocks on the different sheet.

wow.  PLEASE read over things you post.  that was VERY unclear.

let's retry:

Quote:> I want to copy values from a fixed area on sheet A to a non-fixed area
> on sheet B.  the area on sheet B would always be just below the previous
> place I pasted values from sheet A.

I think that's clearer.  and it sure is a LOT shorter.

Quote:> Unfortunately I cannot get it to move down and not paste over the old
> text that has been pasted.  Has anyone got any ideas of what to insert
> into the macro to tell it to paste below the previously pasted text?

how would you do it if you were at the keyboard?  would you use the
Control->Down Arrow key?

what do you get in the Macro Recorder if you press Control->Down Arrow?

does that give you enough to finish the job?
--


 
 
 

Excel 97 Macro Problem

Post by Michael Johnso » Wed, 09 Dec 1998 04:00:00


If Control>DownArrow works in the macro recorder, perhaps you can use the
construction Range("A1", Range("A1").End(xlDown) which would drop to the last
cell in the range.

> Dear all,
> I am trying to write a macro that will copy a specific area of a workbook
> and then paste it into another sheet (which is easy) but then make it so I
> can go back and change the text in the original workbook and when I press
> the button with the macro attached to it, it will paste the new text onto
> the other sheet but below where the last one was pasted and then go back,
> change the text and the edited again text will be pasted below the two
> previous blocks on the different sheet.
> Unfortunately I cannot get it to move down and not paste over the old text
> that has been pasted.  Has anyone got any ideas of what to insert into the
> macro to tell it to paste below the previously pasted text?
> Any help would be appreciated!
> Many thanks
> James Shingleton

 
 
 

Excel 97 Macro Problem

Post by Gary Knous » Thu, 24 Dec 1998 04:00:00




Quote:> If Control>DownArrow works in the macro recorder, perhaps you can use the
> construction Range("A1", Range("A1").End(xlDown) which would drop to the
last
> cell in the range.

After reaching the bottom cell of the range, you would want to offset to
avoid pasting over any data: ActiveCell.End(xlDown).Offset(1, 0).Select


> > Dear all,
> > I am trying to write a macro that will copy a specific area of a
workbook
> > and then paste it into another sheet (which is easy) but then make it
so I
> > can go back and change the text in the original workbook and when I
press
> > the button with the macro attached to it, it will paste the new text
onto
> > the other sheet but below where the last one was pasted and then go
back,
> > change the text and the edited again text will be pasted below the two
> > previous blocks on the different sheet.
> > Unfortunately I cannot get it to move down and not paste over the old
text
> > that has been pasted.  Has anyone got any ideas of what to insert into
the
> > macro to tell it to paste below the previously pasted text?
> > Any help would be appreciated!
> > Many thanks
> > James Shingleton

 
 
 

1. Excel 97 macro problem

I have a set of unique codes and values on 1 worksheet [raw data], on the
other worksheets I have a column of queries for example [Group1] laid out
as follows

                Sub Total
        code1   sheets[rawdata], Column (A:A),find "code 1" move 1 cell to the right
copy value
        code 2
        code 3

I need a macro that will search for each code on my query worksheet
[group1], locate it on my [rawdata] worksheet and return the correct value
net to it. The problem is that the codes may not always appear in the
correct order on the raw data. I may also want to remove/add codes to each
query and let the macro retrieve the correct values. Here is my macro as it
stands:

Sheets("group1").Select
    Range("A3").Select
    Selection.Copy
    Sheets("raw data").Select
    Columns("A:A").Select
    Selection.Find(What:=PASTE, After:=ActiveCell, LookIn:=xlValues, LookAt
_  (PASTE does not work what                                              

                                                                                        is the correct syntax)
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
        False).Activate
    ActiveCell.Offset(0, 1).Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("group1").Select
    Range("a3").Select
    ActiveCell.Offset(0, 1).Activate
    ActiveSheet.Paste
End Sub

The macro will work if done manually but in Visual Basic I do not know what
the command is to copy the text from a cell to the Find dialog box. The
other problem with my macro is that after I have pulled the first code
through in the query I need an efficient way of going to the next code
directly below in the column and then repeating the macro. This procedure
needs to be repeated until it reaches a blank cell. i.e no more codes left
in the column on the query (Group1).

I hope that it is clear what I need to achieve, please can anyone help me,

Many thanks,

Jason

2. Where can I find info on SNMPv3

3. OLE code to run word 97 macro from excel 97 macro

4. uk - connecting a palm 3 to a gsm phone

5. Converting Excel 97 to 2000, macro problem

6. UUEncoding and decoding (newer).

7. Excel 97 or Excel 5 to .sd2 macro

8. Hiding other operating systems

9. EXCEL 97 HAS PROBLEM READING EXCEL 5 W95 FILES

10. I Need Some Macro Help Please In Excel 97

11. Excel 97 Macro with If command

12. Excel 97: Macro to list all worksheet names in the active workbook?