0

enter image description hereNeed to copy values with no formula currently getting value error coming up - been working on it all night and cant do it

Sub LoopThrough()

    Dim MyFile As String, Str As String, Mydir As String, Wb As Workbook
    Dim Rws As Long, Rng As Range
    Set Wb = ThisWorkbook
    'change address to suite
    Mydir = "C:"
    MyFile = Dir(Mydir & "*.xlsm")
    ChDir Mydir
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0

    Do While MyFile <> ""
        Workbooks.Open (MyFile)
        With Worksheets("IDS_BCS")
            Rws = .Cells(Rows.Count, "A").End(xlUp).Row
            Set Rng = Range(.Cells(2, 1), .Cells(Rws, 7)) 'Column a to column G
            Rng.Copy Wb.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            ActiveWorkbook.Close True
        End With
        MyFile = Dir()
    Loop


End Sub

Thank you so much for your help

braX
  • 11,506
  • 5
  • 20
  • 33
  • Have you tried [Range.SpecialCells](https://learn.microsoft.com/en-us/office/vba/api/excel.range.specialcells) with xlCellTypeConstants ... Your question title and the question body mean different? Do you want to copy cells that dont contain formulas or do you want to copy all including formula as values? – Naresh Apr 09 '20 at 05:26
  • Hi @NareshBhople sorry been up most the night doing this brain is scrambled. I need all values on the page copied and pasted over, currently its copy formulas as so getting value error –  Apr 09 '20 at 05:41
  • You can use [Range.PasteSpecial](https://learn.microsoft.com/en-us/office/vba/api/excel.range.pastespecial) with [xlPasteValuesAndNumberFormats](https://learn.microsoft.com/en-us/office/vba/api/excel.xlpastetype) – Naresh Apr 09 '20 at 05:42
  • Rng.PasteSpecialCells Wb.Worksheets("Sheet1")?? @NareshBhople –  Apr 09 '20 at 05:50
  • Wb.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats – Naresh Apr 09 '20 at 05:54
  • woild this replace this? Rng.Copy Wb.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) @NareshBhople –  Apr 09 '20 at 05:58
  • You can easily search on web for excel VBA paste special values. On youtube there are many videos such as .. https://www.youtube.com/watch?v=dxZOyl33700 – Naresh Apr 09 '20 at 06:03
  • Rng.copy is one statement then on next line Wb.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats) – Naresh Apr 09 '20 at 06:08
  • Have a look at the answers to my question - that may give you some idea, if it helps give them a vote! https://stackoverflow.com/q/50776026/4961700 – Solar Mike Apr 09 '20 at 06:08
  • Getting Run-time error 1004 paste special method of range class failed @NareshBhople Thank you for all your help –  Apr 09 '20 at 06:13
  • Put xlPasteValuesAndNumberFormats in parenthesis as shown above in my new comment. – Naresh Apr 09 '20 at 06:16
  • hey @NareshBhople still getting the error code attached picture above. do you think because the copy method i am doing is pasting as well, it is not liking it? –  Apr 09 '20 at 06:27
  • See the line above error and keep only Rng.copy there – Naresh Apr 09 '20 at 06:28
  • Also, add `Application.CutCopyMode` = False before workbook.close – Naresh Apr 09 '20 at 06:31
  • Sorry @NareshBhople didnt see you previous message that worked perfectly. Thank you so much for your help it is really appreciated. –  Apr 09 '20 at 06:42
  • Glad to hear that :) – Naresh Apr 09 '20 at 06:43

0 Answers0