heres the macro that wont work for my membership form
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: heres the macro that wont work for my membership form

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    ActiveCell.Offset(-8, -5).Range("A1").Select
    Range("D9").Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Sheet2").Select
    Range("B1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("membership form").Select
    Range("D11").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Sheet2").Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("membership form").Select
    Range("D13").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Sheet2").Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("membership form").Select
    Range("D14").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Sheet2").Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("membership form").Select
    Range("D15").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Sheet2").Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("membership form").Select
    Range("D17").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Sheet2").Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("membership form").Select
    Range("D19").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Sheet2").Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveCell.Offset(10, 0).Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("membership form").Select
    Range("D19").Select
    Application.CutCopyMode = False
    Range("D19").Select
    Selection.ClearContents
    Range("D17").Select
    Selection.ClearContents
    Range("D15").Select
    Selection.ClearContents
    Range("D14").Select
    Selection.ClearContents
    Range("D13").Select
    Selection.ClearContents
    Range("D11").Select
    Selection.ClearContents
    Range("D9").Select
    Selection.ClearContents
    Range("D9").Select
    End Sub

    it works the first time, but then when i try to use it a second time, to add a second person, i get and error message.

    pleas help me, as i am pulling my hair out

    Bob

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Bob, I don't have time to go right through your code but you have about 80% more lines of code than you need!

    I would say your problems stem from the fact you are refering to the ActiveCell when you select a sheet. This could (and does) change each time the code is run.

    See the code below and just follow the same logic.

    Range("D9").Copy
    Sheets("Sheet2").Range("B1").End(xlDown).Offset _
    (1, 0).PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False

    Sheets("membership form").Range("D11").Copy
    'You had ActiveCell NOT A1, no idea what the activecell is _
    , it would change each time.
    Sheets("Sheet2").Range("A1").Offset(0, 2).Range _
    ("A1").PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com