Home |
Search |
Today's Posts |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a macro that includes 2 worksheets
You could have the worksheet that gets the data as the activesheet. Then ask
the user what CSV file should be imported. The bad news is when I was looking at your code, I couldn't see where the original data would be pasted. Your code depended on the activecell in that worksheet. But this may get you started: Option Explicit Sub OinkBoink2() Dim myFileName As Variant Dim CSVWks As Worksheet Dim ActWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range Dim LastRow As Long Set ActWks = ActiveSheet myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Set CSVWks = Workbooks.Open(Filename:=myFileName).Worksheets(1) With CSVWks 'you don't copy row 1, so I deleted it here. .Rows(1).Delete .Columns("A:C").Delete .Columns("B:H").Delete .Columns("F:F").Delete .Columns("G:H").Insert .Range("k1", .Cells(1, .Columns.Count)).Delete With .Columns("A:J") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False .EntireColumn.AutoFit End With 'since your recorded macro depends on the activecell, 'I can't figure out where you're pasting. 'this line needs to be fixed. Set DestCell = ActWks.Range("somethinggoeshere") Set RngToCopy = .Range("A1").CurrentRegion RngToCopy.Copy _ Destination:=DestCell 'get ready for the next copy|paste Set DestCell = ActWks.Range("K5") 'I used column I to get the last row--not 21 (or 20 after deleting 'row 1) Set RngToCopy = .Range("I1:J" & .Cells(.Rows.Count, "I").End(xlUp).Row) RngToCopy.Copy _ Destination:=DestCell End With With ActWks LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row .Range("J5:J" & LastRow).FormulaR1C1 _ = "=RC[-1]-RC[-2]" .Range("N5:N" & LastRow).FormulaR1C1 _ = "=RC[-1]*RC[-6]" .Range("O5:O" & LastRow).FormulaR1C1 _ = "=RC[-2]*RC[-5]" With .Cells(LastRow + 2, "N") .FormulaR1C1 = "=SUM(R5C:R[-2]C)" .Value = .Value End With With .Cells(LastRow + 3, "N") .FormulaR1C1 = "=SUM(R5C[1]:R[-3]C[1])" .Value = .Value End With End With End Sub I _think_ it does what you want--but I didn't test it. The code did compile, though. Richard Champlin wrote: How can I keep it from using a hard reference to that particular run of the csv file? Each time I run the csv file (e.g. to tweak the macro), the computer will give it a new version number. Is there a way of altering the code so it does not matter? Richard Sub OinkBoink() ' ' OinkBoink Macro ' Macro recorded 8/21/2007 by Richard Champlin ' ' Windows("IC270[1].csv").Activate Columns("A:C").Select Selection.Delete Shift:=xlToLeft Columns("B:H").Select Selection.Delete Shift:=xlToLeft Columns("F:F").Select Selection.Delete Shift:=xlToLeft Columns("G:H").Select Selection.Insert Shift:=xlToRight ActiveWindow.SmallScroll ToRight:=2 Columns("K:K").Select Range(Selection, Selection.End(xlToRight)).Select Selection.Delete Shift:=xlToLeft Columns("A:J").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:J").EntireColumn.AutoFit Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Cut Windows("0807 Summary.xls").Activate ActiveSheet.Paste Selection.End(xlToRight).Select Range("K5").Select Windows("IC270[1].csv").Activate Selection.End(xlToRight).Select Range(Selection, Selection.End(xlDown)).Select Range("I2:J21").Select Selection.Cut Windows("0807 Summary.xls").Activate ActiveSheet.Paste Range("J5").Select ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]" Range("J5").Select Selection.AutoFill Destination:=Range("J5:J24"), Type:=xlFillDefault Range("J5:J24").Select Range("N5").Select ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-6]" Range("N5").Select Selection.AutoFill Destination:=Range("N5:N24"), Type:=xlFillDefault Range("N5:N24").Select Range("O5").Select ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-5]" Range("O5").Select Selection.AutoFill Destination:=Range("O5:O24") Range("O5:O24").Select Selection.End(xlDown).Select Range("N25").Select ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)" Range("N25").Select Selection.AutoFill Destination:=Range("N25:O25"), Type:=xlFillDefault Range("N25:O25").Select Selection.Copy Range("N27:N28").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Application.CutCopyMode = False Range("N25:O25").Select Selection.ClearContents End Sub -- Richard Champlin Administrative Program Assistant II Children''''''''s Hospital & Regional Medical Center, Seattle "Gord Dibben" wrote: Your missing macros were probably stored in your Personal.xls file which your upgrade neglected to bring over. As far as your currently recorded macro not working, it could be some hard-coded reference to the workbook it was recorded into. Post the code so's we can make adjustments if needed. Gord Dibben MS Excel MVP On Thu, 23 Aug 2007 15:14:03 -0700, Richard Champlin wrote: Last night, my computer was upgraded to Windows XP. I found that macros I had recorded were no longer visible. The one I used most often was rather simple, so I just re-recorded it...it involves copying some data from a CSV file into an Excel spreadsheet. Before the upgrade, it worked perfectly. Now, it seems to stop functioning when I toggle between the affected files. Any ideas? -- Richard Champlin Administrative Program Assistant II Children''''s Hospital -- Dave Peterson |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a formula which includes percentages | Excel Discussion (Misc queries) | |||
How to choose from a list that includes blanks? | Excel Discussion (Misc queries) | |||
=max(range includes #N/As) | Excel Worksheet Functions | |||
Drop down list includes hyperlink | Excel Discussion (Misc queries) | |||
Cannot protect worksheet if it includes a list | Excel Worksheet Functions |