pasting problems
 

Hi,
I am getting a "PasteSpecial method of Range class failed" message
when I run the following code. Could it have something to do with the
use of the code name?

Thanks
Louis
-----

Sub timesheetGenerate()

Dim LOldWb As Workbook
Dim LNewWb As Workbook
Dim x As Name

Dim wks As Worksheet
Dim CodeNameString As String

'Open existing timesheet :-)
Set LOldWb = Workbooks.Open(Filename:=UserForm1.TextBox1.Value)


'Add new workbook
Set LNewWb = Workbooks.Add


'///
'Copy sheet one of existing timesheet
LOldWb.Activate
For Each wks In LOldWb.Worksheets
CodeNameString = LCase(wks.CodeName)
If CodeNameString = "Sheet1" Then
LOldWb.Sheets(CodeNameString).Visible = True
LOldWb.Sheets(CodeNameString).Select

Cells.Select
'///replace formula with string
Selection.Replace What:="=", Replacement:="$$$$$=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
'///copy all
Selection.Copy
End If
Next wks



'///
'Paste sheet one to new timesheet
LNewWb.Activate

For Each wks In LNewWb.Worksheets
CodeNameString = wks.CodeName
If CodeNameString = "Sheet1" Then
LNewWb.Sheets(CodeNameString).Visible = True
LNewWb.Sheets(CodeNameString).Select

Cells.Select
'///paste all (values)
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'///replace string with formula

Selection.Replace What:="$$$$$=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

End If
Next wks

'\\\\
ActiveWorkbook.SaveAs Filename:=UserForm1.TextBox2.Value

'\\\\
ActiveWorkbook.Close

'\\\\
LOldWb.Activate

ActiveWorkbook.Close

End Sub

pasting problems
 

hi,
just a guess but instead of cells.select before the paste, try
cells(1,1).select
not sure if it will work but that is all i can think of. is that the only
line the compiler don't like?

Regards
FSt1

"ll" wrote:

Hi,
I am getting a "PasteSpecial method of Range class failed" message
when I run the following code. Could it have something to do with the
use of the code name?

Thanks
Louis
-----

Sub timesheetGenerate()

Dim LOldWb As Workbook
Dim LNewWb As Workbook
Dim x As Name

Dim wks As Worksheet
Dim CodeNameString As String

'Open existing timesheet :-)
Set LOldWb = Workbooks.Open(Filename:=UserForm1.TextBox1.Value)


'Add new workbook
Set LNewWb = Workbooks.Add


'///
'Copy sheet one of existing timesheet
LOldWb.Activate
For Each wks In LOldWb.Worksheets
CodeNameString = LCase(wks.CodeName)
If CodeNameString = "Sheet1" Then
LOldWb.Sheets(CodeNameString).Visible = True
LOldWb.Sheets(CodeNameString).Select

Cells.Select
'///replace formula with string
Selection.Replace What:="=", Replacement:="$$$$$=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
'///copy all
Selection.Copy
End If
Next wks



'///
'Paste sheet one to new timesheet
LNewWb.Activate

For Each wks In LNewWb.Worksheets
CodeNameString = wks.CodeName
If CodeNameString = "Sheet1" Then
LNewWb.Sheets(CodeNameString).Visible = True
LNewWb.Sheets(CodeNameString).Select

Cells.Select
'///paste all (values)
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'///replace string with formula

Selection.Replace What:="$$$$$=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

End If
Next wks

'\\\\
ActiveWorkbook.SaveAs Filename:=UserForm1.TextBox2.Value

'\\\\
ActiveWorkbook.Close

'\\\\
LOldWb.Activate

ActiveWorkbook.Close

End Sub

pasting problems
 

Your Cells.Select is selecting all the cells on your workbook. It is
impossible to paste to it. Select just one cell and do the paste.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"ll" wrote in message
oups.com...
Hi,
I am getting a "PasteSpecial method of Range class failed" message
when I run the following code. Could it have something to do with the
use of the code name?

Thanks
Louis
-----

Sub timesheetGenerate()

Dim LOldWb As Workbook
Dim LNewWb As Workbook
Dim x As Name

Dim wks As Worksheet
Dim CodeNameString As String

'Open existing timesheet :-)
Set LOldWb = Workbooks.Open(Filename:=UserForm1.TextBox1.Value)


'Add new workbook
Set LNewWb = Workbooks.Add


'///
'Copy sheet one of existing timesheet
LOldWb.Activate
For Each wks In LOldWb.Worksheets
CodeNameString = LCase(wks.CodeName)
If CodeNameString = "Sheet1" Then
LOldWb.Sheets(CodeNameString).Visible = True
LOldWb.Sheets(CodeNameString).Select

Cells.Select
'///replace formula with string
Selection.Replace What:="=", Replacement:="$$$$$=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
'///copy all
Selection.Copy
End If
Next wks



'///
'Paste sheet one to new timesheet
LNewWb.Activate

For Each wks In LNewWb.Worksheets
CodeNameString = wks.CodeName
If CodeNameString = "Sheet1" Then
LNewWb.Sheets(CodeNameString).Visible = True
LNewWb.Sheets(CodeNameString).Select

Cells.Select
'///paste all (values)
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'///replace string with formula

Selection.Replace What:="$$$$$=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

End If
Next wks

'\\\\
ActiveWorkbook.SaveAs Filename:=UserForm1.TextBox2.Value

'\\\\
ActiveWorkbook.Close

'\\\\
LOldWb.Activate

ActiveWorkbook.Close

End Sub

pasting problems
 

Thanks,
I've altered the cells.select to cells(1,1).select, and, while I can
see that the desired selection and replacement occurs, the copy
doesn't occur, for some reason:

Sub timesheetGenerate()
Dim fillamt As Long
Dim origDate As Date

Dim LOldWb As Workbook
Dim LNewWb As Workbook
Dim x As Name

Dim wks As Worksheet
Dim CodeNameString As String




Const xlPasteAll = -4104
Const xlNone = -4142
Const xlByRows = &H1
'var xlPart = 0x2


'Open existing timesheet
Set LOldWb = Workbooks.Open(Filename:=UserForm1.TextBox1.Value)

LOldWb.Activate


'Add new workbook
Set LNewWb = Workbooks.Add



'///
'Copy sheet one of existing timesheet
LOldWb.Activate

For Each wks In LOldWb.Worksheets
CodeNameString = LCase(wks.CodeName)
If CodeNameString = "sheet1" Then
LOldWb.Sheets(CodeNameString).Visible = True
LOldWb.Sheets(CodeNameString).Select

Cells(1, 1).Select
'///replace formula with string
Selection.Replace What:="=", Replacement:="$$$$$=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Copy

End If
Next wks


'///
'Paste sheet one to new timesheet
LNewWb.Activate

For Each wks In LNewWb.Worksheets
CodeNameString = wks.CodeName
If CodeNameString = "Sheet1" Then
LNewWb.Sheets(CodeNameString).Visible = True
LNewWb.Sheets(CodeNameString).Select

Cells(1, 1).Select

'///paste all (values)
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'///replace string with formula
Selection.Replace What:="$$$$$=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

End If
Next wks


'\\\\Sheets("Sheet1").Select
For Each wks In LNewWb.Worksheets
CodeNameString = wks.CodeName
If CodeNameString = "sheet1" Then
LNewWb.Sheets(CodeNameString).Visible = True
LNewWb.Sheets(CodeNameString).Select

' Loop through all of the defined names in the active
' workbook.
For Each x In LOldWb.Names
' Add each defined name from the active workbook to
' the target workbook ("Book2.xls" or "Book2.xlsm").
' "x.value" refers to the cell references the
' defined name points to.
LNewWb.Names.Add Name:=x.Name, _
RefersTo:=x.Value
Next x

End If
Next wks
'\\\\
ActiveWorkbook.SaveAs Filename:=UserForm1.TextBox2.Value
'\\\\
ActiveWorkbook.Close
'\\\\
LOldWb.Activate
ActiveWorkbook.Close

End Sub