Consulting

Results 1 to 18 of 18

Thread: Macro Help

  1. #1

    Macro Help

    Application.ScreenUpdating = False 
    Sheets("Output Tracker").Select 
    Range("B17").Select 
    Selection.Copy 
    Sheets("HTB").Select 
    ActiveSheet.ChartObjects("Chart 1").Activate 
    ActiveWindow.Visible = False 
    Windows("S15 Load Capacity Master.xls").Activate 
    If Sheets("HTB").Range("B6").Value <> "" Then 
    Sheets("HTB").Range("B2:B6").ClearContents 
    Else 
    For i = 2 To 6 
    If Sheets("HTB").Range("B" & i).Value = "" Then 
    Sheets("HTB").Range("B" & i).Select 
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ 
    False, Transpose:=False 
    Application.CutCopyMode = False 
    Selection.NumberFormat = "0.00%" 
    Exit For 
    End If 
    Next i 
    End If 
    ChDir "H:\" 
    Workbooks.OpenText Filename:="H:\S15.XLS", Origin:=xlWindows, StartRow:= _ 
    1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ 
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ 
    , Space:=False, Other:=False, FieldInfo:=Array(1, 1) 
    Cells.Select 
    Selection.Copy 
    Windows("S15 Load Capacity Master.xls").Activate 
    Sheets("MCPB").Select 
    Cells.Select 
    ActiveSheet.Paste 
    Range("C7").Select 
    Application.CutCopyMode = False 
    ActiveCell.FormulaR1C1 = "=-2.9+MID(RC[-1],5,8)" 
    ActiveCell.FormulaR1C1 = "=-2.9+MID(RC[-1],5,8)" 
    Selection.ClearContents 
    Range("C7").Select 
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],5,8)" 
    Selection.AutoFill Destination:=Range("C7:C6555"), Type:=xlFillDefault 
    Range("C7:C6555").Select 
    Sheets("Output Tracker").Select 
    Windows("s15.xls").Activate 
    ActiveWorkbook.Close 
    Application.ScreenUpdating = True 
    End Sub


    sussed out how to stop writing in two lots on a monday..............

    but now after clearcontents it dosent paste into B2 like it shud you have to rerun macro

    any help would be internally gratefull

    Regards

    Merc

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Maybe


    Application.ScreenUpdating = False
    Sheets("Output Tracker").Select
    Range("B17").Select
    Selection.Copy
    Sheets("HTB").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveWindow.Visible = False
    Windows("S15 Load Capacity Master.xls").Activate
    If Sheets("HTB").Range("B6").Value <> "" Then
        Sheets("HTB").Range("B2:B6").ClearContents
    End If
    For i = 2 To 6
        If Sheets("HTB").Range("B" & i).Value = "" Then
            Sheets("HTB").Range("B" & i).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
            Application.CutCopyMode = False
            Selection.NumberFormat = "0.00%"
            Exit For
        End If
    Next i
    ChDir "H:\"
    Workbooks.OpenText Filename:="H:\S15.XLS", Origin:=xlWindows, StartRow:= _
    1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=False, FieldInfo:=Array(1, 1)
    Cells.Select
    Selection.Copy
    Windows("S15 Load Capacity Master.xls").Activate
    Sheets("MCPB").Select
    Cells.Select
    ActiveSheet.Paste
    Range("C7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=-2.9+MID(RC[-1],5,8)"
    ActiveCell.FormulaR1C1 = "=-2.9+MID(RC[-1],5,8)"
    Selection.ClearContents
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],5,8)"
    Selection.AutoFill Destination:=Range("C7:C6555"), Type:=xlFillDefault
    Range("C7:C6555").Select
    Sheets("Output Tracker").Select
    Windows("s15.xls").Activate
    ActiveWorkbook.Close
    Application.ScreenUpdating = True
    End Sub

  3. #3
    wat part did you change xld ?

    and thnx for quick reply

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by mercmannick
    wat part did you change xld ?
    I took out the else part of the If test when you clear the cells, so that it always drops down into thge next part, not ignoring it when the cells need clearing.

  5. #5
    Sheets("HTB").Range("B" & i).PasteSpecial _
                    Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                    xlNone, SkipBlanks:=False, Transpose:=False
    before it clears the macro stops on this ?

    any sugestions xld

    Thanks

    Merc

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by mercmannick
    Sheets("HTB").Range("B" & i).PasteSpecial _
    Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

    before it clears the macro stops on this ?

    any sugestions xld
    Merc,

    Can you post a workbook with your data? It is a bit complex to work blind on.

  7. #7
    can i email you the workbook as it too complicated to just put on one bit

    Thanks

    Merc

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by mercmannick
    can i email you the workbook as it too complicated to just put on one bit
    Sure. you can add attachments in the forum though.

  9. #9


    thanks Xld

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by mercmannick


    thanks Xld
    Can you give me a few details? How/what do I run, and what exactlyt happens/should happen ? It is a bit complex to try and figure on my own.

  11. #11
    all i need is on sheet htb to be able to paste the percentage from sheet output tracker to b2,then b3 b4 etc , when i have this sorted i will add code to the main update macro so it pastes into htb automatically with the macro, if u look at vba code for Sub S03F()

    you will c what it does it is only when all the b2 - e6 is full it shud clear the contents and start again by pasting into b2 etc etc

    but at clear contents it falls over and i cant figure out why ?

    hope this helps

    Merc
    Last edited by mercmannick; 07-01-2005 at 01:51 PM. Reason: missing text

  12. #12
    Anyone point me in right direction on this please

    Merc

  13. #13
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Try this
    Sheets("Output Tracker").Range("B17:E17").Copy
    	Sheets("HTB").Range("B2").PasteSpecial Paste:=xlPasteValues
    	
    	If Sheets("HTB").Range("B6").Value <> "" Then
    	   Sheets("HTB").Range("B2:E7").ClearContents
    	End If
    	For i = 2 To 6
    		If Sheets("HTB").Range("B" & i).Value = "" Then
    			Sheets("HTB").Range("B" & i).PasteSpecial Paste:=xlPasteValues
    			Exit For
    		End If
    	Next i

  14. #14
    no still falls over at

    Sheets("HTB").Range("B" & i).PasteSpecial Paste:=xlPasteValues
    And it pastes into b2 and b3 if u click end then rerun macro




    Merc

  15. #15
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Merc

    I downloaded your spreadsheet and it worked fine for me.

  16. #16
    what the hell am i doing wrong then
    it paste in fine until clear contents then it gives Error

    Merc

  17. #17
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    When is it actually failing?

    Which button on which sheet are you using?

    Try this
    Application.ScreenUpdating = False
    Sheets("Output Tracker").Range("B17:E17").Copy
    Sheets("HTB").Range("B2").PasteSpecial Paste:=xlPasteValues
    
    If Sheets("HTB").Range("B6").Value <> "" Then
        Sheets("HTB").Range("B2:E7").ClearContents
        Sheets("Output Tracker").Range("B17:E17").Copy
    End If
    
    For i = 2 To 6
        If Sheets("HTB").Range("B" & i).Value = "" Then
            Sheets("HTB").Range("B" & i).PasteSpecial Paste:=xlPasteValues
            Exit For
        End If
    Next i

  18. #18
    Excellent thnx Norie

    Dont know what i was doing but works a treat now

    1 happy Chappy

    Merc

Posting Permissions

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