PDA

View Full Version : [SOLVED] Macro Help



mercmannick
07-01-2005, 11:19 AM
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

Bob Phillips
07-01-2005, 11:44 AM
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

mercmannick
07-01-2005, 11:54 AM
wat part did you change xld ?

and thnx for quick reply

Bob Phillips
07-01-2005, 11:56 AM
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.

mercmannick
07-01-2005, 12:00 PM
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

Bob Phillips
07-01-2005, 12:04 PM
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.

mercmannick
07-01-2005, 12:10 PM
can i email you the workbook as it too complicated to just put on one bit

Thanks

Merc

Bob Phillips
07-01-2005, 12:18 PM
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.

mercmannick
07-01-2005, 12:26 PM
:friends:

thanks Xld

Bob Phillips
07-01-2005, 01:35 PM
:friends:

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.:)

mercmannick
07-01-2005, 01:47 PM
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

mercmannick
07-02-2005, 06:22 AM
Anyone point me in right direction on this please

Merc

Norie
07-02-2005, 08:58 AM
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

mercmannick
07-02-2005, 09:05 AM
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

Norie
07-02-2005, 09:09 AM
Merc

I downloaded your spreadsheet and it worked fine for me.

mercmannick
07-02-2005, 09:17 AM
what the hell am i doing wrong then :(
it paste in fine until clear contents then it gives Error

Merc

Norie
07-02-2005, 09:41 AM
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

mercmannick
07-02-2005, 09:49 AM
Excellent thnx Norie

Dont know what i was doing but works a treat now

1 happy Chappy

Merc