Thread: copy range in worksheets paste to master

I am working with a workbook that i have created. The workbook has around thirty worksheets all with the starting nave of "DIV". I have a code that will loop throught the wrok book and copy the used range and insert them into a master. However what i need is a code that will loop through the wroksheets and only copy the used range only in columns A:P and starting in row 10 (i have headers from row 1-10). I have columns beyond "P" that has working information for that worksheet and do not want to copy it over to the master.

Thank you for the help..

The code you gave me only seems to copy one row. I believe it could be because there is blank rows in the range. Is there a way for it to go all the way to the bottom and then com back up to the first cell with value. I cannot get rid of the blank rows, they must remain.

Thank you

I am getting a run time error on that code line

I did change it out. here is the code i am working with
Private Sub CommandButton1_Click()

Dim DESTSH As Worksheet
Dim Last As Long
Dim lUsedRows As Long, x As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

' delete existing rows in estimate sheet
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("ESUM").Select
Rows("13:10000").Select
Selection.DELETE Shift:=x1up
On Error GoTo 0
Application.DisplayAlerts = True

Set DESTSH = ActiveWorkbook.Worksheets("ESUM")


For x = 1 To Sheets.Count
If Left(Sheets(x).Name, 3) = "DIV" Then
lUsedRows = Range(Sheets(x).Range("A10"), Sheets(x).Range("A" & Rows.Count).End(xlUp)).Rows.Count
Last = LASTROW(DESTSH)
Sheets(x).Range("A10:P" & lUsedRows).Copy Destination:=DESTSH.Cells(Last + 1, 1)
End If
Next x
ExitTheSub:
Application.Goto DESTSH.Cells(1)
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

it made no difference.

HI
try these codes

Sub vexcel()
Dim a As Long, x As Long, y As Long
Worksheets("ESUM").Cells.ClearContents
    For a = 1 To Sheets.Count
        If Worksheets(a).Name <> "ESUM" Then
        x = Worksheets(a).Range("A65536").End(xlUp).Row
        Worksheets(a).Range("A10:P" & x).Copy
        y = Worksheets("ESUM").Range("B65536").End(xlUp).Row
        Worksheets("ESUM").Range("A" & y + 2) = Worksheets(a).Name
        Worksheets("ESUM").Range("B" & y + 2).PasteSpecial
        End If
    Next a
MsgBox "Complete"
End Sub

ravi

Hi
Thanks JB for your suggestion. I will do so hereafter.
Ravi

Hi,

I have searched and searched in almost all the forums, but i have not been able to achieve what i need. I have to create a work sheet for my manager where he will assign different task/projects to us, and the same would be a updated in a shared file kept in our common DIR thus whenever we open we can view our individual sheets view the task assigned and update accordingly.

Now, i have already created a Master sheet where task assigned gets populated on individual sheet but i am stuck at is how get the master sheet updated for column (Status) & column(update) when we update our sheets in column (F) & column (G) the same needs to be updated on master sheet ?

Formula/macro anything is it possible?

please