Below is the code I am using. I have several workbooks open. One of the
workbooks, "po-info.xls", is used to compile information copied from the
others. After the information is copied, that workbook is closed. The same
procedure is continued with the next workbook until information from all
workbooks is copied to "po-info.xls".

I need to save the "values" and the "formats" of the copied information in
the "po-info.xls" file. I have tried several arrangements of the
"xlpasteformats" without success.

Help!

Sub info()
Application.DisplayAlerts = False
Dim a
a = InputBox("Enter date = mm/dd/yy")
Dim lop As Long
Dim Number As Long
Number = Workbooks.Count
For lop = 1 To Number
If Not ActiveWorkbook.Name = "po-info.xls" Then
Worksheets("po").Range("s9").Value = a
ActiveWorkbook.Worksheets("info").Range("a1:g23"). Copy
Workbooks("po-info.xls").Worksheets("po-info"). _
Range("a1").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlValues
Application.CutCopyMode = False
ActiveWorkbook.Close savechanges:=False
End If
ActiveWindow.ActivateNext
Next lop
ActiveCell.Range("a1").Select
Range("a1:a2").EntireRow.Delete
Workbooks("po-info.xls").SaveAs Filename:="po_upload.prn", _
FileFormat:=xlTextPrinter
ActiveWindow.Close savechanges:=True
Application.DisplayAlerts = True
End Sub

--
l-hawk

I have been successful by replacing the following:

Workbooks("po-info.xls").Worksheets("po-info"). _
Range("a1").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlValues

With:

With Workbooks("po-info.xls").Worksheets("po-info"). _
Range("a1").End(xlDown).Offset(1, 0)
.PasteSpecial xlValues
.PasteSpecial xlFormats
End With


--
l-hawk


"hawki" wrote:

Below is the code I am using. I have several workbooks open. One of the
workbooks, "po-info.xls", is used to compile information copied from the
others. After the information is copied, that workbook is closed. The same
procedure is continued with the next workbook until information from all
workbooks is copied to "po-info.xls".

I need to save the "values" and the "formats" of the copied information in
the "po-info.xls" file. I have tried several arrangements of the
"xlpasteformats" without success.

Help!

Sub info()
Application.DisplayAlerts = False
Dim a
a = InputBox("Enter date = mm/dd/yy")
Dim lop As Long
Dim Number As Long
Number = Workbooks.Count
For lop = 1 To Number
If Not ActiveWorkbook.Name = "po-info.xls" Then
Worksheets("po").Range("s9").Value = a
ActiveWorkbook.Worksheets("info").Range("a1:g23"). Copy
Workbooks("po-info.xls").Worksheets("po-info"). _
Range("a1").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlValues
Application.CutCopyMode = False
ActiveWorkbook.Close savechanges:=False
End If
ActiveWindow.ActivateNext
Next lop
ActiveCell.Range("a1").Select
Range("a1:a2").EntireRow.Delete
Workbooks("po-info.xls").SaveAs Filename:="po_upload.prn", _
FileFormat:=xlTextPrinter
ActiveWindow.Close savechanges:=True
Application.DisplayAlerts = True
End Sub

--
l-hawk