To copy selected columns to another sheet

[klnp_mysore]

Hi
How to copy the selected columns from one worksheet to another worksheet.

 

[Datsmart]

    Selection.EntireColumn.Copy _
    Destination:=Sheets("Sheet2").Range("A1")

Full column(s) must be selected and Sheet2 must exist.

 

[klnp_mysore]

hi,
I have 7 columns in sheet1 and 5 columns in sheet2.

Sheet1
ColA ColB ColC ColD ColE ColF ColG
1 aa bb cc dd ee Y


Sheet2
ColA ColB ColC ColD ColE
1 aa cc dd ee

I have to copy the selected values of sheet1 if the value in ColG is "Y" .
ex. the values in colA of sheet1 to be copied to ColA of sheet2, the values in colB of sheet1 to be copied to ColB of sheet2, the values in colD of sheet1 to be copied to ColC of sheet2, the values in colE of sheet1 to be copied to ColD of sheet2,

How to do this with exce vba.

Regards

 

[Datsmart]

The below code assumes Sheets "Sheet1" and "Sheet2" exist and Sheet1 is active when the code runs.

Sub CopyBetweenSheets()
    'Assign Range where "Y" values exist
    Set Rng1 = Range("G1:G" & Sheets("Sheet1").Cells(Rows.Count, "G").End(xlUp).Row)
    For Each c In Rng1
        If c.Value = "Y" Then
            'Assign LastRow variable
            LR = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, 1).End(xlUp).Row + 1
            'Assign cell values between sheets
            Sheets("Sheet2").Range("A" & LR).Value = Cells(c.Row, "A").Value
            Sheets("Sheet2").Range("B" & LR).Value = Cells(c.Row, "B").Value
            Sheets("Sheet2").Range("C" & LR).Value = Cells(c.Row, "D").Value
            Sheets("Sheet2").Range("D" & LR).Value = Cells(c.Row, "E").Value
        End If
    Next c
End Sub

Since the code "assigns" values rather than copying, the cell formating will not be transferred.

 

[klnp_mysore]

Hi,
First row contains column Heading, datas start from the second row,
Sheet1 contains blank rows in between.I have to copy only the rows that have entry in column G as "OK".

Regards

The below code assumes Sheets "Sheet1" and "Sheet2" exist and Sheet1 is active when the code runs.

Sub CopyBetweenSheets()
    'Assign Range where "Y" values exist
    Set Rng1 = Range("G1:G" & Sheets("Sheet1").Cells(Rows.Count, "G").End(xlUp).Row)
    For Each c In Rng1
        If c.Value = "Y" Then
            'Assign LastRow variable
            LR = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, 1).End(xlUp).Row + 1
            'Assign cell values between sheets
            Sheets("Sheet2").Range("A" & LR).Value = Cells(c.Row, "A").Value
            Sheets("Sheet2").Range("B" & LR).Value = Cells(c.Row, "B").Value
            Sheets("Sheet2").Range("C" & LR).Value = Cells(c.Row, "D").Value
            Sheets("Sheet2").Range("D" & LR).Value = Cells(c.Row, "E").Value
        End If
    Next c
End Sub

Since the code "assigns" values rather than copying, the cell formating will not be transferred.

 

[Datsmart]

In your first post you said you wanted to copy rows if Column G contained "Y", now you say you want to copy rows that have entry in column G as "OK".

Edit the following code line as needed:

If c.Value = "Y" Then

Did you try the code?
Does it do what you want?

 

[klnp_mysore]

hi,
I have 7 columns in sheet1 and 5 columns in sheet2.

Sheet1
ColA ColB ColC ColD ColE ColF ColG
1 aa bb cc dd ee Y


Sheet2
ColA ColB ColC ColD ColE
1 aa cc dd ee

I have to copy the selected values of sheet1 if the value in ColG is "Y" .
ex. the values in colA of sheet1 to be copied to ColA of sheet2, the values in colB of sheet1 to be copied to ColB of sheet2, the values in colD of sheet1 to be copied to ColC of sheet2, the values in colE of sheet1 to be copied to ColD of sheet2,
If colG is "N" then the copy the rows to sheet3

How to do this with exce vba.

Regards
<!-- / message -->