Results 1 to 6 of 6

Macro to copy paste multiple worksheets data into one worksheets

This is a discussion on Macro to copy paste multiple worksheets data into one worksheets within the Excel Questions forums, part of the Question Forums category; hi, i need a macro which copy and paste from multiple worksheets (except for 3 worksheets which is named after ...

  1. #1
    Board Regular
    Join Date
    Sep 2010
    Posts
    56

    Default Macro to copy paste multiple worksheets data into one worksheets

    hi,

    i need a macro which copy and paste from multiple worksheets (except for 3 worksheets which is named after Jan, Feb and Mar) into one worksheets (named as OVERALL). The data to copy will cover from cell A1:D1 and below where there is data available. Thanks in advance.

  2. #2
    Board Regular
    Join Date
    Sep 2007
    Location
    New Jersey USA
    Posts
    3,374

    Default Re: Macro to copy paste multiple worksheets data into one worksheets

    Maybe:

    Code:
    Sub su2009()
    
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
    
        If ws.Name <> "Jan" Then
        
            If ws.Name <> "Feb" Then
            
                If ws.Name <> "Mar" Then
                
                    If ws.Name <> "OVERALL" Then
        
                        ws.Activate
            
                        ws.Range("A1:D1").Copy Sheets("OVERALL").Range("A" & Rows.Count).End(3)(2)
                        
                    End If
                    
                End If
                
            End If
            
        End If
        
    Next ws
    
    End Sub

  3. #3
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    2,023

    Default Re: Macro to copy paste multiple worksheets data into one worksheets

    Would this give the same results as the nestedIfs you have in your code

    Code:
    If ws.Name <> "Jan" and ws.name<> "Feb" and ws.name<> "Mar"
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  4. #4
    Board Regular
    Join Date
    Sep 2007
    Location
    New Jersey USA
    Posts
    3,374

    Default Re: Macro to copy paste multiple worksheets data into one worksheets

    Quote Originally Posted by Momentman View Post
    Would this give the same results as the nestedIfs you have in your code

    Code:
    If ws.Name <> "Jan" and ws.name<> "Feb" and ws.name<> "Mar"
    Maybe, I didn't think of And? It tried it with Or and that didn't work.

  5. #5
    Board Regular
    Join Date
    Sep 2010
    Posts
    56

    Default Re: Macro to copy paste multiple worksheets data into one worksheets

    hi John,

    The macro is not workin like it suppose to. it couldnt combine all the data of the worksheets in OVERALL. anyhow here is currently the macro that i am using but this macro is only copying specific worksheets (RAW to RAW6). anyway to go around this. i need the macro to copy all the worksheets except for worksheet "DateTime", "VLOOKUP", "RDBMergeSheet" and the data to combine in worksheet "RDBMergeSheet".

    Sub CombineWorksheets()
    Sheets("RDBMergeSheet").Select
    Cells.Select
    Selection.ClearContents
    Sheets("RAW").Select
    Range("C1:H1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("RDBMergeSheet").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    NextRow = Range("A65536").End(xlUp).Row + 1
    Range("A" & NextRow).Select
    Sheets("RAW (2)").Select
    Range("C1:H1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("RDBMergeSheet").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    NextRow = Range("A65536").End(xlUp).Row + 1
    Range("A" & NextRow).Select
    Sheets("RAW (3)").Select
    Range("C1:H1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("RDBMergeSheet").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    NextRow = Range("A65536").End(xlUp).Row + 1
    Range("A" & NextRow).Select
    Sheets("RAW (4)").Select
    Range("C1:H1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("RDBMergeSheet").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    NextRow = Range("A65536").End(xlUp).Row + 1
    Range("A" & NextRow).Select
    Sheets("RAW (5)").Select
    Range("C1:H1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("RDBMergeSheet").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    NextRow = Range("A65536").End(xlUp).Row + 1
    Range("A" & NextRow).Select
    Sheets("RAW (6)").Select
    Range("C1:H1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("RDBMergeSheet").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    NextRow = Range("A65536").End(xlUp).Row + 1
    Range("A" & NextRow).Select
    End Sub

  6. #6
    Board Regular
    Join Date
    Sep 2007
    Location
    New Jersey USA
    Posts
    3,374

    Default Re: Macro to copy paste multiple worksheets data into one worksheets

    The macro I provided was tested, and worked for me. What is it not doing that you want it too do? To adjust just change the names of the sheets in the code that are not to be copied, the destination sheet and the range.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com