Results 1 to 8 of 8

Thread: Macro Recording

  1. #1
    New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    HI

    Recorded macro not updated in particular sheet, it jumping to some other sheet (Button which we assign Macro)


    VVK

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm sorry, I don't understand your question, could you tell us more about what is (not) happening?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi pieterse,

    Good Morning.

    We recorded a macro for repetative function, it will not update the data in a particular sheet, data is moving to next sheet where we assign the macro button.

    it suppose to update the data in working sheet, but data is updating in RUN sheet.

    can you give the solution to this problem.

    VVK
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You need to change your macro so Excel understands what sheet you want to work with in the code.

    VBA does not require you to select sheets and cells to work with them.

    Your original code starts with this:

    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:U").Select
    Selection.Delete Shift:=xlToLeft
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[-4])"
    Range("E3").Select
    Columns("E:E").EntireColumn.AutoFit
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E1176"), Type:=xlFillDefault
    Range("E2:E1176").Select
    Selection.Copy
    ActiveWindow.ScrollRow = 1138
    .
    .
    .
    ActiveWindow.ScrollRow = 1
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Columns("D:F").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select
    Sheets("GC").Select
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:I").Select
    Selection.Delete Shift:=xlToLeft
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[-4])"
    Range("E3").Select
    Columns("E:E").EntireColumn.AutoFit
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E631"), Type:=xlFillDefault
    Range("E2:E631").Select
    Selection.Copy
    ActiveWindow.ScrollRow = 598
    .
    .
    .
    ActiveWindow.ScrollRow = 1
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Columns("D:F").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select


    Modify that so it becomes this:

    With Sheets("Working")
    .Columns("A:B").Delete Shift:=xlToLeft
    .Columns("B:F").Delete Shift:=xlToLeft
    .Columns("C:U").Delete Shift:=xlToLeft
    .Range("E2").FormulaR1C1 = "=TRIM(RC[-4])"
    .Columns("E:E").EntireColumn.AutoFit
    .Range("E2").AutoFill Destination:=.Range("E2:E1176"), Type:=xlFillDefault
    .Range("E2:E1176").Copy
    .Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    .Columns("D:F").Delete Shift:=xlToLeft
    .Columns("A:A").Delete Shift:=xlToLeft
    .Columns("B").Delete Shift:=xlToLeft
    .Columns("C:I").Delete Shift:=xlToLeft
    .Range("E2").FormulaR1C1 = "=TRIM(RC[-4])"
    .Columns("E:E").EntireColumn.AutoFit
    .Range("E2").AutoFill Destination:=.Range("E2:E631"), Type:=xlFillDefault
    .Range("E2:E631").Copy
    .Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    .Columns("D:F").Delete Shift:=xlToLeft
    End With

    I leave it as an excersize for you to do the remaining part of the code :-)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Trimming Operation is working well its assigned to macro-1 (trimming)

    pls can you verify the Macro-2 (Stocks) is not updating in working sheet, its updating in RUN sheet.

    vvk

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your code does not work because nowhere in the code you explicitly tell VBA which worksheet it needs to work on.

    At the start of macro2 add this command:
    Sheets("WhicheverSheetItShouldWorkWith").Activate

    NB: Remove all the lines which look like this:
    ActiveWindow.ScrollRow = SomeNumber
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi pieterse,

    Command Included now its working.

    Thanx .

    VVK

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excellent.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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