macro to paste last active rows data into "master"

**jw01** · 03-22-2012, 04:26 PM

hello

i have the following macro in "my workbook"

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim ofs As Long
If Sh.Name Like "P*" And Sh.Name <> Sheet17.Name Then
    ofs = CLng(Mid(Sh.Name, 2)) - 1
    Sheets("Master").Range("B10:P1009").Offset(ofs * 1000).Value = Sh.Range("B10:P1009").Value
End If
End Sub

what i would like...if in the range B10:P1009, the data is last reported on row 696, can the macro only consolidate the data in the master sheet of the active cells i.e. B10:P696?

my data is consolidated into the "master" worksheet, starting in cell B10....

can someone pls help!

**jw01** · 03-23-2012, 01:12 AM

any thoughts guys?!? thxs

**jw01** · 03-23-2012, 09:49 AM

any thoughts or updates guys? thx u

**arlu1201** · 03-23-2012, 10:13 AM

Are you consolidating data from multiple worksheets or multiple workbooks?

**jw01** · 03-23-2012, 01:41 PM

hello

yes i am.

the worksheets i am consolidating my data from are P1, P2, P3...P12

also, in my master sheet where the data is being consolidated, column A is a # cell i.e. A10 = 1, A11 = 2, A12 = 3 etc

ur thoughts? thx u

**jw01** · 03-24-2012, 02:35 PM

hello all

any thoughts?? thxs

**arlu1201** · 03-25-2012, 07:57 AM

You can try this code

Sub copy_data()
Dim i As Long
Dim lrow As Long

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name Like "P*" Then
            lrow = .Range("B" & .Rows.Count).End(xlUp).Row
            .Range("B10:P" & lrow).Copy Worksheets("Master").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    End With
Next i

End Sub

What is the name of your Sheet17? You have specified that as a condition. I have not included it, since i didnt know what the sheetname was. What if the sheet no changes to 18? In that case, its better to specify the name in the condition, like if .name <>"Test".

**jw01** · 03-25-2012, 01:29 PM

hello quick question and thx u so much for your help!!

if for instance in my worksheet "P1"...where the data is being extracted from into the master sheet, contains one blank row...will the macro automatically adjust to pick up the next row or will it jump into the next sheet "P2" and extract data from there? thx u so much for your kind help.

**arlu1201** · 03-26-2012, 06:11 AM

The macro will check for the last row with data in column B in each worksheet beginning with P. It does this by going to the last row in excel and climbing up to the last row with data. If there are blanks in between, it will include that as well. Then, it will move to the next worksheet.

**jw01** · 03-26-2012, 09:36 AM

hey arlu

thxs for your wonderful help. i am using the macro in the "workbook" sheet as when the users go are on the P1, P2..P12 sheet, i would like the macro to automatically consolidate the data from the P1-P12 worksheets.

the only issue using the macro you provided...it is taking a long time and also, it does not overwrite the existing data in the master sheet

here is the code

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim i As Long
Dim lrow As Long

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name Like "P*" Then
            lrow = .Range("B" & .Rows.Count).End(xlUp).Row
            .Range("B10:P" & lrow).Copy Worksheets("Master").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    End With
Next i

End Sub

**arlu1201** · 03-26-2012, 09:57 AM

Why are you using the workbook_sheetdeactivate event? You can use it as normal sub-routine as in Sub copy_data(). This could be slowing down your code.

To clear the contents of the master sheet, use this updated code

Private Sub copy_data()
Dim i As Long
Dim lrow As Long

worksheets("Master").cells.clearcontents
for i = 1 to worksheets.count
   if worksheets(i).name <>"Master" then
       worksheets(i).rows("10:10").copy worksheets("Master").range("A10")
   end if
next i

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name Like "P*" Then
            lrow = .Range("B" & .Rows.Count).End(xlUp).Row
            .Range("B10:P" & lrow).Copy Worksheets("Master").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    End With
Next i

End Sub

**jw01** · 03-26-2012, 01:56 PM

hey arlu

the sole reason why i was using workbook_event was to make the macro automated.

ideally i do not want the user to run any macros. i would like if.when they click on another worksheet, the data gets consolidated into the "master" sheet. does that make sense...will the updated macro you provided perform that task?

thx u so much for your help, it is really appreciated.

macro to paste last active rows data into "master"

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

macro to paste last active rows data into "master"

Re: macro to paste last active rows data into "master"

Re: macro to paste last active rows data into "master"

Re: macro to paste last active rows data into "master"

Re: macro to paste last active rows data into "master"

Re: macro to paste last active rows data into "master"

Re: macro to paste last active rows data into "master"

Re: macro to paste last active rows data into "master"

Re: macro to paste last active rows data into "master"

Re: macro to paste last active rows data into "master"

Re: macro to paste last active rows data into "master"

Re: macro to paste last active rows data into "master"

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions