probably a relatively easy macro question....

[wishboneblog]

Hi,
I'm totally new to macros in Excel and I'm hoping there's an easy
solution to my problem. Any help is much appreciated....

Here's my problem:

I have a workbook.
*Each sheet in the workbook is a day in a tour itinerary, formatted in
such a way that it'll look pretty when printed out.
*Each sheet is layed out in the same way, eg. Cell D4 is the venue
name, Cell F5 is the street address, etc.
*The number of sheets may change from tour to tour

I want to end up with a CSV file in which each line is the relevant
data from each sheet. My plan was to do the following:

*create 2 new sheets at the end of the workbook (I can do this bit).
Let's say they're sheet25 and sheet26
*on the first row of the first new sheet type the CSV headers across
the top (so A1 = "venue_name", A2="venue_phone", A3="venue_postcode",
etc)
*on the row beneath enter the relevant reference cell for the data I
need (so if the venue name appears in cell D4 on each sheet then cell
B2 will say "D4")

I want the macro to act on the second new sheet and do the following:
* figure out how many columns contain information (or I could just
write this value in another cell which the macro refers to)
*for all cells in row A which contain information - set sheet26.Ax
equal to sheet25.Ax (i.e. copy the first header row)

*then something like....

for ($i=0 ; $i<FinalSheet ; $i++){
for ($j=0 ; $j<FinalHeaderCol ; $j++){
Cell(row=A+$i , col=$j) = Sheet($i).Cell(ref = sheet25.B$j) //
so using the value written under the header row as a reference for the
cell to take the value from

}
}



I wish I could explain it better. I'm sure it's not a difficult task,
my problem is knowing the right syntax to use and how to take a value
from a Cell and use that as a reference for the lookup of another
cell.


Thanks for your help,

Joe

 

[Dave Peterson]

I don't think you'll need that second additional worksheet. The macro could
create this on the fly.

In fact, it would be easier to create the .csv file if the macro created a new
worksheet in a new workbook.

I named the worksheet that held the Layout nicely (as Layout).

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim LayoutWks As Worksheet
Dim CSVWks As Worksheet
Dim TestRng As Range
Dim FoundAnError As Boolean
Dim LastCol As Long
Dim iCol As Long
Dim oRow As Long

'name the sheet with the layout nicely:
Set LayoutWks = Worksheets("Layout")

With LayoutWks
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
'look for typos in those addresses in row 2
For iCol = 1 To LastCol
Set TestRng = Nothing
On Error Resume Next
Set TestRng = .Range(.Cells(2, iCol).Value)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "Invalid address in: " _
& .Cells(2, iCol).Address(0, 0)
Exit Sub 'stop looking for any more typos
End If
Next iCol

'create a new workbook with a single sheet
Set CSVWks = Workbooks.Add(1).Worksheets(1)

'add that header to the CSV worksheet
.Rows(1).Copy _
Destination:=CSVWks.Range("a1")

oRow = 1
For Each wks In ThisWorkbook.Worksheets
If wks.Name = .Name Then
'skip it!
Else
oRow = oRow + 1
'do the real work
For iCol = 1 To LastCol
'just plop the value or worry about the formatting??
CSVWks.Cells(oRow, iCol).NumberFormat _
= wks.Range(.Cells(2, iCol).Value).NumberFormat
CSVWks.Cells(oRow, iCol).Value _
= wks.Range(.Cells(2, iCol).Value).Value
Next iCol
'nice for testing. Delete when you're ready
CSVWks.Cells(oRow, LastCol + 1).Value = wks.Name
End If
Next wks
End With

With CSVWks.Parent
'overwrite existing .csv file with no alert
Application.DisplayAlerts = False
.SaveAs Filename:="C:\mycsvfile.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
'close the file without saving
.Close savechanges:=False
End With

End Sub

 

[Dave Peterson]

Ps. You can delete this line:
Dim FoundAnError As Boolean

<<snipped>>