Consulting

Results 1 to 18 of 18

Thread: Macro Help

  1. 07-01-2005, 11:19 AM #1
    mercmannick
    mercmannick is offline
    VBAX Tutor mercmannick's Avatar
    Joined
    Jul 2005
    Posts
    214
    Location

    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
    Reply With Quote Reply With Quote
  2. 07-01-2005, 11:44 AM #2
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    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
    Reply With Quote Reply With Quote
  3. 07-01-2005, 11:54 AM #3
    mercmannick
    mercmannick is offline
    VBAX Tutor mercmannick's Avatar
    Joined
    Jul 2005
    Posts
    214
    Location
    wat part did you change xld ?

    and thnx for quick reply
    Reply With Quote Reply With Quote
  4. 07-01-2005, 11:56 AM #4
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    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.
    Reply With Quote Reply With Quote
  5. 07-01-2005, 12:00 PM #5
    mercmannick
    mercmannick is offline
    VBAX Tutor mercmannick's Avatar
    Joined
    Jul 2005
    Posts
    214
    Location
    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
    Reply With Quote Reply With Quote
  6. 07-01-2005, 12:04 PM #6
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    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.
    Reply With Quote Reply With Quote
  7. 07-01-2005, 12:10 PM #7
    mercmannick
    mercmannick is offline
    VBAX Tutor mercmannick's Avatar
    Joined
    Jul 2005
    Posts
    214
    Location
    can i email you the workbook as it too complicated to just put on one bit

    Thanks

    Merc
    Reply With Quote Reply With Quote
  8. 07-01-2005, 12:18 PM #8
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    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.
    Reply With Quote Reply With Quote
  9. 07-01-2005, 12:26 PM #9
    mercmannick
    mercmannick is offline
    VBAX Tutor mercmannick's Avatar
    Joined
    Jul 2005
    Posts
    214
    Location


    thanks Xld
    Reply With Quote Reply With Quote
  10. 07-01-2005, 01:35 PM #10
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    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.
    Reply With Quote Reply With Quote
  11. 07-01-2005, 01:47 PM #11
    mercmannick
    mercmannick is offline
    VBAX Tutor mercmannick's Avatar
    Joined
    Jul 2005
    Posts
    214
    Location
    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
    Reply With Quote Reply With Quote
  12. 07-02-2005, 06:22 AM #12
    mercmannick
    mercmannick is offline
    VBAX Tutor mercmannick's Avatar
    Joined
    Jul 2005
    Posts
    214
    Location
    Anyone point me in right direction on this please

    Merc
    Reply With Quote Reply With Quote
  13. 07-02-2005, 08:58 AM #13
    Norie
    Norie is offline
    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
    Reply With Quote Reply With Quote
  14. 07-02-2005, 09:05 AM #14
    mercmannick
    mercmannick is offline
    VBAX Tutor mercmannick's Avatar
    Joined
    Jul 2005
    Posts
    214
    Location
    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
    Reply With Quote Reply With Quote
  15. 07-02-2005, 09:09 AM #15
    Norie
    Norie is offline
    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.
    Reply With Quote Reply With Quote
  16. 07-02-2005, 09:17 AM #16
    mercmannick
    mercmannick is offline
    VBAX Tutor mercmannick's Avatar
    Joined
    Jul 2005
    Posts
    214
    Location
    what the hell am i doing wrong then
    it paste in fine until clear contents then it gives Error

    Merc
    Reply With Quote Reply With Quote
  17. 07-02-2005, 09:41 AM #17
    Norie
    Norie is offline
    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
    Reply With Quote Reply With Quote
  18. 07-02-2005, 09:49 AM #18
    mercmannick
    mercmannick is offline
    VBAX Tutor mercmannick's Avatar
    Joined
    Jul 2005
    Posts
    214
    Location
    Excellent thnx Norie

    Dont know what i was doing but works a treat now

    1 happy Chappy

    Merc
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules