1

Question: How can I copy cells B through Y for all rows based on a unique ID in Column A? I can't copy the entire row, just that range.

screenshot with music data

screenshot with music data

My method: I copy the sheet > filter > copy visible cells > delete visible cells > move to next filter entry and repeat.

The problem: Some maps (esp. small ones) don't copy. I'd also like a simpler way to do it. I made this file for me just to concat XML together. But now several Mac users are using it and having more problems due to this part of the code.

Sub mapmaker()

Dim LR As Long
Dim Rng As Range
Dim mws As Worksheet

Dim c As Range, i As Long
Dim lastrow As Long, nextrow As Long
Dim cn As Range, rn As Range
Dim output As String

' duplicate sheet with data to copy, set range, see if range contains data

Set rn = Sheets("XML").Range("DR1:DR505")
Application.ScreenUpdating = False
Sheets("Master").Copy After:=Sheets("Master")
ActiveSheet.Name = "MstrCpy"
Sheets("MstrCpy").Select
Range("A1").Select
Set Rng = Worksheets("MstrCpy").Range("A2:A1000")
Set mws = Worksheets("MstrCpy")
If WorksheetFunction.CountA(Rng) < 1 Then Exit Sub

With mws
              
     ' count number of entries
    i = WorksheetFunction.CountA(Rng)
     'turn off any previous filters
    If .AutoFilterMode = True Then .AutoFilterMode = False
     
     'loop through cells, filter and copy results
    For Each c In .Range("A2:A" & i)
        Sheets("XML").Range("A8:X1000").ClearContents
        Sheets("MstrCpy").Select
        
        nextrow = Sheets("MstrCpy").Cells(Rows.Count, "A").End(xlUp).Row
        lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        
        .Range("A1").AutoFilter Field:=1, Criteria1:="=" & Range("A2").Value
        .Range("B2:Y" & lastrow).SpecialCells(xlCellTypeVisible).Copy Sheets("XML").Range("A8")
        
        .Range("A2").Copy Sheets("XML").Range("B4")
        .Range("B2:Y" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Sheets("MstrCpy").Select
        .AutoFilterMode = False
        Sheets("XML").Select

        output1 = Range("BB2").Value
        output2 = Range("BB3").Value
        
        Open output1 For Output As #1
        For Each cell In rn
            Print #1, cell.Value & Chr(10)
        Next
        Close
        Name output1 As output2
        Sheets("MstrCpy").Select
    Next c
     
     'turn off filtering
    .AutoFilterMode = False
End With

Application.DisplayAlerts = False
Sheets("MstrCpy").Delete
Sheets("Master").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

I tend to write things less backwards now. But I don't know VBA well enough to figure this out and my attempts have failed. The similar questions I've seen don't seem to be useful here. Any help would be greatly appreciated and would aid more people than just myself. Thank you!

-Sean

Community
  • 1
  • 1
film composer
  • 25
  • 3
  • [Don't use `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). That may be causing some of the issues. Also, if you step through your code with `F8`, does that help to pinpoint where/when the macro doesn't do as expected? – BruceWayne Apr 13 '18 at 17:12
  • I just read a thread about it... didn't realize ".Select" was such a problem. Though I don't see how selecting a worksheet would cause a filter issue. Either way I'll try to avoid it. I did use F8 and was still completely lost. Admittedly, I'm a coding idiot. I tend to just find a way to do what I need and go with it. When I see a cleaner way to do it, I'll opt for that cause I like reliability as much as anyone else. I just don't often see how. As I said, the ".Select" thing is a mystery to me. But I'll play with that. – film composer Apr 13 '18 at 18:07

0 Answers0