CODEWITHSUNDEEP
vbaexcel
Michael Kapp
Michael Kapp

Reputation: 97

How to copy a row from one sheet and paste only certain columns to a new sheet?

My goal is to find data that does not exists in the first DB sheet and copy to the second DB sheet. The first DB has all the data, but only certain fields (Columns) are needed in the second DB sheet. I don’t see a “clean” way to copy a row (from the first DB sheet) and paste ONLY CERTAIN fields (i.e. Columns A, D, G, I, & K). I don’t want the space left between the fields/columns either. (i.e. Columns A, D, G, I, & K on the first sheet would become Columns A, B, C, D, & E). I think I know how to do this using another temporary sheet, but I figured there has to be a “CLEAN” way to approach this problem. Any and All help is appreciated. This solution does not work: VBA: loop to copy certain columns from main sheet, create a new sheet, and paste

here is my modified code: 

Sub columnCopy()

Dim sh1 As Worksheet
Set sh1 = Sheets("Sheet4")

lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column ' Last Column
lr = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row ' Last row

For I = 2 To lr
    'Trying to only copy columns "A" and "E" from Sheet4
    sh1.Range(sh1.Cells(I, 1), sh1.Cells(I, 5)).Copy
    'and paste data into Cells "A" and "B" on Sheet5
    Sheets("Sheet5").Cells(I, 1).PasteSpecial Paste:=xlPasteValues

Next I
'MsgBox I
End Sub

The probably still remains that Columns A, B, C, D, & E get copied to the new sheet, NOT just columns A & E as expected. Please Help.

Upvotes: 0

Views: 698

Answers (2)

user4039065
user4039065

Reputation:

As mentioned in cyboashu's excellent answer above, your solution lies in creating a union of the desired columns. Here is an alternative method of achieving the union that also demonstrates a quirk when copying and pasting a union of cells.

Using Range("A2:A9", "E2:E9") references all of the cells within A2:E9. However, adjusting the string cell range(s) slightly to Range("A2:A9, E2:E9") references A2:A9 unioned with E2:E9 as a contiguous block while discarding columns B, C and D. This can be proven in the VBE's Immediate window with ?Range("A2:A9", "E2:E9").Address(0, 0) and ?Range("A2:A9, E2:E9").Address(0, 0).

The quirk with copying and pasting unioned, discontiguous ranges mentioned earlier is that conventional copying and pasting methods result in a copy and paste special, values & formatting command. In other words, it strips any formula down to its resultant value. This is likely due to the fact that Excel cannot reconcile formula addresses within a unioned non-contiguous source range to their new contiguous target destination.

Option Explicit

Sub columnCopy()
    Dim rngstr As String, lr As Long

    With Worksheets("Sheet4")
        rngstr = "A2:A0,D2:D0,G2:G0,I2:I0,K2:K0"
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        rngstr = Replace(rngstr, "0", lr)
        .Range(rngstr).Copy Destination:=Worksheets("Sheet5").Cells(2, 1)
    End With
End Sub

So this alternative may make it easier to configure large groups of discontiguous cells and it does simplify the copy and paste values method if you do not mind carrying the formatting along with the values from formulas. In fact, the latter may be preferred behavior.

Upvotes: 0

cyboashu
cyboashu

Reputation: 10433

You don't need a loop. All you need to do is union the copy range as Rows are going to be same.

    Sub columnCopy()

        Dim sh1         As Worksheet
        Dim rngCopy     As Range
        Set sh1 = Sheets("Sheet1")

        lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column ' Last Column
        lr = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row ' Last row

        Set rngCopy = Union(sh1.Range(sh1.Cells(2, 1), sh1.Cells(lr, 1)), sh1.Range(sh1.Cells(2, 5), sh1.Cells(lr, 5)))
        rngCopy.Copy
        Sheet2.Cells(2, 1).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False


    End Sub

Upvotes: 2

Related Questions