PDA

View Full Version : [SOLVED] Copying only values in VBA



jschase
01-11-2018, 03:23 PM
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?

SamT
01-11-2018, 04:54 PM
Copy the Range... PasteSpecial the Values

yujin
01-11-2018, 09:16 PM
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

mana
01-12-2018, 05:38 AM
Set rData = wbData.ActiveSheet.Range("P14:S" & LR)
.Range("A" & NR).Resize(rData.Rows.Count, rData.Columns.Count).Value = rData.Value