Consulting

Results 1 to 4 of 4

Thread: Copying only values in VBA

  1. #1

    Copying only values in VBA

    I just started learning vba, and I am trying to copy certain cells from one workbook onto a sheet in my master workbook where all records are saved. this is what i have so far:

    Sub Consolidate() 'Author:     Jerry Beaucaire'
         'Date:       9/15/2009     (2007 compatible)  (updated 4/29/2011)
         'Summary:    Merge files in a specific folder into one master sheet (stacked)
         '            Moves imported files into another folder
         ' Edited/altered by me
         
         
        Dim fName As String, fPath As String, fPathDone As String 
        Dim LR As Long, NR As Long 
        Dim wbData As Workbook, wsMaster As Worksheet 
         
         
         'Setup
        Application.ScreenUpdating = False 'speed up macro execution
        Application.EnableEvents = False 'turn off other macros for now
        Application.DisplayAlerts = False 'turn off system messages for now
         
        Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into
         
         
        With wsMaster 
            If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then 
                .UsedRange.Offset(1).EntireRow.Clear 
                NR = 2 
            Else 
                NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'appends data to existing data
            End If 
             
             
             'Path and filename (edit this section to suit)
            fPath = "C:\Users\jchase.BRYCEWORLD.000\Desktop\Test\" 'remember final \ in this string"
            fPathDone = fPath & "Imported\" 'remember final \ in this string
            On Error Resume Next 
            MkDir fPathDone 'creates the completed folder if missing
            On Error GoTo 0 
            fName = Dir(fPath & "New BM Analysis test.xlsm") 'listing of desired files, edit filter as desired
             
             
             'Import a sheet from found files
            Do While Len(fName) > 0 
                If fName <> ThisWorkbook.Name Then 'don't reopen this file accidentally
                    Set wbData = Workbooks.Open(fPath & fName) 'Open file
                     
                     
                     'This is the section to customize, replace with your own action code as needed
                    LR = Range("A" & Rows.Count).End(xlUp).Row 'Find last row
                    wbData.ActiveSheet.Range("P14:S" & LR).Copy.Value  .Range("A" & NR) 
                     
                    wbData.Close False 'close file
                    NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'Next row
                     'Name fPath & fName As fPathDone & fName           'move file to IMPORTED folder
                End If 
                fName = Dir 'ready next filename
            Loop 
        End With 
         
         
    ErrorExit: 'Cleanup
        ActiveSheet.Columns.AutoFit 
        Application.DisplayAlerts = True 'turn system alerts back on
        Application.EnableEvents = True 'turn other macros back on
        Application.ScreenUpdating = True 'refreshes the screen
    End Sub 
    
    
    Formatting tags added by mark007
    I am trying to get values from P14-s14 down until there are no more values.Iam getting an error 424 on the line
    wbData.ActiveSheet.Range("P14:S" & LR).Copy.Value  .Range("A" & NR) 
    
    
    Formatting tags added by mark007
    .
    Am i not correctly calling for the values?

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,311
    Location
    Copy the Range... PasteSpecial the Values
    Please take the time to read the Forum FAQ

  3. #3
    Hi. When you want to copy only values, use PasteSpecial method with xlPasteValues option

    wbData.ActiveSheet.Range("P14:S" & LR).Copy 
    .Range("A" & NR).PasteSpecial xlPasteValues 
    
    
    Formatting tags added by mark007
    or directly assign the values to the target cells
    .Range("A" & NR).Value = wbData.ActiveSheet.Range("P14:S" & LR).Value 
    
    
    Formatting tags added by mark007

  4. #4
    Set rData = wbData.ActiveSheet.Range("P14:S" & LR) 
    .Range("A" & NR).Resize(rData.Rows.Count, rData.Columns.Count).Value = rData.Value 
    
    
    Formatting tags added by mark007

Tags for this Thread

Posting Permissions

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