Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

using vba code to copy & paste excel ranges..

Status
Not open for further replies.

kingz2000

Programmer
May 28, 2002
304
DE
Hi,


when I copy a range of rows from 1 excelfile to another, I notice that it pastes the rows all the same size. Although the original rows are varying in sizes. How can I insist on pasting the exact same row heights aswell??

The reason this is important is for the printing..The rows have been sized to fit perfectly on a page.

If it is not possible to copy the exact row sizes, then I guess I have to paste, AND THEN redefine the row heights. Here too I am not sure of the command - Please!??

Thanks in advance.
 
Hi,

please tell us all exactly how you are copying the range? Please cover every stage of the process? This works fine for me ( 2 Excel workbooks open in the same Excel instance ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Hi,

here are the main chunks from my procedure

Dim rs As New ADODB.Recordset
Dim SQL As String
Dim SQL2 As String

Dim oExc As New Excel.Application

Dim wkb1 As Workbook
Dim wkb2 As Workbook
Dim wks1 As Worksheet
Dim wks2 As Worksheet

With oExc

Set wkb1 = .Workbooks.Open(sourcePath)
Set wks1 = wkb1.Worksheets(sourceSheet)

Set wkb2 = .Workbooks.Open(destPath)
Set wks2 = wkb2.Worksheets(destSheet)

While Not rs.EOF
wks1.range(rs!CopyRange).Copy

strPasteRange = PasteRange(strPasteRange, rs! Zeilenabstand)

wks2.Paste wks2.range(strPasteRange)

oExc.Quit

Set wks1 = Nothing
Set wks2 = Nothing
Set wkb1 = Nothing
Set wkb2 = Nothing
Set oExc = Nothing

Set rs = Nothing

CopyExcelRange = False

Exit Function
End If

rs.MoveNext
Wend

rs.Close


wkb1.Close SaveChanges:=False
wkb2.Close SaveChanges:=True
.Quit

End With

Set wks1 = Nothing
Set wks2 = Nothing
Set wkb1 = Nothing
Set wkb2 = Nothing
Set oExc = Nothing

Set rs = Nothing


CopyExcelRange = True


 
Kingz2000,
I don't know if this solves your problem entirely but it may help. When in Excel 2000 and I assume 2003 as well you can go to Window to split the screen. Thus you can have two workbooks open and copy data (or formats) between the two. I did that and got the following code with the macro recorder:
Sub Testpaste()
'
' Testpaste Macro
' Macro recorded 26/04/2006 by David Prince
'

'
Columns("A:A").ColumnWidth = 10
Range("A1").Select
Selection.Copy
Windows("Book2").Activate
Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

The only problem is that in the "paste special" commands there is no "row height" function.
Hope this is of some help.
Regards
David
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top