?
Solved

Add additional columns

Posted on 2011-10-22
4
Medium Priority
?
200 Views
Last Modified: 2012-05-12
I also want to add to each persons score going across the page the cells (see attached sheet and view code).
The sheet currently collates the scores from a number of worksheets in a folder.  I have added the cells I want to add to that Master going across the page.
Master.xls
0
Comment
Question by:Dier02
  • 2
3 Comments
 
LVL 5

Expert Comment

by:GirardAndrew
ID: 37012631
Hi Dier02,

First of all, I have corrected your codes. I believe the Copy and Past Values function for columns 3 and 4 was not correct. Please refer to the code below for a fix.

Thanks,
Girard Andrew
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
       rw = rw + 1
       FileName = Dir
   Loop
   
   Set IndexSheet = Nothing
End Sub

Open in new window

0
 
LVL 5

Accepted Solution

by:
GirardAndrew earned 2000 total points
ID: 37012638
Hi Dier02,

As for the additional columns, all you need to do is repeat the 3 line of codes and replace the column target and source cell. For the example below, I have added 2 columns for the target (columns 5 and 6) and used the data from cells C3 and C4 respectively.

I hope you will find this useful.

Sincerely,
Girard Andrew
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 & "'!C3"
       IndexSheet.Cells(rw, 5).Copy
       IndexSheet.Cells(rw, 5).PasteSpecial Paste:=xlPasteValues
       
       IndexSheet.Cells(rw, 6).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!C4"
       IndexSheet.Cells(rw, 6).Copy
       IndexSheet.Cells(rw, 6).PasteSpecial Paste:=xlPasteValues
       rw = rw + 1
       FileName = Dir
   Loop
   
   Set IndexSheet = Nothing
End Sub

Open in new window

0
 
LVL 50
ID: 37419396
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question