pasting problems

[ll]

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

 

[FSt1]

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

 

[Bob Flanagan]

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]

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