+ Reply to Thread
Results 1 to 3 of 3

copy certain cells from all worksheets to one sheet. Need review of my VBA code.

  1. #1
    Registered User
    Join Date
    02-18-2017
    Location
    Germany
    MS-Off Ver
    Office 2013
    Posts
    3

    copy certain cells from all worksheets to one sheet. Need review of my VBA code.

    Hey.

    I am trying to copy multiple cell entries from all worksheets (with a few exceptions) into one sheet called "MergedDataSheet".
    Everything is working like a charm with the code below.

    But now I would like to copy 2 additional cell entries from all worksheets (except from the ones listed in the code below).
    The additional cells are:
    B14 and B15.

    B14 should be copied to column K, and B15 should be copied to comlumn L. Both entries should be in the same row as the name of the origins data sheet (column H).
    The listing of the data sheets is working fine with the code below already. So basically, all I wish to add is those 2 cells from all worksheets into the "MergedDataSheet".

    Do you guys have any ideas?

    ---------------------

    Sub CopyRangeFromMultiWorksheets()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim last As Long
    Dim CopyRng As Range

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

    'Delete the sheet "MergedDataSheet" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("MergedDataSheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "RDBMergeSheet"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "MergedDataSheet"


    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets
    If IsError(Application.Match(sh.Name, _
    Array(DestSh.Name, "Help", "Dashboard", "Dashboard (V2)", "Dashboard (V3)", "Overview", "Project (1)"), 0)) Then

    'Find the last row with data on the DestSh
    last = LastRow(DestSh)

    'Fill in the range that you want to copy
    Set CopyRng = sh.Range("A30,E30,A50,E50,A70,E70,A90,E90")

    'Test if there enough rows in the DestSh to copy all the data
    If last + CopyRng.Rows.Count > DestSh.Rows.Count Then
    MsgBox "There are not enough rows in the Destsh"
    GoTo ExitTheSub
    End If

    'This example copies values
    CopyRng.Copy
    With DestSh.Cells(last + 1, "A")
    .PasteSpecial xlPasteValues
    'enable row below to copy formatting as well
    '.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    End With

    'Optional: This will copy the sheet name in the H column
    DestSh.Cells(last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name

    End If
    Next

    ExitTheSub:

    Application.GoTo DestSh.Cells(1)

    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    End Sub

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: copy certain cells from all worksheets to one sheet. Need review of my VBA code.

    Add these two lines.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    02-18-2017
    Location
    Germany
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: copy certain cells from all worksheets to one sheet. Need review of my VBA code.

    Thank you!
    This solved my issues. I always tried to adjust the previously specified range... which worked kind of like a mouse drawn range. Anyway, thanks a lot!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trying to finish a VBA code to copy cells from multiple workbooks and worksheets
    By sweetnasty in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-10-2014, 11:56 PM
  2. [SOLVED] copy certain cells from all worksheets to a master sheet
    By istvan60 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-02-2014, 07:49 AM
  3. [SOLVED] Copy from worksheets into sheet which ran code
    By floydian in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-10-2013, 03:18 PM
  4. [SOLVED] Code to copy data from specified worksheets into one sheet
    By ZeDoctor in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-02-2012, 09:02 AM
  5. Code Required To Use Condition To Copy Data From Different Worksheets Into Another Sheet
    By lukerogers1987 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-29-2012, 05:26 AM
  6. Replies: 0
    Last Post: 03-01-2012, 12:13 PM
  7. Linking worksheets to copy cells from 1 sheet to another
    By nmicon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2009, 12:05 AM

Tags for this Thread

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