0

I have tried to simplify the whole coding actually as nothing seemed to work. To recap:I have a folder with 51 Excel files. In each file there are specific variables between cell J4 and cell R4, included, that I want to tranfer into an inclusive excel workbook keeping the original format. The code that I am using is the following:

*** I have defined my variables ***
Sub LoopThroughDirectory() 
Dim MyFile As String
Dim pp As Workbook
Dim row As Integer
row = 1

***this is the folder that has my 51 excel files*** 
MyFile = Dir("C:\Users\Aaa\Desktop\Analysed Data\*.xls*")

***consider all the files until the end, sheet 1, range, copy, close***
Do While MyFile <> ""
Workbook.Open ("C:\Users\Aaa\Desktop\Analysed Data\")
Worksheets("sheet1").Select
Range("J4:R4").Select
Selection.Copy
ActiveWindow.Close

***go to my workbook, sheet 1, row 1. Select A1 and paste special values  and format***
Set pp = Workbook
Windows("pp.xlsx").Activate
Worksheets("sheet1").Cells(row, 1) = MyFile
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats,    Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

 ***do it in loop for all my excel files, and copy-pastespecial the range of values each time in the following  row*** 
 row = row + 1 
 Loop

 End Sub

it doesn't work like that either but I thought that if I get rid of the erow probably the process would be more straightforward and that if I explain what I mean with my coding, it's easier to get help. Thank you for your help. Silvia

PsychSilvia
  • 17
  • 6
  • 2
    What happens when you widen the column? Are these formulas that return time or date that could be negative due to unresolved values in the target worksheet? –  Sep 14 '17 at 16:41
  • Use Paste Special xlpastevalues. Look at this post as an example: https://stackoverflow.com/questions/23937262/excel-vba-copy-paste-values-only-xlpastevalues – Ibo Sep 14 '17 at 16:47
  • If I widen the column I have the numerical values but not in the right format in term of decimal places. The formulas simply compute the average score and the sum of certain positive values, I don't have any negative result. I tried to use the PasteSpecial but now I have got another error saying "invalid use of property". – PsychSilvia Sep 14 '17 at 17:14
  • Do you just need the formatting to change or are the numbers calculated wrong – Cocoberry2526 Sep 14 '17 at 19:15
  • Just the formatting to change. "PasteSpecial xlpastevalues" seems to be the right command but I can't add it to my code without changing the whole thing and I am not so experienced to manage to do it. – PsychSilvia Sep 15 '17 at 07:49

0 Answers0