Consulting

Results 1 to 6 of 6

Thread: Change the copy order in this VBA code

  1. #1

    Change the copy order in this VBA code

    Hello all,

    I wrote this code with a lot of headaches and help, but I am at a problem. I was not aware that I would need the columns to copy to the new sheet in a specific order. Right now the code allows me to put an x on any sheet and that copies columns A,B,D,F of the row marked. I need the order to be A,B,F,D. I have tried changing the order in the range, as well as setting cells equal, but I am in over my head. Here is the code:

    Sub Transfer()
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim rng As Range
        Dim strAddress As String
        Dim s As Long
        Dim t As Long
        Application.ScreenUpdating = False
        Set wshT = Worksheets("Inspection")
        t = wshT.Range("B" & wshT.Rows.Count).End(xlUp).Row
        If t < 4 Then t = 4
        For Each wshS In Worksheets
            If wshS.Name <> wshT.Name Then
                Set rng = wshS.Range("J:J").Find(What:="X", LookAt:=xlWhole)
                If Not rng Is Nothing Then
                    strAddress = rng.Address
                    Do
                        t = t + 1
                        s = rng.Row
                        wshS.Range("A" & s & ",B" & s & ",D" & s & ",F" & s).Copy _
                            Destination:=wshT.Range("B" & t)
                        ' Optional: clear the "X"
                        wshS.Range("J" & s).ClearContents
                        Set rng = wshS.Range("J:J").FindNext(After:=rng)
                        If rng Is Nothing Then Exit Do
                    Loop Until rng.Address = strAddress
                End If
            End If
        Next wshS
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub
    Any help would be appreciated
    Last edited by Paul_Hossler; 09-12-2019 at 03:10 PM. Reason: Added CODE tags

  2. #2
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    271
    Location
    rather than
                        wshS.Range("A" & s & ",B" & s & ",D" & s & ",F" & s).Copy _
                                Destination:=wshT.Range("B" & t)
    use
                        wshS.Range("A" & s & ":B" & s).Copy _
                                Destination:=wshT.Range("B" & t)
                        wshS.Range("F" & s).Copy _
                                Destination:=wshT.Range("D" & t)
                        wshS.Range("D" & s).Copy _
                                Destination:=wshT.Range("E" & t)
    Artik

  3. #3
    Worked perfectly. Thank you

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,846
    Location
    With wshS.Rows(s)
       .Range("A:B").Copy wshT.Range("B" & t)
       .Range("F").Copy  Etc
       Etc
    End With
    Please take the time to read the Forum FAQ

  5. #5
    VBAX Expert
    Joined
    Sep 2016
    Posts
    721
    Location
    If common headers exist in all worksheets


    Sub test()
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim rngS As Range, rngD As Range, rngC As Range
    
        Set wshT = Worksheets("Inspection")
        Set rngD = wshT.Cells(1, wshT.UsedRange.Columns.Count + 2)
        wshT.[b3].Resize(, 4).Copy rngD
        Set rngD = rngD.CurrentRegion
    
        For Each wshS In Worksheets
            If wshS.Name <> wshT.Name Then
                Set rngS = wshS.[a1].CurrentRegion
                Set rngC = rngS.Offset(, rngS.Columns.Count + 1).Resize(2, 1)
                rngC(2).Formula = "=J2=""X"""
            
                rngS.AdvancedFilter xlFilterCopy, rngC, rngD
                rngD.CurrentRegion.Offset(1).Copy
                wshT.Cells(Rows.Count, 2).End(xlUp).Offset(1).PasteSpecial xlValues
                rngC.Clear
                rngS.Columns("j").Offset(1).ClearContents
            End If
        Next
        
        rngD.CurrentRegion.Clear
            
    End Sub

  6. #6
    Cross posting.
    You get lots of answer here:

    VBA code problems dealing with the order of copy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •