Modify Code to work on Sheets with different names

  • Thread starter Thread starter magmike
  • Start date Start date
M

magmike

I have a macro that inserts a pre-formatted row at the bottom of the section (or actually, just above the next section). However, when I copy the sheet, the new sheet which is named different causes the insertion on the original sheet. How would I modify this code to affect the current sheet, instead of a named sheet?

Application.Goto ThisWorkbook.Worksheets("BVE").Range("Internet_Header"), scroll:=False
Range("Analog_Row").Copy
Selection.Insert
Application.CutCopyMode = False


Thanks in advance,

magmike
 
I have a macro that inserts a pre-formatted row at the bottom of the section (or actually, just above the next section). However, when I copy the sheet, the new sheet which is named different causes the insertion on the original sheet. How would I modify this code to affect the current sheet, instead of a named sheet?

Application.Goto ThisWorkbook.Worksheets("BVE").Range("Internet_Header"), scroll:=False
Range("Analog_Row").Copy
Selection.Insert
Application.CutCopyMode = False
Click to expand...

If the source range is named "Analog_Row" and the target range "Internet_Header", this should work:

Range("Internet_Header").Insert ' Shift:=xlShiftDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Analog_Row").Copy Destination:=Range("Internet_Header").Offset(-1, 0)

The part in the 1st statement after the apostrophe is optional.
Unsolicited free advice: Any VBA code with ".Goto" or "Selection." is bad.
 
If the source range is named "Analog_Row" and the target range "Internet_Header", this should work:



Range("Internet_Header").Insert ' Shift:=xlShiftDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("Analog_Row").Copy Destination:=Range("Internet_Header").Offset(-1, 0)



The part in the 1st statement after the apostrophe is optional.

Unsolicited free advice: Any VBA code with ".Goto" or "Selection." is bad..
Click to expand...

How so? Do teach a novice...
 
I have a macro that inserts a pre-formatted row at the bottom of the
section (or actually, just above the next section). However, when I
copy the sheet, the new sheet which is named different causes the
insertion on the original sheet. How would I modify this code to
affect the current sheet, instead of a named sheet?

Application.Goto
ThisWorkbook.Worksheets("BVE").Range("Internet_Header"),
scroll:=False Range("Analog_Row").Copy Selection.Insert
Application.CutCopyMode = False


Thanks in advance,

magmike
Click to expand...

Any 'pre-formatted template' rows I use reside on the sheet they're
used on, hidden, above the 1st row of data. The code is written similar
to the last example I gave you such that it acts on the current sheet
(ActiveSheet) by default, to unhide the range, copy it, then insert it
at the active cell row so existing rows shift down.

The template rows/cols can be a block (entire section) or just a single
row/col. Rows can be inserted above or below the active cell row, or
added to the bottom of the 'input' area. Cols can be inserted left or
right of the active cell col, or added to the end of the 'input' area.
Template cols are hidden to the left of the 1st col of data.

I suggest you redesign your project sheets so coding is simplified and
easier to maintain. The will also make each copied sheet continue to be
self-sufficient, and NOT establish 'external links' if copied to
another workbook. (Assumes all named ranges are local in scope, as they
should be by default!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Any 'pre-formatted template' rows I use reside on the sheet they're

used on, hidden, above the 1st row of data. The code is written similar

to the last example I gave you such that it acts on the current sheet

(ActiveSheet) by default, to unhide the range, copy it, then insert it

at the active cell row so existing rows shift down.



The template rows/cols can be a block (entire section) or just a single

row/col. Rows can be inserted above or below the active cell row, or

added to the bottom of the 'input' area. Cols can be inserted left or

right of the active cell col, or added to the end of the 'input' area.

Template cols are hidden to the left of the 1st col of data.



I suggest you redesign your project sheets so coding is simplified and

easier to maintain. The will also make each copied sheet continue to be

self-sufficient, and NOT establish 'external links' if copied to

another workbook. (Assumes all named ranges are local in scope, as they

should be by default!)



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

Great advise. I never thought of that. Thank you!
 
Any 'pre-formatted template' rows I use reside on the sheet they're

used on, hidden, above the 1st row of data. The code is written similar

to the last example I gave you such that it acts on the current sheet

(ActiveSheet) by default, to unhide the range, copy it, then insert it

at the active cell row so existing rows shift down.



The template rows/cols can be a block (entire section) or just a single

row/col. Rows can be inserted above or below the active cell row, or

added to the bottom of the 'input' area. Cols can be inserted left or

right of the active cell col, or added to the end of the 'input' area.

Template cols are hidden to the left of the 1st col of data.



I suggest you redesign your project sheets so coding is simplified and

easier to maintain. The will also make each copied sheet continue to be

self-sufficient, and NOT establish 'external links' if copied to

another workbook. (Assumes all named ranges are local in scope, as they

should be by default!)



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

Great advice - I never thought of that. Thank you!
 
Back
Top