Hello Bernie,
thanks alot this works. Just one question, let's say if my sheet where I'm
copying has sections with data example America uses range (A8:Z19), Europe
(A24:Z34) etc, example
A8 W2009
a9:a18(Is there a way to copy those rows here)?
A19 Total
the Cells(Rows.Count, 1).End(xlUp)(2).Select puts rows at end.. Please
advise if you have an idea.
Really appreciate the time you have put in helping me out.
Thanks,
Juan
"Bernie Deitrick" wrote:
> Juan,
>
> We were forgetting to re-select the sheet with the data after the
> pastespecial.
>
> Try it this way:
>
> Sub Test()
> Dim i As Long
> Dim mySht As Worksheet
> Set mySht = ActiveSheet
> For i = 1 To ActiveSheet.UsedRange.Rows.Count
> If mySht.Cells(i, "U") Like "*X*" Then
> mySht.Cells(i, "U").EntireRow.Copy
> Sheets("Sheet2").Select
> Cells(Rows.Count, 1).End(xlUp)(2).Select
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
> False, Transpose:=False
> End If
> Next i
> End Sub
>
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Juan" <(E-Mail Removed)> wrote in message
> news:41CFA0C9-7D65-4201-9540-(E-Mail Removed)...
> > Hello Bernie
> > well this is not coming out right. It's copying just one row but many
> > times. I want to copy to specific range, since the sheet where I want
> > to copy to has sections. I tried using the following:
> > Sheets("Won").Select
> > Rows(Application.InputBox("Enter Row")).Select
> > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
> > _
> > False, Transpose:=False
> > but get a runtime error PasteSpecial method of Range class failed. I tesed
> > by
> > using With Selection.Interior .colorindex and that work, so not sure why
> > the
> > Paste doesn't. I will continue and see if I can get this to work. I really
> > appreciate
> > your help. So if you have any other suggestion please advise.
> > again thanks,
> > Juan
> >
> > "Bernie Deitrick" wrote:
> >
> >> Sorry, Juan! Typo on my part:
> >>
> >> Cells(Rows, Count, 1).End(xlUp)(2).Select
> >>
> >> should be
> >>
> >> Cells(Rows.Count, 1).End(xlUp)(2).Select
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Juan" <(E-Mail Removed)> wrote in message
> >> news:53BE030B-0E02-405A-8BC0-(E-Mail Removed)...
> >> > Hello Bernie,
> >> > thanks but not working.
> >> > I replaced
> >> > Sheets("Sheet2").Select
> >> > Range("A9").Select
> >> > with
> >> > Sheets("Sheet2").Select
> >> > Cells(Rows, Count, 1).End(xlUp)(2).Select
> >> > But Get error" Wrong Number of arguments or invalid property assignment
> >> > Do you have any suggestions?
> >> > thanks
> >> > Juan
> >> >
> >> > "Bernie Deitrick" wrote:
> >> >
> >> >> Baby steps....
> >> >>
> >> >> You need to increment the rows - you are always copying to the same
> >> >> place.
> >> >> Instead of
> >> >>
> >> >> Sheets("Sheet2").Select
> >> >> Range("A9").Select
> >> >>
> >> >> you could use
> >> >>
> >> >> Sheets("Sheet2").Select
> >> >> Cells(Rows,Count,1).End(xlUp)(2).Select
> >> >>
> >> >> and that won't overwrite existing data.
> >> >>
> >> >> HTH,
> >> >> Bernie
> >> >> MS Excel MVP
> >> >>
> >> >>
> >> >> "Juan" <(E-Mail Removed)> wrote in message
> >> >> news:96D9B322-CE75-439D-A949-(E-Mail Removed)...
> >> >> > Hi,
> >> >> > Would like to do the following within a range ex
> >> >> > A9:Z18: If column U has an X , copy all rows which
> >> >> > contain X and paste to sheet 2, else if Column V has X, copy all
> >> >> > rows and
> >> >> > paste
> >> >> > on sheet 3 range A7 Else if Column W has X copy/paste to sheet 4
> >> >> > range A9.
> >> >> > I tried something like below:
> >> >> > Dim i As Long
> >> >> > Range("A9:Z18").Select
> >> >> > For i = 1 To ActiveSheet.UsedRange.Rows.Count
> >> >> > If Cells(i, "U") Like "*X*" Then
> >> >> > Cells(i, "U").EntireRow.Select
> >> >> > Selection.Copy
> >> >> > Sheets("Sheet2").Select
> >> >> > Range("A9").Select
> >> >> > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> >> >> > SkipBlanks:=
> >> >> > _
> >> >> > False, Transpose:=False
> >> >> > ElseIf Cells(i, "V") Like "*X*" Then
> >> >> > Cells(i, "V").EntireRow.Select
> >> >> > Selection.Copy
> >> >> > Sheets("Sheet3").Select
> >> >> > Range("A7").Select
> >> >> > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> >> >> > SkipBlanks:=
> >> >> > _
> >> >> > False, Transpose:=False
> >> >> > etc.
> >> >> >
> >> >> > But seems to only copy one row from Column U. Would appreciate any
> >> >> > help.
> >> >> > Thank you,
> >> >> > JUAN
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Similar Threads
