PC Review


Reply
Thread Tools Rate Thread

Copy rows from other Excel-file

 
 
maywood
Guest
Posts: n/a
 
      5th Nov 2009
Hi,

In my workbook (NewWkb) I am using a Command Button to import some complete
sheets from another Excel file (ImportWkB) (see code below).
Now I want to use the same button to import additionaly some special rows
from some sheets from a second excel-file (ImportWkB2). Is that possible?

For example I want to copy row 2 & 5 from sheet 2 and row 3 & 6 from sheet 3
in my second excel source-file to my workbook Sheet 11 in cell B10 and below
Does someone has an idea hot wo extend the code?

-----Code-------:
Private Sub CommandButton1_Click()
Dim fname As Variant
Dim NewWkb As Workbook ' destination workbook
Dim ImportWkB As Workbook ' source workbook No1
Dim ImportWkB2 As Workbook ' the second excel source-file
Dim sh As Variant 'sheets
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("Excel-Data-Input,*.xls", ,
"Source")
If fname <> False Then
Application.ScreenUpdating = False
Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True, UpdateLinks:=0)

For Each sh In ImportWkB.Sheets
NewWkb.Sheets(sh.Index + 4).Cells.UnMerge
Set DestRange = NewWkb.Sheets(sh.Index + 4).Range("B10")
sh.UsedRange.Copy

With DestRange
.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False
End With
Set DestRange = Nothing

Application.CutCopyMode = False

If sh.Index >= 6 Then Exit For
Next sh

ImportWkB.Close False
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VBA - How to copy rows found & to cater if no rows found via autofilter kazzy Microsoft Excel Discussion 1 17th Feb 2011 02:10 AM
copy rows to another file climate Microsoft Excel Worksheet Functions 0 19th May 2010 09:04 AM
copy rows to another file climate Microsoft Excel Worksheet Functions 0 19th May 2010 09:00 AM
200 COL Excel file with 700 rows -how to switch COLS & ROWS in ACC JacquelineAB Microsoft Access External Data 2 17th Dec 2008 09:55 PM
How do i copy a scramble file with 20970 rows to excel? =?Utf-8?B?a2lt?= Microsoft Excel New Users 4 9th Sep 2006 02:31 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:47 PM.