copy and paste 2 destinations

[mercmannick]

hi

can someone tell me where i am going wrong , trying to paste to 2 diffrent sheets in samework book but diffrent places as code below ...


shtReport.Range("Q3:Q" & iLastRow).Copy
shtFileEXT.Paste Range("I2")
shtFileINT.Paste Range("G2")
Application.CutCopyMode = False

it is pasting to shtFileEXT but not to shtFileINT

Thanks

Merc

[Charlize]

Perhaps this will do the trick ...
shtReport.Range("Q3:Q" & iLastRow).Copy
activesheet.paste destination:=shtFileEXT.Range("I2")
activesheet.paste destination:=shtFileINT.Range("G2")
Application.CutCopyMode = False
Charlize

[mercmannick]

SUPERB THANKS cHARLIZE

[mercmannick]

one more quick question


1200099349 6200004245 1500014102 QM-Lot 102921244

if col g has these figures in how can i modify this code to delete entire row any over 9 digits , with QM-Lot, with PurchRqs but leave Planned and the 9 digit no's........

iLastRow = Range("A2").End(xlDown).Row


For iRow = iLastRow To 2 Step -1
With Cells(iRow, "I")
.Value = DateValue(Replace(.Value, ".", "/"))
If .Value >= Date - 5 Then
Rows(iRow).Delete 'Interior.ColorIndex = 3

End If

End With

Next iRow

and do opposite on diffrent sheet delete anything planned or 9 digits


Thanks

Merc

[Charlize]

Can you try to explain it again because I don't know what you want. Do you want to delete a row when a certain condition is met in column G (which numbers cause the row to be deleted ?) ? Or do you want the cells to be cleared of that row exept for QM-Lot, PurchRqs ... ?

Charlize

[mercmannick]

hi
sorry didnt explain very well

if col g has anything but a 9 digit number or "planned" delete entirerow on shtfileINT

if col g on shfileEXT has a 9 digit no or "planned" delete entirerow

Thanks

Merc

[mdmackillop]

Option Explicit
Option Compare Text

Sub Del9()
Dim Sh As Worksheet, i As Long
Set Sh = Sheets("shtfileINT")
With Sh
For i = LRw(Sh, "G").Row To 1 Step -1
If Len(.Cells(i, "G")) <> 9 Then
If .Cells(i, "G") <> "Planned" Then .Cells(i, "G").EntireRow.Delete
End If
Next
End With
Set Sh = Sheets("shtfileEXT")
With Sh
For i = LRw(Sh, "G").Row To 1 Step -1
If Len(.Cells(i, "G")) = 9 Or .Cells(i, "G") = "Planned" Then
.Cells(i, "G").EntireRow.Delete
End If
Next
End With

End Sub

Function LRw(ByVal LrSh As Worksheet, ByVal Col As Variant, Optional ByVal OSet As Long) As Range
With LrSh
Set LRw = .Cells(.Rows.Count, Col).End(xlUp).Offset(OSet)
End With
End Function

[mercmannick]

mdmackillop

cool , i explained wrong i think 9 digits i mean if any cell in column has more than 9 digits IE: 666666666 is ok : 6666666666 needs deleting

Thanks

Merc

[mdmackillop]

I'm sure you can work out the neccessary change!

[mercmannick]

something like this

Sub Del9()
Dim Sh As Worksheet, i As Long
Set Sh = Sheets("Internal")
With Sh
For i = LRw(Sh, "G").Row To 1 Step -1
If Len(.Cells(i, "G")) >= 699999999 Then
If .Cells(i, "G") = "QM-Lot" Or .Cells(i, "G") = "PurchReq" Then .Cells(i, "G").EntireRow.Delete
End If
Next
End With
Set Sh = Sheets("External")
With Sh
For i = LRw(Sh, "I").Row To 1 Step -1
If Len(.Cells(i, "I")) <= 699999999 Or .Cells(i, "I") = "planned" Then
.Cells(i, "I").EntireRow.Delete
End If
Next
End With

End Sub

Merc

[mdmackillop]

Close.
Len gives the length of the cell
Len(.Cells(i, "G")) >= 699999999
would be a lot of text.
You could use
.Cells(i, "G") > 999999999
or
Len(.Cells(i, "G")) > 9

[Charlize]

Just thinking. Are there words that have more then 9 characters. Should we check with isnumeric or not ? Because when len is more than 9 (number or word ?)

Charlize