Recorded Macro

I have a number of raw csv files of data for various data sets covering various periods (i.e. number of rows).  I have recorded a macro for one of the csv files to format the raw csv file so compatible with excel and converted to a Table so able to analyse with pivot tables.  The macro inserts some additional columns with formula into the workbook.  Some of these formula are linked to some indexes from a separate “Index” sheet which I manually copy and paste into the csv workbook.

1.      As can see from code below the macro was created on a sheet with 83414 rows of data – how can I modify the code so any inserted formula will work for all data rows and not stop at row 83414 as other data sets have more and less rows.?

2.      How can I make sure the macro will run on any workbook i.e. need ensure not linked to specific sheet name etc so will run whenever activated irrespective of sheet  or workbook name?  

3.      Attempts to record in the macro inserting of the sheet “Index” with its contents and formulas failed – hence resorted to inserting manually before running macro .  Is there a way around this so able to insert the sheet as part of the macro? Maybe insert from separate saved DataBuoyIndex.xlsx file path on PC?

4.      How can I add code so modified csv is saved as and xlsx file?

5.      Any suggested improvements to shorten code?

Thanks


Sub DataBuoyMacro()
'
    Cells.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:R").Select
    Columns("A:R").EntireColumn.AutoFit
    Columns("P:P").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "FilterDaate"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "FilterDate"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "DirText"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Speed Group"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Height Group"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Period Group"
    Columns("B:B").Select
    Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveWindow.ScrollRow = 83178
    ActiveWindow.ScrollRow = 82636
    ActiveWindow.ScrollRow = 82094
    ActiveWindow.ScrollRow = 80142
    ActiveWindow.ScrollRow = 75153
    ActiveWindow.ScrollRow = 70490
    ActiveWindow.ScrollRow = 59103
    ActiveWindow.ScrollRow = 50102
    ActiveWindow.ScrollRow = 38174
    ActiveWindow.ScrollRow = 36005
    ActiveWindow.ScrollRow = 31667
    ActiveWindow.ScrollRow = 30149
    ActiveWindow.ScrollRow = 28956
    ActiveWindow.ScrollRow = 27438
    ActiveWindow.ScrollRow = 25486
    ActiveWindow.ScrollRow = 24184
    ActiveWindow.ScrollRow = 22991
    ActiveWindow.ScrollRow = 21148
    ActiveWindow.ScrollRow = 19846
    ActiveWindow.ScrollRow = 17569
    ActiveWindow.ScrollRow = 15942
    ActiveWindow.ScrollRow = 15400
    ActiveWindow.ScrollRow = 14641
    ActiveWindow.ScrollRow = 13773
    ActiveWindow.ScrollRow = 13231
    ActiveWindow.ScrollRow = 12689
    ActiveWindow.ScrollRow = 12038
    ActiveWindow.ScrollRow = 11388
    ActiveWindow.ScrollRow = 10954
    ActiveWindow.ScrollRow = 10737
    ActiveWindow.ScrollRow = 10520
    ActiveWindow.ScrollRow = 9978
    ActiveWindow.ScrollRow = 9544
    ActiveWindow.ScrollRow = 9110
    ActiveWindow.ScrollRow = 8785
    ActiveWindow.ScrollRow = 8568
    ActiveWindow.ScrollRow = 8243
    ActiveWindow.ScrollRow = 8026
    ActiveWindow.ScrollRow = 7917
    ActiveWindow.ScrollRow = 7809
    ActiveWindow.ScrollRow = 7701
    ActiveWindow.ScrollRow = 7375
    ActiveWindow.ScrollRow = 7267
    ActiveWindow.ScrollRow = 6941
    ActiveWindow.ScrollRow = 6725
    ActiveWindow.ScrollRow = 6399
    ActiveWindow.ScrollRow = 6291
    ActiveWindow.ScrollRow = 5965
    ActiveWindow.ScrollRow = 5640
    ActiveWindow.ScrollRow = 5423
    ActiveWindow.ScrollRow = 5206
    ActiveWindow.ScrollRow = 4989
    ActiveWindow.ScrollRow = 4773
    ActiveWindow.ScrollRow = 4339
    ActiveWindow.ScrollRow = 4013
    ActiveWindow.ScrollRow = 3688
    ActiveWindow.ScrollRow = 3254
    ActiveWindow.ScrollRow = 2712
    ActiveWindow.ScrollRow = 2387
    ActiveWindow.ScrollRow = 2061
    ActiveWindow.ScrollRow = 1845
    ActiveWindow.ScrollRow = 1302
    ActiveWindow.ScrollRow = 977
    ActiveWindow.ScrollRow = 652
    ActiveWindow.ScrollRow = 326
    ActiveWindow.ScrollRow = 1
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="T", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="Z", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.NumberFormat = "m/d/yyyy h:mm"
    ActiveWindow.ScrollRow = 83287
    ActiveWindow.ScrollRow = 82961
    ActiveWindow.ScrollRow = 82527
    ActiveWindow.ScrollRow = 80467
    ActiveWindow.ScrollRow = 73527
    ActiveWindow.ScrollRow = 72334
    ActiveWindow.ScrollRow = 69297
    ActiveWindow.ScrollRow = 67887
    ActiveWindow.ScrollRow = 65176
    ActiveWindow.ScrollRow = 63658
    ActiveWindow.ScrollRow = 62791
    ActiveWindow.ScrollRow = 61272
    ActiveWindow.ScrollRow = 59971
    ActiveWindow.ScrollRow = 58561
    ActiveWindow.ScrollRow = 57043
    ActiveWindow.ScrollRow = 55525
    ActiveWindow.ScrollRow = 53790
    ActiveWindow.ScrollRow = 52380
    ActiveWindow.ScrollRow = 47934
    ActiveWindow.ScrollRow = 45439
    ActiveWindow.ScrollRow = 43054
    ActiveWindow.ScrollRow = 40017
    ActiveWindow.ScrollRow = 38174
    ActiveWindow.ScrollRow = 36222
    ActiveWindow.ScrollRow = 34703
    ActiveWindow.ScrollRow = 33510
    ActiveWindow.ScrollRow = 32318
    ActiveWindow.ScrollRow = 31125
    ActiveWindow.ScrollRow = 30149
    ActiveWindow.ScrollRow = 28739
    ActiveWindow.ScrollRow = 27329
    ActiveWindow.ScrollRow = 26245
    ActiveWindow.ScrollRow = 25052
    ActiveWindow.ScrollRow = 24293
    ActiveWindow.ScrollRow = 23100
    ActiveWindow.ScrollRow = 22124
    ActiveWindow.ScrollRow = 21256
    ActiveWindow.ScrollRow = 20605
    ActiveWindow.ScrollRow = 20280
    ActiveWindow.ScrollRow = 19846
    ActiveWindow.ScrollRow = 19087
    ActiveWindow.ScrollRow = 18653
    ActiveWindow.ScrollRow = 18111
    ActiveWindow.ScrollRow = 17677
    ActiveWindow.ScrollRow = 17027
    ActiveWindow.ScrollRow = 16376
    ActiveWindow.ScrollRow = 16051
    ActiveWindow.ScrollRow = 15617
    ActiveWindow.ScrollRow = 15075
    ActiveWindow.ScrollRow = 14749
    ActiveWindow.ScrollRow = 14533
    ActiveWindow.ScrollRow = 14316
    ActiveWindow.ScrollRow = 14099
    ActiveWindow.ScrollRow = 13990
    ActiveWindow.ScrollRow = 13557
    ActiveWindow.ScrollRow = 13123
    ActiveWindow.ScrollRow = 12906
    ActiveWindow.ScrollRow = 12581
    ActiveWindow.ScrollRow = 12472
    ActiveWindow.ScrollRow = 12255
    ActiveWindow.ScrollRow = 12038
    ActiveWindow.ScrollRow = 11821
    ActiveWindow.ScrollRow = 11713
    ActiveWindow.ScrollRow = 11605
    ActiveWindow.ScrollRow = 11388
    ActiveWindow.ScrollRow = 11279
    ActiveWindow.ScrollRow = 10845
    ActiveWindow.ScrollRow = 10412
    ActiveWindow.ScrollRow = 10086
    ActiveWindow.ScrollRow = 9869
    ActiveWindow.ScrollRow = 9436
    ActiveWindow.ScrollRow = 9110
    ActiveWindow.ScrollRow = 8677
    ActiveWindow.ScrollRow = 8134
    ActiveWindow.ScrollRow = 7484
    ActiveWindow.ScrollRow = 7050
    ActiveWindow.ScrollRow = 6508
    ActiveWindow.ScrollRow = 6074
    ActiveWindow.ScrollRow = 4989
    ActiveWindow.ScrollRow = 4447
    ActiveWindow.ScrollRow = 4013
    ActiveWindow.ScrollRow = 3688
    ActiveWindow.ScrollRow = 3254
    ActiveWindow.ScrollRow = 2604
    ActiveWindow.ScrollRow = 2278
    ActiveWindow.ScrollRow = 1736
    ActiveWindow.ScrollRow = 1194
    ActiveWindow.ScrollRow = 760
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 1
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "UTC"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = _
        "=YEAR(RC[-1]) & "" - "" & CHOOSE(MONTH(RC[-1]), ""January"", ""February"", ""March"", ""April"", ""May"", ""June"", ""July"", ""August"", ""September"", ""October"", ""November"", ""December"")"
    Range("C3").Select
    Selection.AutoFill Destination:=Range("C3:C83414")
    Range("C3:C83414").Select
    Range("F3").Select
    ActiveCell.FormulaR1C1 = _
        "=CHOOSE(1+ROUND(RC[-1]/22.5,0),""N"",""NNE"",""NE"",""ENE"",""E"",""ESE"",""SE"",""SSE"",""S"",""SSW"",""SW"",""WSW"",""W"",""WNW"",""NW"",""NNW"",""N"")"
    Range("F3").Select
    Selection.AutoFill Destination:=Range("F3:F83414")
    Range("F3:F83414").Select
    Range("H3").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Index!R18C3:R28C3,MATCH(R[-1]C[-1],Index!R18C2:R28C2,1))"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Index!R18C3:R28C3,MATCH(RC[-1],Index!R18C2:R28C2,1))"
    Range("H3").Select
    Selection.AutoFill Destination:=Range("H3:H83414")
    Range("H3:H83414").Select
    Range("K3").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Index!R4C9:R15C9,MATCH(RC[-1],Index!R4C8:R15C8,1))"
    Range("K3").Select
    Selection.AutoFill Destination:=Range("K3:K83414")
    Range("K3:K83414").Select
    Range("M3").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Index!R4C3:R14C3,MATCH(RC[-1],Index!R4C2:R14C2,1))"
    Range("M3").Select
    Selection.AutoFill Destination:=Range("M3:M83414")
    Range("M3:M83414").Select
    Range("A1:V16").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$W$83414"), , xlNo).Name = _
        "Table1"
    Range("Table1[#All]").Select
End Sub

Open in new window

PVR101Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCost AccountantCommented:
OK... first delete all scrolls.
Then where you see .select   selection. delete to leave one line with a dot.
This leaves this shorter code:
Sub DataBuoyMacro()

With Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:R").EntireColumn.AutoFit
Columns("P:P").Delete Shift:=xlToLeft
Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("K:K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("M:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").FormulaR1C1 = "FilterDaate"
Range("C1").FormulaR1C1 = "FilterDate"
Range("F1").FormulaR1C1 = "DirText"
Range("H1").FormulaR1C1 = "Speed Group"
Range("K1").FormulaR1C1 = "Height Group"
Range("M1").FormulaR1C1 = "Period Group"
Columns("B:B").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Replace What:="T", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Replace What:="Z", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").NumberFormat = "m/d/yyyy h:mm"
Range("B1").FormulaR1C1 = "Time"
Range("B2").FormulaR1C1 = "UTC"
Range("C3").FormulaR1C1 = _
"=YEAR(RC[-1]) & "" - "" & CHOOSE(MONTH(RC[-1]), ""January"", ""February"", ""March"", ""April"", ""May"", ""June"", ""July"", ""August"", ""September"", ""October"", ""November"", ""December"")"
Range("C3").AutoFill Destination:=Range("C3:C83414")
Range("C3:C83414").Select
Range("F3").FormulaR1C1 = _
"=CHOOSE(1+ROUND(RC[-1]/22.5,0),""N"",""NNE"",""NE"",""ENE"",""E"",""ESE"",""SE"",""SSE"",""S"",""SSW"",""SW"",""WSW"",""W"",""WNW"",""NW"",""NNW"",""N"")"
Range("F3").AutoFill Destination:=Range("F3:F83414")
Range("H3").FormulaR1C1 = _
"=INDEX(Index!R18C3:R28C3,MATCH(R[-1]C[-1],Index!R18C2:R28C2,1))"
Range("H3").FormulaR1C1 = _
"=INDEX(Index!R18C3:R28C3,MATCH(RC[-1],Index!R18C2:R28C2,1))"
Range("H3").AutoFill Destination:=Range("H3:H83414")
Range("K3").FormulaR1C1 = _
"=INDEX(Index!R4C9:R15C9,MATCH(RC[-1],Index!R4C8:R15C8,1))"
Range("K3").AutoFill Destination:=Range("K3:K83414")
Range("M3").FormulaR1C1 = _
"=INDEX(Index!R4C3:R14C3,MATCH(RC[-1],Index!R4C2:R14C2,1))"
Range("M3").AutoFill Destination:=Range("M3:M83414")
Range("A1:V16").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$W$83414"), , xlNo).Name = _
"Table1"
Range("Table1[#All]").Select
End Sub

Open in new window


from here we can shorten further.
But this first couple of steps will make the code far simpler.
yarwellCommented:
" How can I add code so modified csv is saved as and xlsx file?" - in Excel on the PC you would Save As... select Excel workbook. Recorded as a macro it looks like :-

ActiveWorkbook.SaveAs Filename:="C:\Users\xxxxxxxxxxxxxx\fred.xls", _
        FileFormat:=xlNormal
PVR101Author Commented:
thanks Steve - was wondering if need all the scrolling.  

Code works in formating sheet and 'calls' for me to select the "Index" sheet file path location a number of times (counted 7 times i think) before completing but as before Table calcs end on row 83415.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Martin LissOlder than dirtCommented:
Change all the lines of code that look like this

Selection.AutoFill Destination:=Range("C3:C83414")

to

Selection.AutoFill Destination:=Range("C3:C" & ActiveSheet.UsedRange.Rows.Count)
PVR101Author Commented:
yarwell  - thanks will apply to code and test

Martin - i changed above line and attempted to correct line imediately below but got error ;

 Selection.AutoFill Destination:=Range("C3:C" & ActiveSheet.UsedRange.Rows.Count)
    Range("C3:C").Select
    ActiveWindow.SmallScroll Down:=303
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F3").Select
    ActiveCell.FormulaR1C1 = _
        "=CHOOSE(1+ROUND(RC[-1]/22.5,0),""N"",""NNE"",""NE"",""ENE"",""E"",""ESE"",""SE"",""SSE"",""S"",""SSW"",""SW"",""WSW"",""W"",""WNW"",""NW"",""NNW"",""N"")"
    Range("F3").Select
    Selection.AutoFill Destination:=Range("F3:F" & ActiveSheet.UsedRange.Rows.Count)
    Range("F3:F").Select
    With Selection

Open in new window


will be similar issue with line below range F3:F line
Martin LissOlder than dirtCommented:
Change

Range("C3:C").Select

to

Range("C3:C" & ActiveSheet.UsedRange.Rows.Count).Select

And similarly for column F, etc.

Range("F3:F" & ActiveSheet.UsedRange.Rows.Count).Select
PVR101Author Commented:
OK Think getting myself tied in knots a bit as errors - here is full modified code as stands.  Appreciate if can review against original and correct accordingly.

Sub DataBuoyFormattingComplete()
'
' DataBuoyFormattingComplete Macro
' MI data formatting and column insertion for Pivot tables etc
'

'Sub DataBuoyMacro()

With Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:R").EntireColumn.AutoFit
Columns("P:P").Delete Shift:=xlToLeft
Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("K:K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("M:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").FormulaR1C1 = "FilterDate"
Range("F1").FormulaR1C1 = "DirText"
Range("H1").FormulaR1C1 = "Speed Group"
Range("K1").FormulaR1C1 = "Height Group"
Range("M1").FormulaR1C1 = "Period Group"
Columns("B:B").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Replace What:="T", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Replace What:="Z", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").NumberFormat = "m/d/yyyy h:mm"
Range("B1").FormulaR1C1 = "Time"
Range("B2").FormulaR1C1 = "UTC"
Range("C3").FormulaR1C1 = _
"=YEAR(RC[-1]) & "" - "" & CHOOSE(MONTH(RC[-1]), ""January"", ""February"", ""March"", ""April"", ""May"", ""June"", ""July"", ""August"", ""September"", ""October"", ""November"", ""December"")"
Selection.AutoFill Destination:=Range("C3:C" & ActiveSheet.UsedRange.Rows.Count)
    Range("C3:C" & ActiveSheet.UsedRange.Rows.Count).Select
Range("F3").FormulaR1C1 = _
"=CHOOSE(1+ROUND(RC[-1]/22.5,0),""N"",""NNE"",""NE"",""ENE"",""E"",""ESE"",""SE"",""SSE"",""S"",""SSW"",""SW"",""WSW"",""W"",""WNW"",""NW"",""NNW"",""N"")"
Selection.AutoFill Destination:=Range("F3:F" & ActiveSheet.UsedRange.Rows.Count)
Range("H3").FormulaR1C1 = _
"=INDEX(Index!R18C3:R28C3,MATCH(R[-1]C[-1],Index!R18C2:R28C2,1))"
Range("H3").FormulaR1C1 = _
"=INDEX(Index!R18C3:R28C3,MATCH(RC[-1],Index!R18C2:R28C2,1))"
Selection.AutoFill Destination:=Range("H3:H" & ActiveSheet.UsedRange.Rows.Count)
Range("K3").FormulaR1C1 = _
"=INDEX(Index!R4C9:R15C9,MATCH(RC[-1],Index!R4C8:R15C8,1))"
Selection.AutoFill Destination:=Range("K3:K" & ActiveSheet.UsedRange.Rows.Count)
Range("M3").FormulaR1C1 = _
"=INDEX(Index!R4C3:R14C3,MATCH(RC[-1],Index!R4C2:R14C2,1))"
Selection.AutoFill Destination:=Range("M3:M" & ActiveSheet.UsedRange.Rows.Count)
Range("A1:V16").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$W$83414"), , xlNo).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveWorkbook.SaveAs Filename:="C:\Users\xxx.DOMAIN\Documents\DATABUOY.xls", _
        FileFormat:=xlNormal
End Sub

Open in new window


first time messing with marco's so easily confused afraid.

Yarwell file save appeared to work but was as xls not xlsx format and got compatibility messages do i just change to xlsx or need modify xlNormal also?
Martin LissOlder than dirtCommented:
Change line 67/68 to

ActiveWorkbook.SaveAs Filename:="C:\Users\xxx.DOMAIN\Documents\DATABUOY.xls", _
        FileExtStr:= ".xlsx": FileFormatNum:= 51
SteveCost AccountantCommented:
Rather than autofill... simply place the formula in the range to be filled:

Range("C3").FormulaR1C1 = _
"=YEAR(RC[-1]) & "" - "" & CHOOSE(MONTH(RC[-1]), ""January"", ""February"", ""March"", ""April"", ""May"", ""June"", ""July"", ""August"", ""September"", ""October"", ""November"", ""December"")"
Selection.AutoFill Destination:=Range("C3:C" & ActiveSheet.UsedRange.Rows.Count)

Open in new window


becomes:
Range("C3:C" & ActiveSheet.UsedRange.Rows.Count).FormulaR1C1 = _
"=YEAR(RC[-1]) & "" - "" & CHOOSE(MONTH(RC[-1]), ""January"", ""February"", ""March"", ""April"", ""May"", ""June"", ""July"", ""August"", ""September"", ""October"", ""November"", ""December"")"

Open in new window

SteveCost AccountantCommented:
Or even use TEXT:
Range("C3:C" & ActiveSheet.UsedRange.Rows.Count).FormulaR1C1 = _
"=TEXT(RC[-1],""yyyy-mmmm"")"

Open in new window

PVR101Author Commented:
guys thanks for assistance but after sometime troubleshooting i am frankly a bit lost with suggested changes and attempt to implement. attached is table detailing process and latest code but getting errors again. also attached is sample csv file to run macro on.

can I please ask someone if they can provide the full code based on original macro steps  with all improvements added.
Raw-Data.csv
MACRO-SHEET.xlsm
SteveCost AccountantCommented:
OK the attached code will do most of what you want, but you need to post the index list for it to be added to the file.

I would tend to run the macro from the file with the index...
So that you have a file dialog asking which csv to convert.
Then run the code and save the modified file automatically.

If you post the index file I can do the rest.

Here is the current tidy code:
Sub DataBuoyMacro()

With Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:R").EntireColumn.AutoFit
Columns("P:P").Delete Shift:=xlToLeft
Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("K:K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("M:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").FormulaR1C1 = "FilterDate"
Range("F1").FormulaR1C1 = "DirText"
Range("H1").FormulaR1C1 = "Speed Group"
Range("K1").FormulaR1C1 = "Height Group"
Range("M1").FormulaR1C1 = "Period Group"
Columns("B:B").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Replace What:="T", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Replace What:="Z", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").NumberFormat = "m/d/yyyy h:mm"
Range("B1").FormulaR1C1 = "Time"
Range("B2").FormulaR1C1 = "UTC"
Range("C3:C" & ActiveSheet.UsedRange.Rows.Count).FormulaR1C1 = _
"=YEAR(RC[-1]) & "" - "" & CHOOSE(MONTH(RC[-1]), ""January"", ""February"", ""March"", ""April"", ""May"", ""June"", ""July"", ""August"", ""September"", ""October"", ""November"", ""December"")"
Range("F3:F" & ActiveSheet.UsedRange.Rows.Count).FormulaR1C1 = _
"=CHOOSE(1 + ROUND(RC[-1]/22.5,0),""N"",""NNE"",""NE"",""ENE"",""E"",""ESE"",""SE"",""SSE"",""S"",""SSW"",""SW"",""WSW"",""W"",""WNW"",""NW"",""NNW"",""N"")"
'Range("H3:H" & ActiveSheet.UsedRange.Rows.Count).FormulaR1C1 = _
=INDEX(Index!R18C3:R28C3,MATCH(R[-1]C[-1],Index!R18C2:R28C2,1))
'Range("H3:H" & ActiveSheet.UsedRange.Rows.Count).FormulaR1C1 = _
=INDEX(Index!R18C3:R28C3,MATCH(RC[-1],Index!R18C2:R28C2,1))
'Range("K3:K" & ActiveSheet.UsedRange.Rows.Count).FormulaR1C1 = _
=INDEX(Index!R4C9:R15C9,MATCH(RC[-1],Index!R4C8:R15C8,1))
'Range("M3:M" & ActiveSheet.UsedRange.Rows.Count).FormulaR1C1 = _
=INDEX(Index!R4C3:R14C3,MATCH(RC[-1],Index!R4C2:R14C2,1))
Rows("2:2").Delete
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.UsedRange, , xlYes)

ActiveWorkbook.SaveAs Replace(ActiveWorkbook.FullName, ".csv", ".xlsx"), 51

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveCost AccountantCommented:
You can also try: the variation for the month grouping:
Range("C3:C" & ActiveSheet.UsedRange.Rows.Count).FormulaR1C1 = _
"=text(RC[-1],""yyyy - mmmm"")"

Open in new window

PVR101Author Commented:
steve - huge thanks for indulging me - attached is index file.
Index.xlsx
SteveCost AccountantCommented:
The code below can be ran without the need for the Index workbook.
I have converted each of your lookups to a function (which could be called like any other Excel Function if you choose).
Sub DataBuoyMacro()

Application.ScreenUpdating = False

Dim wb As Workbook
Set wb = Workbooks.Open(get_user_specified_filepath(), ReadOnly:=True)

With wb.Sheets(1)
    With .Cells
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

.Rows("2:2").Delete

.Columns("A:R").EntireColumn.AutoFit
.Columns("P:P").Delete Shift:=xlToLeft
.Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("K:K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("M:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Range("C1").FormulaR1C1 = "FilterDate"
.Range("F1").FormulaR1C1 = "DirText"
.Range("H1").FormulaR1C1 = "Speed Group"
.Range("K1").FormulaR1C1 = "Height Group"
.Range("M1").FormulaR1C1 = "Period Group"

myNumberRows = .UsedRange.Rows.Count

.Range("B2:B" & myNumberRows).Replace " ", "", xlPart, xlByRows, False, False, False
.Range("B2:B" & myNumberRows).Replace "T", " ", xlPart, xlByRows, False, False, False
.Range("B2:B" & myNumberRows).Replace "Z", "", xlPart, xlByRows, False, False, False
.Range("B2:B" & myNumberRows).NumberFormat = "m/d/yyyy h:mm"

On Error Resume Next
For x = 2 To myNumberRows
    .Range("C" & x).Value = Format(.Range("B" & x).Value, "yyyy - Mmmm")
    .Range("F" & x).Value = "'" & WindDirectionAbr(.Range("E" & x).Value)
    .Range("H" & x).Value = "'" & WindSpeedGroup(.Range("G" & x).Value)
    .Range("K" & x).Value = "'" & HeightGroup(.Range("J" & x).Value)
    .Range("M" & x).Value = "'" & PeriodGroup(.Range("L" & x).Value)
Next x
On Error GoTo 0
Set tbl = .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes)

End With

wb.SaveAs Replace(wb.FullName, ".csv", ".xlsx"), 51

wb.Close

Set wb = Nothing

Application.ScreenUpdating = True

MsgBox "Macro completed"

End Sub

Function WindDirectionAbr(Direction As Double) As String
    myIndex = Round(Direction / 22.5, 0)
    myDirections = Split("N|NNE|NE|ENE|E|ESE|SE|SSE|S|SSW|SW|WSW|W|WNW|NW|NNW|N", "|")
    WindDirectionAbr = myDirections(myIndex)
End Function

Function WindSpeedGroup(WindSpeed As Double) As String
    myIndex = Int(WindSpeed / 5)
    mySpeedGroups = Split("0 - 5|5 - 10|10 - 15|15 - 20|20 - 25|25 - 30|30 - 35|35 - 40|40 - 45|45 - 50|>=50", "|")
    WindSpeedGroup = mySpeedGroups(myIndex)
End Function

Function HeightGroup(myHeight As Double) As String
    If myHeight < 1 Then
        HeightGroup = "<1"
    ElseIf myHeight >= 10 Then
        HeightGroup = ">=10"
    ElseIf myHeight >= 2 And myHeight < 2.5 Then
        HeightGroup = "2 - 2.5"
    ElseIf myHeight >= 2.5 And myHeight < 3 Then
        HeightGroup = "2.5 - 3"
    Else
        HeightGroup = Int(myHeight) & " - " & Int(myHeight) + 1
    End If
End Function

Function PeriodGroup(myPeriod As Double) As String
    If myPeriod < 4 Then
        PeriodGroup = "<4"
    ElseIf myPeriod > 13 Then
        PeriodGroup = ">13"
    Else
        PeriodGroup = Int(myPeriod) & "-" & Int(myPeriod) + 1
    End If
End Function

Private Function get_user_specified_filepath() As String
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.AllowMultiSelect = False
    fd.Filters.Add "Text Files", "*.csv", 1
    fd.FilterIndex = 1
    fd.Title = "Please select the file."
    fd.Show
    get_user_specified_filepath = fd.SelectedItems(1)
End Function

Open in new window


The code will open a file dialog for csv files and once the file is selected it will format and save the file in the same location as the same file name.
Hopefully this will perform as you wanted.

ATB
Steve
PVR101Author Commented:
Cheers Steve.

Macro appears to work great BUT afraid need to link to Index as the bin levels in index can vary depending on threshold limit defined. I have PMd you workbook so able to see what mean and links required etc.
SteveCost AccountantCommented:
Well, we can adjust the functions to marry up to the Index sheet.
This will make it easier to use.
Did everything else work OK?
PVR101Author Commented:
Once linked to Index grouping bin levels i think formatted table will work as able to cut and paste formatted table into full workbook data buoy sheet and refresh PT etc .  

in relation to PMd workbook - maybe best approach could be in full workbook with template PTs and charts etc if macro run where it asks to point to raw csv file on PC which it formats and updates according to Index sheet bin levels in workbook and then the formatted sheet in pasted into 'table1' in buoy Data sheet so all can be updated???
PVR101Author Commented:
Hi Steve - any chance to look at code again so marry index ..
SteveCost AccountantCommented:
SOrry for not geting onto this.. have just been on a Stag do for long weekend... so have not had chance to get onto this.

Will hopefully get a chance today/tomorrow.
PVR101Author Commented:
Thanks Steve - appreciated.

When reverting can I ask that you allow for the formula's on the Data Buoy sheet to remain (like original recorded macro) rather than just the text result for FilterDate,DirText,SpeedGroup etc .  

cheers
SteveCost AccountantCommented:
Will do... plan is to set up the code to run from the index file and as such the formula will be left as per original.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.