Code for current sheet selected...

I have the following code generated from a macro:

Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B3").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("CP093004").Select
Range("B3").Select
Selection.Copy
Sheets("CP031005").Select
Range("B3").Select
...

and, I want to run the macro from a newly imported sheet, so, what can I put
in place of

Sheets("CP031005").Select

so that it selects the current sheet from which the macro was called?

My thanks in advance for any help. Brad

Brad

Try something like this.

Please Login or Register  to view this content.

do you want the macro to add the new sheet as well, its a bit vague

regards

Johny5

John, no, I actually am importing a text file into Excel where it shows up
as an unformatted affair. What I am actually doing in the macro is largely
a series of formatting things, but, I need to copy a formula from another
sheet and drop it into the B column of the current sheet.

I suppose my problem is that when I recorded the macro, it wrote the code in
such a way that it wants to select a particular sheet name -- the name of
the sheet when I recorded the macro -- and, I just want it to refer to the
current sheet from which I run the macro.

Confusing a bit I know,,, hope this helps explain. Brad

<[email protected]> wrote in message
news:[email protected]...
> do you want the macro to add the new sheet as well, its a bit vague
>
> regards
>
> Johny5
>

Easy way

Set thisSheet = Activesheet
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B3").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("CP093004").Select
Range("B3").Select
Selection.Copy
Sheets("CP031005").Select
Range("B3").Select
thisSheet.Activate

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brad" <[email protected]> wrote in message
news:[email protected]...
> I have the following code generated from a macro:
>
> Columns("B:B").Select
> Selection.Insert Shift:=xlToRight
> Range("B3").Select
> ActiveWindow.ScrollWorkbookTabs Sheets:=-1
> Sheets("CP093004").Select
> Range("B3").Select
> Selection.Copy
> Sheets("CP031005").Select
> Range("B3").Select
> ...
>
> and, I want to run the macro from a newly imported sheet, so, what can I
put
> in place of
>
> Sheets("CP031005").Select
>
> so that it selects the current sheet from which the macro was called?
>
> My thanks in advance for any help. Brad
>
>
>