What am I doing wrong in this copy, paste macro?

[copleyr]

The following macro will go into the "Fin Review Team" tab of each of these files and copy G14:G29 and paste it into a blank sheet.

Well, it is supposed to paste it into a blank sheet. The problem is, when it pastes, nothing gets pasted, it is just blank.

What am I doing wrong here?

Thank you in advance!

Sub Copy_files() 
Dim MyPathName As String
Dim MyFileName As String
Dim NumChars As Long
Dim X As Long
Dim SummarySheet As String
Workbooks.Add
SummarySheet = ActiveWorkbook.Name
MyPathName = "C:\Users\Desktop\All/" 'Change this to the folder and filetypes you want to return
MyFileName = Dir(MyPathName)
X = 0
Do While MyFileName <> ""
    X = X + 16
    Workbooks.Open MyPathName & MyFileName, False
    Application.DisplayAlerts = False
    Sheets("Fin Review Team").Range("G14:G29").Copy
    Workbooks(SummarySheet).Activate
    Range("B" & X & ":B" & X).PasteSpecial xlPasteValuesAndNumberFormats
    Range("B" & X & ":B" & X).PasteSpecial xlPasteFormats
    Workbooks(MyFileName).Close False
    MyFileName = Dir
Application.DisplayAlerts = True
Loop
End Sub

 

[njimack]

I think the final character of your path name should be "\", not "/".

Try stepping through the code using F8 to see if the code is working as expected.

 

[jeffreybrown]

Could it be...

C:\Users\Desktop\All/

C:\Users\Desktop\All\

 

[copleyr]

Hey I just checked. That's not the issue as it still looks into the correct path. Thanks for responding

 

[copleyr]

It's official. I'm baffled. Cannot figure out why it is not pasting information!. It is highlighting all of the correct areas for pasting, but nothing is getting pasted....

 

[p45cal]

It works here!
The only thing that I can think of is if
Sheets("Fin Review Team").Range("G14:G29")
is blank in most/all of the workbooks you're opening.

 

[copleyr]

That's really weird. Every workbook has information in that range. I'm baffled.

 

[copleyr]

Actually p45Cal, I figured it out, for the files in the directory that start with "10", the information is in the column "E" range. Thanks for the help!

Would anybody be able to help with with an If-then statement in this macro, for files that begin with "10", to copy the "E" range, rather than "G"?

 

[p45cal]

for the files in the directory that start with "10", the information is in the column "E" range

Is it the folder name which begins "10" or the file name?

 

[copleyr]

Hey p45cal, it is the file name that begins with a "10"