Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: VBA Copy/Paste from Active Cell sheet 1 to next blank cell sheet 2

  1. #1
    Join Date
    24th May 2011
    Posts
    2

    VBA Copy/Paste from Active Cell sheet 1 to next blank cell sheet 2

    Hello, i need a little help with coding a macro for my workbook, am a bit of a novice at this and am still learning all the Functions of VBA.

    I need the VBA code to be able to copy specific data from cells in the selected active row in the one sheet to a specific range of cells in another worksheet without pasting over existing data.

    For example:

    Sheet 1: I select Rows "1, 2 & 3" As Active rows, i want the data from cell "F" of each of these rows to be copied and pasted to Sheet 2 into the Range of cells A42 to A59.
    Now what want to happen is for the data in (sheet 1) Row 1, F to be pasted into (sheet 2) cell A42, then the data from (sheet 1) rows 2, cell F and 3 cell F to be pasted in the next blank cell below.

    Basically what i have is a Goods In booking system of which i want to create a Delivery note for Goods Out, i want to take the data from each active row i select and to put it into the Range of cells shown above, so that each item is listed one below each other descending in column A starting in Cell 42. I have tried to code it myself, and can only get the data to be pasted into individual separate cells rather than pasting the data in the next blank cell below in colum A.

    Here's my try at it:

    VB:
    Sub IMPORT() 
        Sheets("Sheet1").Activate 
        Rows(ActiveCell.Row).Select 
        Cells(ActiveCell.Row, "F").Select 
        Sheets("Sheet2").Activate 
        Range("A42").Select 
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _ 
        xlWhole, xlByRows, xlPrevious).Row 
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ 
        False, Transpose:=False 
    End Sub 
    
    
    The code from above ive got from reading these forums and adapting to my workbook, but obviously doesnt work for me.

    Any help appreciated, thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    10th August 2005
    Posts
    844

    Re: VBA Copy/Paste from Active Cell sheet 1 to next blank cell sheet 2

    Sounds like the problem you are having is finding the last row?
    VB:
    LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row 
    
    
    .

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Copy and paste cell from one sheet to another
    By IBSUB in forum EXCEL HELP
    Replies: 1
    Last Post: April 2nd, 2011, 06:29
  2. Replies: 2
    Last Post: April 1st, 2008, 13:40
  3. Copy/Paste From Active Sheet To Another Sheet
    By bonnet in forum EXCEL HELP
    Replies: 2
    Last Post: September 11th, 2006, 18:53
  4. Replies: 6
    Last Post: January 14th, 2006, 16:04
  5. Replies: 3
    Last Post: January 10th, 2004, 04:02

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno