Results 1 to 19 of 19

macro consolidates data from worksheets - question

Threaded View

  1. #1
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,233

    macro consolidates data from worksheets - question

    hi

    i have the following code:

    Private Sub Worksheet_Activate()
    Dim Sh As Worksheet
    Dim Newsh As Worksheet
    Dim LR As Long, NR As Long
    
    Application.ScreenUpdating = False
    
    Set Newsh = ThisWorkbook.Sheets(Sheet20.Name)
    Newsh.Range("C12").CurrentRegion.Offset(1).Clear
    NR = 9
    
        
        For Each Sh In ThisWorkbook.Worksheets
            If Sh.Name Like "*" And Sh.Name <> Sheet7.Name And Sh.Name <> Sheet1.Name And Sh.Name <> Sheet19.Name And Sh.Name <> Sheet18.Name And Sh.Name <> Sheet17.Name And Sh.Name <> Sheet16.Name And Sh.Name <> Sheet15.Name And Sh.Name <> Sheet14.Name And Sh.Name <> Sheet13.Name And Sh.Name <> Sheet12.Name And Sh.Name <> Sheet11.Name And Sh.Name <> Sheet10.Name And Sh.Name <> Sheet19.Name And Sh.Name <> Sheet20.Name And Sh.Name <> Sheet20.Name And Sh.Visible Then
                LR = Sh.Range("B" & Sh.Rows.Count).End(xlUp).Row
                If LR > 8 Then
                    Sh.Range("C13:AJ" & LR).Copy
                    Newsh.Range("C" & NR).PasteSpecial xlPasteFormats
                    Newsh.Range("C" & NR).PasteSpecial xlPasteAll
                    LR = Newsh.Range("C" & Rows.Count).End(xlUp).Row
                    Newsh.Range("A" & NR, "A" & LR) = Sh.Name
                    NR = LR + 1
       
                End If
            End If
        Next Sh
        
    Application.ScreenUpdating = True
    
    End Sub
    the issue:
    i would like for the macro to paste the data start from cell C13:AJ

    it works..but its pasting the data now in row 13 of column C....can someone pls help and advise?
    Last edited by jw01; 04-03-2012 at 09:07 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1