+ Reply to Thread
Results 1 to 3 of 3

VBA: Compiling Data in just one sheet - How to paste as value in my destination

  1. #1
    Registered User
    Join Date
    04-13-2020
    Location
    Brazil
    MS-Off Ver
    MS Office
    Posts
    6

    Lightbulb VBA: Compiling Data in just one sheet - How to paste as value in my destination

    Hey Guys!

    Objetive: Compile data from multiple sheets in just one.

    I have a workbook with multiple sheets where I'm using a code to be able to compile all the information, which are informations (calculeted by formulas) and organized on a single line in each tab that are my source.
    (see images). However, in the code I'm using, my data is read as formulas and ends up entering the compiled spreadsheet with error ... So I need help changing my code to paste Values instead of just normal format pasting in the main spreadshhet.

    Can you help me?

    This is the code that I am using and where I got from (danwagner's blog, title: how-to-combine-data-from-multiple-sheets-into-a-single-sheet)

    Public Sub CombineDataFromAllSheets()

    Dim wksSrc As Worksheet, wksDst As Worksheet
    Dim rngSrc As Range, rngDst As Range
    Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long

    'Notes: "Src" is short for "Source", "Dst" is short for "Destination"

    'Set references up-front
    Set wksDst = ThisWorkbook.Worksheets("Import")
    lngDstLastRow = LastOccupiedRowNum(wksDst) '<~ defined below (and in Toolbelt)!
    lngLastCol = LastOccupiedColNum(wksDst) '<~ defined below (and in Toolbelt)!

    'Set the initial destination range
    Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)

    'Loop through all sheets
    For Each wksSrc In ThisWorkbook.Worksheets

    'Make sure we skip the "Import" destination sheet!
    If wksSrc.Name <> "Import" And wksSrc.Name <> "Agenda" Then

    'Identify the last occupied row on this sheet
    lngSrcLastRow = LastOccupiedRowNum(wksSrc)

    'Store the source data then copy it to the destination range
    With wksSrc
    Set rngSrc = .Range("C44", "P44")
    rngSrc.Copy Destination:=rngDst

    End With

    'Redefine the destination range now that new data has been added
    lngDstLastRow = LastOccupiedRowNum(wksDst)
    Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)

    End If

    Next wksSrc

    End Sub

    Thanks a lot!

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: VBA: Compiling Data in just one sheet - How to paste as value in my destination

    Change this section to:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-13-2020
    Location
    Brazil
    MS-Off Ver
    MS Office
    Posts
    6

    Re: VBA: Compiling Data in just one sheet - How to paste as value in my destination

    Much Thanks! SOLVED!

+ 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. Copy & paste with specific condition to destination sheet
    By Revathi kannan in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-20-2020, 12:25 AM
  2. VBA to Copy Paste and Format Destination sheet
    By FredFitzgerald in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-06-2019, 09:50 AM
  3. Copy from static destination & paste to dynamic destination
    By Marbleking in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-31-2015, 09:31 AM
  4. Replies: 5
    Last Post: 12-30-2014, 12:41 PM
  5. Replies: 1
    Last Post: 12-15-2012, 11:32 AM
  6. paste data, keep destination formatting
    By excel_try in forum Excel General
    Replies: 9
    Last Post: 07-17-2009, 06:44 AM
  7. Replies: 4
    Last Post: 07-19-2006, 01:00 PM

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