• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Active cell range

Hi,
I have been searching for about an hour now and I'm losing the will to live.
I know this is simple but I cannot get it to work. Nor find a solution here that suits.
I need to copy the values starting at the ActiveCell down 21 Cells in workbook1.
That is to say if the active cell in workbook1 is b20, I need to copy the values of b1 to b41 inclusive.
I then need to paste those values to workbook2,.one cell below the range "StartCell"
I have declared the values of Wb1 and Wb2 but cannot get the copy/paste to do what I need.

Thanks as always for your time.
0
Stephen Byrom
Asked:
Stephen Byrom
  • 7
  • 5
  • 4
  • +1
3 Solutions
 
etech0Commented:
try the following:

wb1.sheets(1)range(activecell, activecell.offset(21,0)).copy
wb2.GoTo Reference:=startcell
activecell.offset(1,0).select
activesheet.paste
0
 
kgerbChief EngineerCommented:
This will work as well...
Sub Copy21Cells()
ActiveCell.Resize(21).Copy Workbooks("Book2").Sheets("Sheet1").Range("StartCell").Offset(1)
End Sub

Open in new window

Kyle
0
 
etech0Commented:
kgerb's code looks faster. If you use it, replace "Book2" with Wb1 - your first workbook.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kgerbChief EngineerCommented:
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

Open in new window

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

Kyle
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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
0
 
kgerbChief EngineerCommented:
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

Open 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
0
 
etech0Commented:
You may also need to change the sheet names from "sheet1" to whatever your sheets are called in their respective workbooks.
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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.
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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.
0
 
kgerbChief EngineerCommented:
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

Open in new window

Kyle
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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.
0
 
kgerbChief EngineerCommented:
:-)  No problem.  We'll get there.  I think this will do it.
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)
End Sub

Open in new window

This code will open NewData.xlsx from your specified path.  It will then copy all the data from the active cell and 21 rows down.  It will then paste the data into the workbook containing the subroutine just below the the cell "StartCell".  Let me know if it works:).

I have a question though.  Before you save and close wb2 each time, are you careful to select the correct cell?  When you open a workbook the active cell is whatever it was the last time it was saved and closed.

Kyle
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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?
0
 
etech0Commented:
Wherever you paste, use this:


    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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.
0
 
Rory ArchibaldCommented:
NFP:


Or just use:

wb1.Sheets("Sheet1").Range("StartCell").Offset(1).Resize(21).value = ActiveCell.Resize(21).Value

Open in new window

0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now