Cut and Paste using Macro gives paste special method error

L

Lourens Pentz

Hi,

I have an email that is saved to a text file and then I run an excel
macro to format the data and apply formulas where needed. At the end I
copy the last 2 columns to a seperate existing excel doc, where I will
then create charts.
Now my issues come in when I copy the last two columns to transfer it
to the chart document. Digging around on the web make it seem that the
data is not saved on the clipboard when trying to paste to the chart
document giving the Method Error. I can follow the macro copying and
pasting the data, so it must be visible on the clipboard, the error
actually appears after the past action.

I get the "method error" when using range and I get the "object does
not support this property" when using activecells (see further in the
case statement).

This is mostly recorded macro modified where needed, I use WinXP pro
and Office 2003.


Can anybody shed any light on this ?

Cheers
Lourens
++++++++++++++++++++++++++++++++++++++
Sub Monthly_statistics()
'
' stats Macro
' Macro recorded
'
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.ActiveInspector.CurrentItem

Dateparm = Year(Date) & Month(Date) & Day(Date)
myMonth = Right(myItem.Subject, 2)

Workbooks.OpenText Filename:= _
"C:\temp\" & myItem.Subject & ".txt", _
Origin:=xlMSDOS, StartRow:=5, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 4), Array(2, 9), Array(3, 1), Array(9, 4), Array(11,
9), Array(12, 1), Array(18, 1) _
), TrailingMinusNumbers:=True


Range("A1").Select
ActiveCell.FormulaR1C1 = "Start Date"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Start Time"
Range("C1").Select
ActiveCell.FormulaR1C1 = "End Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "End Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Total time"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Average Time"

Lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

r = Lastrow
Col = 1


For i = 1 To r Step 1

Select Case ActiveSheet.Cells(i, Col).Value

Case Is = "AP"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "findAYCEusers (previous
day)"

Case Is = "AC"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "findAYCEusers (current day)"

Case Is = "RA"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "Radius2Arbor"

Case Is = "AO"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "AYCEoverlaps"

Case Is = "CD"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "Daily_CDR_DATA_Arch"

End Select
Next i



'populate the "E" column with the formula to calculate the total
'time of execution.

cnt = -2
Col = 5
For i = 3 To r Step 1
If ActiveSheet.Cells(i, Col - 1).Value <> "" Then
ActiveSheet.Cells(i, Col).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]>RC[-1],RC[-1]+1-RC[-3],RC[-1]-RC[-3])"
ActiveSheet.Cells(i, Col).NumberFormat = "h:mm"
Else
If ActiveSheet.Cells(i - 1, Col - 1).Value <> "" And _
ActiveSheet.Cells(i, Col - 1).Value = "" Then
ActiveSheet.Cells(i - 1, Col + 1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & -cnt &
"]C[-1]:RC[-1])"
ActiveSheet.Cells(i - 1, Col + 1).NumberFormat = "h:mm"
cnt = -2
End If
End If
If i = r Then
ActiveSheet.Cells(i, Col + 1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & -cnt &
"]C[-1]:RC[-1])"
ActiveSheet.Cells(i, Col + 1).NumberFormat = "h:mm"
cnt = -2
End If
cnt = cnt + 1
Next i


ActiveWorkbook.SaveAs Filename:= _
"c:\temp\monthly " & myItem.Subject & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Range("E2:F2").Select
Selection.Resize(r).Select
Selection.Copy



Workbooks.Open Filename:= _
"c:\temp\monthly report statistics.xls"




For i = 1 To 12 Step 1

Select Case myMonth

Case Is = "01"


Range("A2").Select
Range.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "02"
ActiveSheet.Cell(2, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "03"
ActiveSheet.Cells(2, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "04"
ActiveSheet.Cells(2, 7).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "05"
ActiveSheet.Cells(2, 9).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "06"
ActiveSheet.Cells(2, 11).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "07"
ActiveSheet.Cells(2, 13).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "08"
ActiveSheet.Cells(2, 15).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "09"
ActiveSheet.Cells(2, 17).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "10"
ActiveSheet.Cells(2, 19).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "11"
ActiveSheet.Cells(2, 21).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "12"
ActiveSheet.Cells(2, 23).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"


End Select
Next i

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"


End Sub
 
L

Lourens Pentz

Hi,

I have noticed a coment from Tom Ogilvy on some elses code at the
following site :http://www.mcse.ms/archive161-2004-1-345300.html

and I have modified the bit where I open the document "monthly report
statistics.xls" to the following :

++++++++++++++++++++++
Range("E2:F2").Resize(r).Copy

Workbooks.Open Filename:= _
"c:\temp\monthly report statistics.xls"

For i = 1 To 12 Step 1

Select Case myMonth

Case Is = "01"
Workbooks("monthly report
statistics.xls").Sheets("Sheet1").Range("A2").PasteSpecial
xlPasteValues
Selection.NumberFormat = "h:mm"

Case Is = "02"
++++++++++++++++
I still get the pastespecial error, I have also changed some
references to not select/activate the document as I have read that it
can also cause the error.

Cheers
Lourens

Hi,

I have an email that is saved to a text file and then I run an excel
macro to format the data and apply formulas where needed. At the end I
copy the last 2 columns to a seperate existing excel doc, where I will
then create charts.
Now my issues come in when I copy the last two columns to transfer it
to the chart document. Digging around on the web make it seem that the
data is not saved on the clipboard when trying to paste to the chart
document giving the Method Error. I can follow the macro copying and
pasting the data, so it must be visible on the clipboard, the error
actually appears after the past action.

I get the "method error" when using range and I get the "object does
not support this property" when using activecells (see further in the
case statement).

This is mostly recorded macro modified where needed, I use WinXP pro
and Office 2003.


Can anybody shed any light on this ?

Cheers
Lourens
++++++++++++++++++++++++++++++++++++++
Sub Monthly_statistics()
'
' stats Macro
' Macro recorded
'
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.ActiveInspector.CurrentItem

Dateparm = Year(Date) & Month(Date) & Day(Date)
myMonth = Right(myItem.Subject, 2)

Workbooks.OpenText Filename:= _
"C:\temp\" & myItem.Subject & ".txt", _
Origin:=xlMSDOS, StartRow:=5, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 4), Array(2, 9), Array(3, 1), Array(9, 4), Array(11,
9), Array(12, 1), Array(18, 1) _
), TrailingMinusNumbers:=True


Range("A1").Select
ActiveCell.FormulaR1C1 = "Start Date"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Start Time"
Range("C1").Select
ActiveCell.FormulaR1C1 = "End Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "End Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Total time"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Average Time"

Lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

r = Lastrow
Col = 1


For i = 1 To r Step 1

Select Case ActiveSheet.Cells(i, Col).Value

Case Is = "AP"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "findAYCEusers (previous
day)"

Case Is = "AC"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "findAYCEusers (current day)"

Case Is = "RA"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "Radius2Arbor"

Case Is = "AO"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "AYCEoverlaps"

Case Is = "CD"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "Daily_CDR_DATA_Arch"

End Select
Next i



'populate the "E" column with the formula to calculate the total
'time of execution.

cnt = -2
Col = 5
For i = 3 To r Step 1
If ActiveSheet.Cells(i, Col - 1).Value <> "" Then
ActiveSheet.Cells(i, Col).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]>RC[-1],RC[-1]+1-RC[-3],RC[-1]-RC[-3])"
ActiveSheet.Cells(i, Col).NumberFormat = "h:mm"
Else
If ActiveSheet.Cells(i - 1, Col - 1).Value <> "" And _
ActiveSheet.Cells(i, Col - 1).Value = "" Then
ActiveSheet.Cells(i - 1, Col + 1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & -cnt &
"]C[-1]:RC[-1])"
ActiveSheet.Cells(i - 1, Col + 1).NumberFormat = "h:mm"
cnt = -2
End If
End If
If i = r Then
ActiveSheet.Cells(i, Col + 1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & -cnt &
"]C[-1]:RC[-1])"
ActiveSheet.Cells(i, Col + 1).NumberFormat = "h:mm"
cnt = -2
End If
cnt = cnt + 1
Next i


ActiveWorkbook.SaveAs Filename:= _
"c:\temp\monthly " & myItem.Subject & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Range("E2:F2").Select
Selection.Resize(r).Select
Selection.Copy



Workbooks.Open Filename:= _
"c:\temp\monthly report statistics.xls"




For i = 1 To 12 Step 1

Select Case myMonth

Case Is = "01"


Range("A2").Select
Range.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "02"
ActiveSheet.Cell(2, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "03"
ActiveSheet.Cells(2, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "04"
ActiveSheet.Cells(2, 7).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "05"
ActiveSheet.Cells(2, 9).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "06"
ActiveSheet.Cells(2, 11).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "07"
ActiveSheet.Cells(2, 13).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "08"
ActiveSheet.Cells(2, 15).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "09"
ActiveSheet.Cells(2, 17).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "10"
ActiveSheet.Cells(2, 19).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "11"
ActiveSheet.Cells(2, 21).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "12"
ActiveSheet.Cells(2, 23).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"


End Select
Next i

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"


End Sub
Click to expand...
 
D

Dave Peterson

Maybe you could add some:
msgbox application.cutcopymode
throughout your code.

My guess is that you're copying too soon.

I think I'd try moving the .copy command after the workbooks.open line and after
all the stuff you do to the headers.

Maybe right before this line:
For i = 1 To r Step 1

But that's an untested guess.

If worse came to worse, you could always copy right before you paste for each
one (but test just moving it to see if that fixes it first.)

Lourens said:
Hi,

I have noticed a coment from Tom Ogilvy on some elses code at the
following site :http://www.mcse.ms/archive161-2004-1-345300.html

and I have modified the bit where I open the document "monthly report
statistics.xls" to the following :

++++++++++++++++++++++
Range("E2:F2").Resize(r).Copy

Workbooks.Open Filename:= _
"c:\temp\monthly report statistics.xls"

For i = 1 To 12 Step 1

Select Case myMonth

Case Is = "01"
Workbooks("monthly report
statistics.xls").Sheets("Sheet1").Range("A2").PasteSpecial
xlPasteValues
Selection.NumberFormat = "h:mm"

Case Is = "02"
++++++++++++++++
I still get the pastespecial error, I have also changed some
references to not select/activate the document as I have read that it
can also cause the error.

Cheers
Lourens

Hi,

I have an email that is saved to a text file and then I run an excel
macro to format the data and apply formulas where needed. At the end I
copy the last 2 columns to a seperate existing excel doc, where I will
then create charts.
Now my issues come in when I copy the last two columns to transfer it
to the chart document. Digging around on the web make it seem that the
data is not saved on the clipboard when trying to paste to the chart
document giving the Method Error. I can follow the macro copying and
pasting the data, so it must be visible on the clipboard, the error
actually appears after the past action.

I get the "method error" when using range and I get the "object does
not support this property" when using activecells (see further in the
case statement).

This is mostly recorded macro modified where needed, I use WinXP pro
and Office 2003.


Can anybody shed any light on this ?

Cheers
Lourens
++++++++++++++++++++++++++++++++++++++
Sub Monthly_statistics()
'
' stats Macro
' Macro recorded
'
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.ActiveInspector.CurrentItem

Dateparm = Year(Date) & Month(Date) & Day(Date)
myMonth = Right(myItem.Subject, 2)

Workbooks.OpenText Filename:= _
"C:\temp\" & myItem.Subject & ".txt", _
Origin:=xlMSDOS, StartRow:=5, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 4), Array(2, 9), Array(3, 1), Array(9, 4), Array(11,
9), Array(12, 1), Array(18, 1) _
), TrailingMinusNumbers:=True


Range("A1").Select
ActiveCell.FormulaR1C1 = "Start Date"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Start Time"
Range("C1").Select
ActiveCell.FormulaR1C1 = "End Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "End Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Total time"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Average Time"

Lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

r = Lastrow
Col = 1


For i = 1 To r Step 1

Select Case ActiveSheet.Cells(i, Col).Value

Case Is = "AP"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "findAYCEusers (previous
day)"

Case Is = "AC"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "findAYCEusers (current day)"

Case Is = "RA"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "Radius2Arbor"

Case Is = "AO"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "AYCEoverlaps"

Case Is = "CD"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "Daily_CDR_DATA_Arch"

End Select
Next i



'populate the "E" column with the formula to calculate the total
'time of execution.

cnt = -2
Col = 5
For i = 3 To r Step 1
If ActiveSheet.Cells(i, Col - 1).Value <> "" Then
ActiveSheet.Cells(i, Col).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]>RC[-1],RC[-1]+1-RC[-3],RC[-1]-RC[-3])"
ActiveSheet.Cells(i, Col).NumberFormat = "h:mm"
Else
If ActiveSheet.Cells(i - 1, Col - 1).Value <> "" And _
ActiveSheet.Cells(i, Col - 1).Value = "" Then
ActiveSheet.Cells(i - 1, Col + 1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & -cnt &
"]C[-1]:RC[-1])"
ActiveSheet.Cells(i - 1, Col + 1).NumberFormat = "h:mm"
cnt = -2
End If
End If
If i = r Then
ActiveSheet.Cells(i, Col + 1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & -cnt &
"]C[-1]:RC[-1])"
ActiveSheet.Cells(i, Col + 1).NumberFormat = "h:mm"
cnt = -2
End If
cnt = cnt + 1
Next i


ActiveWorkbook.SaveAs Filename:= _
"c:\temp\monthly " & myItem.Subject & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Range("E2:F2").Select
Selection.Resize(r).Select
Selection.Copy



Workbooks.Open Filename:= _
"c:\temp\monthly report statistics.xls"




For i = 1 To 12 Step 1

Select Case myMonth

Case Is = "01"


Range("A2").Select
Range.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "02"
ActiveSheet.Cell(2, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "03"
ActiveSheet.Cells(2, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "04"
ActiveSheet.Cells(2, 7).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "05"
ActiveSheet.Cells(2, 9).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "06"
ActiveSheet.Cells(2, 11).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "07"
ActiveSheet.Cells(2, 13).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "08"
ActiveSheet.Cells(2, 15).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "09"
ActiveSheet.Cells(2, 17).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "10"
ActiveSheet.Cells(2, 19).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "11"
ActiveSheet.Cells(2, 21).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "12"
ActiveSheet.Cells(2, 23).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"


End Select
Next i

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"


End Sub
Click to expand...
Click to expand...
 
L

Lourens Pentz

Dave,

Thanks for the reply, after some more reading, etc. It seems I have to
change my code not to use active (ie. activesheet, workbook,etc), so I
am going to visit that path.

Cheers
Lourens
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Totals An Easier Way?? 5
Help merging two VBA codes 2
Link Cells & Paste Special Macro 7
Different Results from the Same Macro 3
how to use Solver in Excel VBA 1
Cleanup this macro please 3
= Today()+1 into Sheet1, Sheet2, and Sheet3 2
works in 2007 but not in 2010 0

Top