Solved Copy Moving Rows in Excel

[murray83]

Hi All

i have also posted this on a dedicated excel page but havent had much luck with help or sugestions

so hope you dont mind me posting it here to, here is the link to orginal post

Orignal Post On Excel Help Forum

so i have a file which is attached and once someone press's filter( which im gonna change the text to say filter and print ) i want it to also copuy and paste the colleague and shifts to the other tab in a nice print friendly way, but so far having issues

cheers all

 

[Gasman]

I always start with the Macro Recorder in Excel to get started. Then modify to suit?

A quick Google shows https://www.google.com/search?q=cop...ctMZgBAKoBB2d3cy13aXrIAQjAAQE&sclient=gws-wiz

Which I would use to amend the recorded code.

 

[murray83]

I always start with the Macro Recorder in Excel to get started. Then modify to suit?

i tried that but the row changes so one time it would be row 28 or maybe row 9, all depenidng on which coleague is trying to use it

 

[Gasman]

No, there will be a way to address filtered range?
I'm sure I did this when I was working. I'll see if I can find it on the other computer, but you would be likely be quicker looking at the links.

 

[Gasman]

Just found this, which is what I was thinking about.

 Application.StatusBar = "Copying required data to new sheet....."
    lLastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:W" & lLastRow).SpecialCells(xlCellTypeVisible).Copy
    Workbooks.Add
    strNewName = ActiveWorkbook.Name
    With Selection
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    End With

HTH

 

[murray83]

test this one.

EDIT: uploaded new version

ok i tried it, but it didnt seem to copy the data, all it did was hide the filtred bit, which i would like still visible no need to hide that after it has copied

but it didnt copy anythinig accross. did it work for you ?

edit below

ok so i looked at the code and change one thing

your code with in mine below and i just chaned this

'arnelgp
    'using macro recorder
    ActiveSheet.Range("$A$8:$AE$38").AutoFilter Field:=3, Criteria1:= _
        "=" & shtname & "*"

to this

'arnelgp
    'using macro recorder
    ActiveSheet.Range("$A$8:$AE$38").AutoFilter Field:=1, Criteria1:= _
        "=" & shtname & "*"

and bam worked a charm so thanks a million

Sub ToPrint()
    Dim shtname As String
    
    shtname = InputBox("Please Put Your Name In Here.", "Print For Which Colleague?")
    If shtname = "" Then Exit Sub
    
    'ActiveSheet.Unprotect Password:="d@ventry2015"
    
    
    'arnelgp
    'using macro recorder
    ActiveSheet.Range("$A$8:$AE$38").AutoFilter Field:=3, Criteria1:= _
        "=" & shtname & "*"
    Dim rng As Range
    Dim tfFound As Boolean
    With ActiveSheet
        .Range("C8").Select
        Set rng = .Cells.Find(What:=shtname, After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        tfFound = Not (rng Is Nothing)
        If tfFound Then
            rng.Activate
            Dim i As Integer
            With Sheet6
                For i = -1 To 7 Step 1
                    .Range(Chr(66 + i) & "3").Value = rng.Offset(, i).Value
                Next
                For i = 8 To 14 Step 1
                    .Range(Chr(59 + i) & "7").Value = rng.Offset(, i).Value
                Next
                For i = 15 To 21 Step 1
                    .Range(Chr(52 + i) & "11").Value = rng.Offset(, i).Value
                Next
                For i = 22 To 28 Step 1
                    .Range(Chr(45 + i) & "15").Value = rng.Offset(, i).Value
                Next
                .Activate
            End With
        End If
    End With
    'end of code
    'ActiveSheet.Protect Password:="d@ventry2015"
End Sub

 

[arnelgp]

but it didnt copy anythinig accross. did it work for you ?

no it did not do anything.

you have 2 sample there.
try searching "Murray" or "Adam" (without quote).

 

[murray83]

no it did not do anything.

see my edited post

cheers for the help