Using a macro that includes 2 worksheets

[Richard Champlin]

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?

 

[Gord Dibben]

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

 

[Richard Champlin]

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

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

 

[Dave Peterson]

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.

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