Active cell range

This will work as well...

Sub Copy21Cells()
ActiveCell.Resize(21).Copy Workbooks("Book2").Sheets("Sheet1").Range("StartCell").Offset(1)
End Sub

Select allOpen in new window

Kyle

kgerb's code looks faster. If you use it, replace "Book2" with Wb1 - your first workbook.

oops, missed that part:-)

Here's the adjusted code.  

Sub Copy21Cells()
wb1.Sheets("Sheet1").ActiveCell.Resize(21).Copy wb2.Sheets("Sheet1").Range("StartCell").Offset(1)
End Sub

Select allOpen in new window

You'll have to modify the sheet names as necessary.

Kyle

Thank you both for your time.
I have tried both examples and I cannot get either to work.
Because of the simplicity I ended up with Kgerb's but it breaks at line 4
Option Explicit

Sub ImportData()
Dim wb1 As Workbook
Dim wb2 As Workbook
    Set wb1 = ThisWorkbook
    Set wb2 = Application.Workbooks.Open("C:\Users\StevieB\Desktop\NewData.xlsx")
STOPS HERE wb2.Sheets("Sheet1").ActiveCell.Resize(21).Copy
wb1.Sheets("Sheet1").Range("StartCell").Offset (1)
End Sub

Try this...

Sub ImportData()
Dim wb1 As Workbook
Dim wb2 As Workbook
    Set wb1 = ThisWorkbook
    Set wb2 = Application.Workbooks.Open("C:\Users\StevieB\Desktop\NewData.xlsx")
wb2.Sheets("Sheet1").ActiveCell.Resize(21).Copy _
wb1.Sheets("Sheet1").Range("StartCell").Offset(1)
End Sub

Select allOpen in new window

Just to make sure.  The way you currently have the code you are copying from wb2 into wb1.  Is that what you want?

Kyle

You may also need to change the sheet names from "sheet1" to whatever your sheets are called in their respective workbooks.

Thanks Kyle, but the code you posted is the same as I posted without the indication of where it stops. I renamed the sheets to be "Sheet1" in both workbooks to comply with your code.

I am copying from wb2 to wb1, thanks
Now it stops at the same place, it's just that the two lines are highlighted now as the error because of the space and underscore.

Well, actually, no it's not the same.  I added a line feed character.  The code was breaking b/c you hadn't finished the copy statement.  You need a destination range in the same statement

In fact there was more wrong than just that.  I should have caught this the first time but...ActiveCell is not an object of either the either the Workbooks collection or the Sheets collection.  You don't need to specify the name of the active workbook as ThisWorkbook or the name of the active worksheet.  By definition the active cell is in the active workbook and on and active worksheet.

This code should work for you.  It will copy from the active workbook into the new workbook (wb2).

Sub ImportData()
Dim wb2 As Workbook
Set wb2 = Workbooks("Book2")
ActiveCell.Resize(21).Copy _
    wb2.Sheets("Sheet1").Range("StartCell").Offset(1)
End Sub

Select allOpen in new window

Kyle

Thanks for your patience,
I don't think i am explaining myself clearly.
I have wb1 open and want to run the macro from there.
wb1 = ThisWorkbook
I want to get the values only from (activecell + 21 cells down inclusive) in wb2
Set wb2 = Application.Workbooks.Open("C:\Users\StevieB\Desktop\NewData.xlsx")
I then want to paste those values not formulas starting at the cell immediately below "StartCell" in wb1.

Again, I am sorry if I did not explain myself clearly earlier.
Thanks for your time.

Thanx for that Kyle.
It now copies the range as we were hoping for, It copies the formulas though and not the values.
Is there a pastespecial routine I need to add so as to just have the values copied?

Wherever you paste, use this:


    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Thanks etech, but I think line 6 of Kyle's code is copy and paste in one action, So I am unsure of where to place the xlPasteValues statement.

I have tried a couple of places to no avail.

Thanks all for your input, especially Kyle.
I managed to get it to work with the foillowing code;
Option Explicit

Sub ImportData()
Dim wb1 As Workbook
Dim wb2 As Workbook
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("C:\Users\StevieB\Desktop\NewData.xlsx")
ActiveCell.Resize(21).Copy
wb1.Sheets("Sheet1").Range("StartCell").Offset(1).Resize(21).Value = ActiveCell.Resize(21).Value
End Sub

It may seem simple to Gurus, Masters and Savants, but it's a struggle for us mere mortals. :)
Thanks again for your time