copy range in worksheets paste to master

[vexcel]

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..

[Richard Buttrey]

Hi,

Use something like

Please Login or Register  to view this content.

[vexcel]

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

[Richard Buttrey]

Hi,

Yes, just substitute

Please Login or Register  to view this content.

Rgds

[vexcel]

I am getting a run time error on that code line

[Richard Buttrey]

Hi,

Have you substituted it for the original line

Please Login or Register  to view this content.

and left all the other code in place?

Rgds

[vexcel]

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

[JBeaucaire]

Maybe this:

Please Login or Register  to view this content.

[vexcel]

it made no difference.

[JBeaucaire]

Or:

Please Login or Register  to view this content.

[ravishankar]

HI
try these codes

Please Login or Register  to view this content.

ravi

[JBeaucaire]

Ravi, resist the urge to hardcode a bottom row number like that. Each version of Excel has a different "bottom row" and hardcoding 65536 can have bad results on Excel 2007+ users.

Use the Rows.Count syntax in place of the hardcoded number so Excel always starts from the actual bottom of the sheet, regardless of version.

[ravishankar]

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

[kumaramitoujjain]

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