copy all the data excluding column 'D'

[GirishDhruva]

Hi Everyone,

i am trying to copy all the data from one sheet to another in same workbook.

In master sheet i have A:AW columns of data were i should copy all the data excluding 'D' column, and paste that copied data in another sheet

Can anyone help me how to solve this


Thanks in advance

 

[nemmi69]

Are you doing this with VBa?

 

[GirishDhruva]

Yes....

 

[nemmi69]

So is column A going to column A, column B going to column B etc? Also do you want to leave column D blank at destination?

 

[GirishDhruva]

Yes...... Column A going to column A, column B going to column B etc but from source column D should be neglected/shouldn't be copied but in destination in the place of column D from source column E's value should be pasted

 

[Michael M]

Use

Sub MM1()
 Dim lr As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
Range("A1:C" & lr).Copy Sheets("Sheet2").Range("A1")
Range("E1:AW" & lr).Copy Sheets("Sheet2").Range("D1")
End Sub

 

[GirishDhruva]

Thanks @Michael M it worked

And one more thing, if i have some data in second sheet how to paste the value at last

 

[Michael M]

Sub MM1()
Dim lr As Long, lr2 As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
lr2 = Sheets("Sheet2").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
Range("A1:C" & lr).Copy Sheets("Sheet2").Range("A" & lr2)
Range("E1:AW" & lr).Copy Sheets("Sheet2").Range("D" & lr2)
End Sub

 

[Michael M]

Shorter....

Sub MM1()
Dim lr As Long, lr2 As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
lr2 = Sheets("Sheet2").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
Range("A1:C" & lr, "E1:AW" & lr).Copy Sheets("Sheet2").Range("A" & lr2)
End Sub

 

[GirishDhruva]

Thanks @Michael M it worked Perfectly

Last Question can we browse for a Excel file and copy multiple sheets into master workbook