Consulting

Results 1 to 4 of 4

Thread: Copying only values in VBA

  1. #1
    VBAX Newbie
    Joined
    Jan 2018
    Posts
    2
    Location

    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
    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)
    .
    Am i not correctly calling for the values?

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

  3. #3
    VBAX Regular
    Joined
    Jan 2018
    Posts
    30
    Location
    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
    or directly assign the values to the target cells
    .Range("A" & NR).Value = wbData.ActiveSheet.Range("P14:S" & LR).Value

  4. #4
    VBAX Expert
    Joined
    Sep 2016
    Posts
    630
    Location
    Set rData = wbData.ActiveSheet.Range("P14:S" & LR)
    .Range("A" & NR).Resize(rData.Rows.Count, rData.Columns.Count).Value = rData.Value

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
  •