Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.office.developer.vba,microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default [Excel XP] Orientation of cells etc.

I have to copy the contents of some cells from an external spreadsheet
to another (in the current Workbook). The range is e.g. all data in the
cells of the row 2-4 in all of the columns.

What is the best way to do this?

My current code is something like this

Application.ScreenUpdating = False
' path set to some workbook
Set wb = Workbooks.Open(sPath)
Dim lng As Long
Dim i As Integer
With ThisWorkbook.Worksheets("Sheet1")
' sSheet points to a specific Spreadsheet in the external Workbook
For lng = 1 To wb.Worksheets(sSheet).Columns.Count
For i = 2 To 4
.Cells(10 + i, lng).Value = wb.Worksheets(sSheet).Cells(i, lng).Value
' copy some of the formatting as well.
.Cells(10 + i, lng).Interior.ColorIndex = wb.Worksheets(sSheet).Cells(i, lng).Interior.ColorIndex
.Cells(10 + i, lng).Font.Size = wb.Worksheets(sSheet).Cells(i, lng).Font.Size
Next
Next
End With

This copies all data in row 2-4 in all columns in the external
spreadsheet.

Howver, I have a feeling that there is a better way to do it. My current
problem is that I need all the formatting as well, that is
text-orientation as well as row height and column width.

Also, the source spreadsheet in the external Workbook has data in
something like the first 250 rows. How do I copy these rows only to
my target spreadsheet? If I try

wb.WorkSheets(sSheet).Rows.Count

, I get 65536 ... which is a bit more than I need :)

I hope you guys can help me with this one ...

--
Jesper Stocholm - http://stocholm.dk
The Web submission form is the preferred procedure. However, if you don't
have access to the Internet you may send your submission by e-mail.
(Nyhedsbrev fra 9th Conference on Reliable Software Technologies)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pagesetup.Orientation need VBA routine to "Best Guess" Orientation [email protected] Excel Discussion (Misc queries) 3 November 10th 09 06:53 PM
Excel 2007 Landscape Orientation ExcelHUH?!?!? Excel Discussion (Misc queries) 6 August 30th 09 05:07 PM
Excel Page orientation Sam Excel Worksheet Functions 1 August 1st 08 09:14 PM
Excel Format cells Alignment Orientation 180 degrees? MPJ, the floor-plan-man Excel Discussion (Misc queries) 0 June 2nd 06 12:01 PM
Where does Excel store the orientation of PivotFields? Valentin Charts and Charting in Excel 0 April 3rd 06 04:02 PM


All times are GMT +1. The time now is 11:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"