How to copy excluding one particular column

[GirishDhruva]

Hi Everyone,
Here i am trying to copy data from one sheet to another, where i need to exclude one particular column from copy sheet

Like :
Think i have 2 sheets( with names as "Combined","HSR") from HSR i need to copy all the data to Combined sheet excluding column "D" values

Worksheets("HSR").Select
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
lr2 = Sheets("Combined").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
Range("A2:C" & lr).Copy Sheets("Combined").Range("A" & lr2 + 1)
Range("E2:AX" & lr).Copy Sheets("Combined").Range("D" & lr2 + 1)

As you can see the solution but instead of "AX" can we give the range dynamically as my columns might increase or decrease

Thank you in advance
Dhruv

 

[kweaver]

If row1 always has headers, for example, this might do the trick:

Sub LastColumnInOneRow()
'Find the last used column in a Row: row 1 in this example
    Dim LastCol As Integer
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End Withl
End Sub

You could incorporate the concept into your routine.

 

[GirishDhruva]

@kweaver yes always my 1st row contains headers but where i need to do these changes in my code, can you please suggest me

 

[GirishDhruva]

Its throwing me a run time error 1004 if i use the below code

Sub ranges()
    Worksheets("HSR").Select
    lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    lr2 = Sheets("Combined").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    LastCol = Sheets("HSR").Cells(1, Columns.Count).End(xlToLeft).Column
    Range("A2:C" & lr).Copy Sheets("Combined").Range("A" & lr2 + 1)
    Range("E2:LastCol" & lr).Copy Sheets("Combined").Range("D" & lr2 + 1)
End Sub

 

[GirishDhruva]

I have tried with the below code also but its throwing me Error code 1004 Method Range of Object _Global Failed

Sub ranges()
Dim ws As Worksheet
Dim ColumnLetter As Variant
    Set ws = Sheets("HSR")
    Worksheets("HSR").Select
    lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    lr2 = Sheets("Combined").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    ColumnLetter = Split(ws.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)
    Range("A2:C" & lr).Copy Sheets("Combined").Range("A" & lr2 + 1)
    Range("E2:ColumnLetter" & lr).Copy Sheets("Combined").Range("D" & lr2 + 1)
End Sub

Could any one suggest me what changes i should make

 

[Rick Rothstein]

Why not simply copy all the columns to the new sheet and then delete Column D from that sheet afterwards?

 

[GirishDhruva]

Already in my Combined sheet i would be having some data which i need for further use but from sheet "HSR" column D is an extra column which is not required.
As i neglect/i dont need to copy the column D from sheet "HSR" then the columns would be matched in Combined sheets , if i don't then one extra column would be their in Combined

 

[GirishDhruva]

I got the solution as i required

Sub ranges()
Dim ws As Worksheet
Dim ColumnLetter As Variant
    Set ws = Sheets("HSR")
    Worksheets("HSR").Select
    lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    lr2 = Sheets("Combined").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    ColumnLetter = Split(ws.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)
    Range("A2:C" & lr).Copy Sheets("Combined").Range("A" & lr2 + 1)
    Range("E2:"& ColumnLetter & lr).Copy Sheets("Combined").Range("D" & lr2 + 1)
End Sub

Thanks friends for your help and suggestions
Dhruv