Macros

  • Thread starter Thread starter Nicole
  • Start date Start date
N

Nicole

I have a marco I have build on a spreadsheet with several
worksheets. When I use the macro it goes back to the
original place where the marco was built. I have asked in
the past and I know that you need to put the macro in
a "module". I believe it is there and the same thing is
happening. If anyone is willing to assist, I would be
happy to send a copy of the spreadsheet (is virus
protected) I am just a wits end. Thanks in advance for
any assistance.
 
Nicole,

I would ask you to describe it or paste the code if you think
you can describe it well enough, if not I am sending you my
e-mail address... You can send me the workbook, but really
it's better to use this newsgroup so that other people can
benefit from the discussions.

Dan E
 
Thanks for the assistance. I will also email you
directly. Here is my macro:

Sub quickfix()
'
' quickfix Macro
' Macro recorded 9/15/2003 by nnoonchester
'
' Keyboard Shortcut: Ctrl+u
'
Sheets("Chandler, Darrell (2)").Select
Columns("D:E").Select
Range("D4").Activate
Selection.Copy
Sheets("Sheet1 (126)").Select
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Sheets("Chandler, Darrell (2)").Select
Columns("H:H").Select
Range("H4").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1 (126)").Select
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Sheets("Chandler, Darrell (2)").Select
Range("F9:G48").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1 (126)").Select
Range("F9").Select
ActiveSheet.Paste
Sheets("Chandler, Darrell (2)").Select
Range("I10:I48").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1 (126)").Select
Range("I10").Select
ActiveSheet.Paste
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Range("A3").Select
End Sub


What is happening, is the macro was built on sheet 126,
referencing the Darrell Chandler page. When performed on
126, all is well, but I also need to do other pages. When
I go to page 127 and run the macro, it goes back to 126
and re-runs the macro. I want to make it a universal
macro that I can use in a number of workbooks. Once it
works on this spreadsheet, what do I need to do to have
the same macro work in other spreadsheets. Thanks.
 
Sub quickfix()
'
' quickfix Macro
' Macro recorded 9/15/2003 by nnoonchester
'
' Keyboard Shortcut: Ctrl+u
'
CurrSheet = ActiveSheet.Name
Sheets("Chandler, Darrell (2)").Columns("D:E").Copy
Sheets(CurrSheet).Columns("D:D").Insert Shift:=xlToRight
Sheets("Chandler, Darrell (2)").Columns("H:H").Copy
Sheets(CurrSheet).Columns("H:H").Insert Shift:=xlToRight
Sheets("Chandler, Darrell (2)").Range("F9:G48").Copy
Sheets(CurrSheet).Range("F9").Select
ActiveSheet.Paste
Sheets("Chandler, Darrell (2)").Range("I10:I48").Copy
Sheets(CurrSheet).Range("I10").Select
ActiveSheet.Paste
Sheets(CurrSheet).Columns("D:D").EntireColumn.Hidden = True
Range("A3").Select
End Sub

I have assumed that you always want to copy from "Chandler, Darrel (2)"
and paste to whichever sheet your on.

Your main problem was that even though you'd put the code into a module
you had references to "Sheet 1 (126)". I fixed that by recording what page
is started on using CurrSheet = ActiveSheet.Name then when pasting
use that reference.

I also shortened the code.

Dan E
 
Back
Top