Additional cells are copied when I didn't select them

[TTXS]

Hello, I have a VBA working perfectly to combine data from multiple sheets (all sheets have same column headers, column B to I). I have extra value in Column A (row count), and cell L11/M11 (total sum). I'm only selecting cell B10 to II0 when I select header when prompted, but I get the full sheet copied in the new sheet, including value in column A/L/M. How can I get value from B to I only? I assume it's the part of highlighted area, but I don't know how to fix that.

Here is the code. Thank you

Sub Combine_WorkSheets()
Dim sRow, sCol, lRow, lCol As Long
Dim hdrs As Range
For Each Sheet In ActiveWorkbook.Worksheets
    If Sheet.Name = "Master" Then
        Application.DisplayAlerts = False
        Worksheets("Master").Delete
        Application.DisplayAlerts = True
    End If
Next Sheet
Sheets.Add.Name = "Master"
Set mtr = Worksheets("Master")
Set wb = ActiveWorkbook
Set hdrs = Application.InputBox("Select the headers", Type:=8)
hdrs.Copy mtr.Range("A1")
sRow = hdrs.Row + 1
sCol = hdrs.Column
Debug.Print sRow, sCol
For Each ws In wb.Worksheets
    If ws.Name <> "Master" Then
        ws.Activate
        lRow = Cells(Rows.Count, sCol).[B]End(xlUp[/B]).Row
        lCol = Cells(sRow, Columns.Count).[B]End(xlToLeft)[/B].Column
        Range(Cells(sRow, sCol), Cells(lRow, lCol)).Copy _
        mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
    End If
Next ws
Worksheets("Master").Activate
ActiveWindow.Zoom = 115
Sheets("Master").Select
    Range("B:I").Select
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add2 Key:=Range("B2:B1000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Master").Sort
        .SetRange Range("A2:G1000")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
  End With
End Sub

 

[dreid1011]

Have you verified lRow and lCol are the values you expect them to be? Are you always copying B10:I10, are you looping through multiple rows, or just the last row?

 

[TTXS]

B10 to I10 is the header. I'm trying to copy all the populated value in column B and I of each sheet to the new sheet. For example, if sheet 1 has B10- B15 to I10 to I15 populated, and sheet 2 has B10 to B20 and I10 to I20 populated, then I want to copy the header (B10 to I10 on both sheets), and then 4 records from sheet 1 and 9 records from sheet 2 to the new sheet. I don't care about any other values on sheet 1 or sheet 2 in other columns, whether its in column H, or I, or XX. Right now, it seems it will copy over all the values in all columns. Not a big deal as I can clear the contents on new sheet. But want to see if there are ways to copy over what i selected. Thanks

 

[dreid1011]

There are ways to copy over just what you want. I know I have experienced what your describing before, trying to find the last row of data and somehow it was selecting the entire column, but I can't seem to replicate it right now. I was testing that portion of your code and it seemed to be behaving as expected. Are you wanting to copy the columns between B and I as well (C:H) or just B and I?

Also, have you stepped through your code and confirmed the variable values at each step to see when and maybe why they are not the range you want?

This is what I was testing with, just manually setting sRow and sCol since I don't have your workbook and it seems to operate as expected.

Private Sub CopyData()
Dim lRow As Long, lCol As Long, sRow As Long, sCol As Long, cRow As Long
sRow = 11
sCol = 2
lRow = Cells(Rows.Count, sCol).End(xlUp).Row
lCol = Cells(sRow, Columns.Count).End(xlToLeft).Column
Debug.Print lRow & " - " & lCol
cRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
Range(Cells(sRow, sCol), Cells(lRow, lCol)).Copy Sheets("Sheet2").Range("A" & cRow)
End Sub

 

[jolivanes]

For your lRow and lCol, use Values. Now you're probably getting data including formulae

lr = Cells.Find("*", ,xlValues , , xlByRows, xlPrevious).Row

Convert above to fit your needs

 

[TTXS]

Thank you. I ended up just adding a code to clear the extra cells that got covered over. I'll keep playing around with your suggestions.

 

[jolivanes]

Re: "adding a code to clear the extra cells that got covered over"
You should not have to do that. You might accidently delete data that should not be deleted.
If you give us some the information about your sheet setup, someone will get you a proper solution.

 

[smozgur]

Thank you. I ended up just adding a code to clear the extra cells that got covered over. I'll keep playing around with your suggestions.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.