Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
probably a relatively easy macro question....
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
probably a relatively easy macro question....
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
probably a relatively easy macro question....
Ps. You can delete this line:
Dim FoundAnError As Boolean <<snipped |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro fill down question, I think its easy | Excel Programming | |||
Easy question: Have macro ignore #N/A | Excel Programming | |||
new user with easy question? not easy for me | New Users to Excel | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
Easy question: macro to move cursor one to the right | Excel Programming |