PDA

View Full Version : Solved: Macro to transfer values



blarkdirth
02-09-2005, 08:18 AM
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

mvidas
02-09-2005, 01:52 PM
Hi Mark,

You can do this using:
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
Matt

blarkdirth
02-09-2005, 04:11 PM
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 ..
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

Does what I need

Thanks again for your help

Mark
:beerchug: