MS Excel / VBA help thread

[code] If Target.Column = 3 Or Target.Column = 4 Then[/code]

Nicely done sir

It's going to balls up where the outputs are written though.

Nope.
Still works, same cell comma separated text

Cough. it's me. Yes. me.

I have this:

Sub HospitalFilter()
Dim LastRow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet, r As Range, iCol As Integer, t As Date, Prefix As String
Dim sh As Worksheet, Master As String
On Error Resume Next
Set r = Application.InputBox("Click in the column to extract by, i.e. Cell A1", Type:=8)
On Error GoTo 0
If r Is Nothing Then Exit Sub
iCol = r.Column
t = Now
Application.ScreenUpdating = False
With ActiveSheet

Master = .Name
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(2, 1), Cells(LastRow, LastCol)).Sort Key1:=Cells(2, iCol), Order1:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
iStart = 2
For i = 2 To LastRow
    If .Cells(i, iCol).Value <> .Cells(i + 1, iCol).Value Then
        iEnd = i
        Sheets.Add after:=Sheets(Sheets.Count)
        Set ws = ActiveSheet
        On Error Resume Next
        ws.Name = .Cells(iStart, iCol).Value
        On Error GoTo 0
        ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
        .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
         ws.Cells.EntireColumn.AutoFit
         ws.Cells.EntireRow.AutoFit

        iStart = iEnd + 1
    End If
Next i

End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Completed in " & Format(Now - t, "hh:mm:ss.00"), vbInformation

 Application.ScreenUpdating = True

End Sub

What do I need to it to make it do "now save all the files you've created based on the contents of the cell you selected at the set r= step"?

(the cell i pick will have a 3 letter code. I would like the newly generated worksheets to be split out and saved as ABC data.xlsx)

eta: actually, if it could be ABC data month.xlsx (where month is the month the file was created) that would be bostin

[code]dim myFileName as string
....
myFileName = whatevercell.value & "some other words" & Format(Now, "mm") & ".xlsx"
...
ActiveWorkbook.SaveAs Filename:=myFileName
[/code]

or something - I forget the save file bit

[Edit] That's it

That's sort of it.

The sheets are created, but i'd like new workbooks for each of the sheets. Does that make sense?

Column that i select by
aaa
aab
aac
etc
etc

I want to generate sheets of data for aaa aab aac and then have these sheets saved as separate workbooks called aaa data month.xlsx

How does that work then?

[code] Dim wb As Workbook

Sheets("Output").Copy 

Set wb = ActiveWorkbook 

With wb 
    .SaveAs ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd") 
    .Close False 
End With 

End Sub
[/code]

In your example, you could loop through the names of the sheets, or a list of names from cells, or just hardcode them into the vba.

ENGLISH please.

http://stackoverflow.com/questions/19940409/use-vba-macro-to-save-each-excel-worksheet-as-separate-workbook

Sorry.
Can I just bump that code in anywhere into my existing vba script?

I'm not sure what you're trying to do here.

The code in this post will save the sheet "Output" as a new workbook. You can name this whatever you like.

If you have a number of different sheets ,you just change the sheet name.

Alternatively you can have the sheet name populated by looping through a list (i.e. 1 - sheet "AAA", 2 - "sheet B" etc...) or just hard coded (i.e.. write it out in the code each time).

If you are trying to make a new sheet from a load of different columns, you would need to do the following (in pseudocode)

Identify the range to be copied (e.g. columns 1 to 20)
loop through the range
copy the column to a new sheet
save the new sheet as a new workbook
move on to the next column

The code for each step would pretty much be in that order

But, it depends on what you are trying to do

This was a horrible clunking bit of code that does the loop through a list of sheetnames you define and saves them in a path you define as new workbooks.

Only seems to work in Excel 2003 as it is 10 years old now

Sub ExportWorkbookSheets()
'
' Generic export Macro
' Macro recorded 12/05/03 by SMCCOLL
'
'This macro will allow individual sheets from a set of budget reports
'or any other multisheet workbook to be saved as separate files

'Macro start
Dim wsh As Worksheet 'sets a variable for later formulas,

'This section sets up the sheets to be exported by putting a list of worksheets in a new sheet in the workbook called "sheetlist"
'in column A and folder path to be saved in Column B. You can add a prefix to the filename by putting it in the path as it will
'just concatenate the sheetname

Sheets("Sheetlist").Select
FinalRow = Range("A15000").End(xlUp).Row
For i = 1 To FinalRow
    Sheets("SheetList").Select
    sheet_name = Range("A" & i).Value
    Sheet_path = Range("B" & i).Value

Set wsh = Worksheets(sheet_name)
Workbooks.Add xlWBATWorksheet 'Create a new blank excel workbook
wsh.Cells.Copy 'Copies source worksheet
ActiveSheet.Cells.Select
Application.DisplayAlerts = False
ActiveSheet.Paste 'pastes worksheet
ActiveSheet.Cells.PasteSpecial xlPasteValues 'range values it
ActiveSheet.Cells.PasteSpecial xlPasteFormats 'pastes formats
ActiveSheet.Select
With ActiveSheet.PageSetup 'formats new worksheet to print and page correctly

    .PrintTitleRows = False
    .Orientation = xlLandscape
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With 'Paste values

'this next bit saves the workbook to the path in arg1 with filename arg2

Application.ActiveWorkbook.SaveAs (Sheet_path & sheet_name)
ActiveWorkbook.Close False
'this loops the procedure so it runs through all the rows of the distribution file
'until it gets to a blank cell and stops.
Next i
Application.DisplayAlerts = True
End Sub

I have a bog standard line chart, but I would like for the data points to change colour depending on the value.

For example:

=60% (red data points)
50.01% to 59.99% (orange data points)
<=50% (green data points)

Is there any way you can do a kind of Conditional Formatting on chart data?

Thanks!

Excel 2003 by the way...

Do it at the input stage? i.e. have several series of various colours that construct themselves from your data according to various criteria?

So use min and max formulae to sort the data points into the various series?

bumpity bump.
Who in here knows R.
Well.
Like REEALLY well.
Like could be given a project and get on with it without hand holding?

?

I know someone who does R.

Not sure if they have the capacity to take on much work, but that would depend on what needs doing, i guess.

PM?

It'll be working on producing clean datasets for audit research.

Hey,

Hoping someone might be able to help me... I have been given some data in excel which sits across multiple tabs and workbooks, I need to collate all this data, which is all fine, I have written a macro that can collate the data.

The one issue I have is that the system this data comes from puts the identifier for the tab into an object embedded in each tab, and I can't work out a way of extracting this info from the object and pasting it into a cell, I have attached a simplified example, anyone got any ideas?!

1 Attachment

• excel_problem.xlsx

Pivot table help needed please!

I've got a large statistical return that I need to build about half a dozen quite complex pivot tables from. Good news - I have last year's version that I can clone these pivot tables from. Bad news - there are a handful of extra columns in this year's source data so I can't just do a straight copy & paste/clone.

Is there a straightforward solution to this?

You can create data connections to create pivot tables from multiple source spreadsheets but they all have to have identical schemas and formats for each column in each spreadsheet

Cheers for replying!

I don't need to be able to reference multiple source spreadsheets, I just need to build this year's version using last year's as a guide, if that makes sense. If I cut out the extra columns that are in this year's source data and tag them on to the end, then clone the pivot tables from last year and refresh, do you think that will work? I'm thinking if I do that, the cell references won't be buggered as the data will be in the same place. (There are a bunch of Vlookups at the end of all the data that would need re-running but I'll cross that bridge when I come to it).