Consulting

Results 1 to 3 of 3

Thread: Solved: Macro to transfer values

  1. #1
    VBAX Regular
    Joined
    Aug 2004
    Location
    Biggleswade, England
    Posts
    10
    Location

    Solved: Macro to transfer values

    Hi All

    I would be grateful for some help on the following challenge

    On Sheet 1 in various cells eg A3, B3, C3 a user will input values

    At the press on a Button on the sheet I would like the values to be transferred into the next completely blank row on sheet 2, in columns E, F, G.

    The row to transfer into has columns up to AY

    If any of the cells in a row up to AY has a value then the row should be viewed as not blank

    Appreciate any help

    Mark

  2. #2
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hi Mark,

    You can do this using:
    [vba]Sub CopyToNextBlankRow()
    Dim WS1 As Worksheet, WS2 As Worksheet, rw As Long
    Set WS1 = Sheets("Sheet1")
    Set WS2 = Sheets("Sheet2")
    rw = WS2.Cells.Find("*", WS2.Range("A1"), LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    WS2.Range("E" & rw & ":G" & rw) = WS1.Range("A3:C3").Value
    End Sub[/vba]
    Matt

  3. #3
    VBAX Regular
    Joined
    Aug 2004
    Location
    Biggleswade, England
    Posts
    10
    Location
    Matt

    Thanks for the code which works exactly as it should

    However I didn't mention (!) that I have formulas on sheet 2 which are on the same rows that I need the values from sheet 1 to go into

    So my code is as follows ie checking in column G ..
    [VBA] Sub CopyToNextBlankRow()
    Dim WS1 As Worksheet, WS2 As Worksheet, rw As Long
    Set WS1 = Sheets("Single Item Check")
    Set WS2 = Sheets("Ebay Fee _ Sales Calculator")
    rw = WS2.Cells.Find("*", WS2.Range("g65536").End(xlUp), LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    WS2.Range("E" & rw & ":G" & rw) = WS1.Range("A3:c3").Value [/VBA]

    Does what I need

    Thanks again for your help

    Mark

Posting Permissions

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