400 error on following

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

dier02

Thread Starter
Joined
Oct 27, 2011
Messages
2
Public Sub ListWorkbooks()
Dim Directory As String
Dim FileName As String
Dim IndexSheet As Worksheet
Dim rw As Long

Directory = "" & Range("Path").Value & ""
If Left(Directory, 1) <> "\" Then
Directory = Directory & "\"
End If

rw = 10

Set IndexSheet = ThisWorkbook.ActiveSheet

FileName = Dir(Directory & "*")
Do While FileName <> ""

IndexSheet.Cells(rw, 2).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!A3"
IndexSheet.Cells(rw, 2).Copy
IndexSheet.Cells(rw, 2).PasteSpecial Paste:=xlPasteValues

IndexSheet.Cells(rw, 3).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!C1"
IndexSheet.Cells(rw, 3).Copy
IndexSheet.Cells(rw, 3).PasteSpecial Paste:=xlPasteValues

IndexSheet.Cells(rw, 4).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!C2"
IndexSheet.Cells(rw, 4).Copy
IndexSheet.Cells(rw, 4).PasteSpecial Paste:=xlPasteValues

IndexSheet.Cells(rw, 5).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!F53"
IndexSheet.Cells(rw, 5).Copy
IndexSheet.Cells(rw, 5).PasteSpecial Paste:=xlPasteValues

IndexSheet.Cells(rw, 6).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!H53"
IndexSheet.Cells(rw, 6).Copy
IndexSheet.Cells(rw, 6).PasteSpecial Paste:=xlPasteValues

IndexSheet.Cells(rw, 7).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!J53"
IndexSheet.Cells(rw, 7).Copy
IndexSheet.Cells(rw, 7).PasteSpecial Paste:=xlPasteValues

IndexSheet.Cells(rw, 8).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!L53"
IndexSheet.Cells(rw, 8).Copy
IndexSheet.Cells(rw, 8).PasteSpecial Paste:=xlPasteValues

IndexSheet.Cells(rw, 9).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!N53"
IndexSheet.Cells(rw, 9).Copy
IndexSheet.Cells(rw, 9).PasteSpecial Paste:=xlPasteValues

IndexSheet.Cells(rw, 10).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!N53"
IndexSheet.Cells(rw, 10).Copy
IndexSheet.Cells(rw, 10).PasteSpecial Paste:=xlPasteValues

IndexSheet.Cells(rw, 11).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!R53"
IndexSheet.Cells(rw, 11).Copy
IndexSheet.Cells(rw, 11).PasteSpecial Paste:=xlPasteValues

IndexSheet.Cells(rw, 12).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!T53"
IndexSheet.Cells(rw, 12).Copy
IndexSheet.Cells(rw, 12).PasteSpecial Paste:=xlPasteValues

IndexSheet.Cells(rw, 13).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!V53"
IndexSheet.Cells(rw, 13).Copy
IndexSheet.Cells(rw, 13).PasteSpecial Paste:=xlPasteValues

rw = rw + 1
FileName = Dir
Loop

Set IndexSheet = Nothing
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
 

dier02

Thread Starter
Joined
Oct 27, 2011
Messages
2
To collate data in a number of excel files. There is one open sheet and one hidden sheet and it takes the data from the hidden sheet.
 
Joined
Jul 28, 2006
Messages
1,225
Wrong:
Code:
If Left(Directory, 1) <> "\" Then
Directory = Directory & "\"
End If

FileName = Dir
Right:
Code:
If Right(Directory, 1) <> "\" Then
Directory = Directory & "\"
End If

FileName = Dir()
Jimmy
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top