Excel Macro -- Output onto new row, new sheet

[megpow]

Hey Guys --

I'm new to VB so I need help with a really simple task -- Its so silly I'm embarassed to ask!

I have an input sheet where operators enter daily data. I want the output data to be put on a different sheet. The output sheet takes values (and not formats) from specific cells (absolute reference) in the input sheet and places them all in the same row (different cols) on the output sheet whenever the macro is run. When the macro is run again, I want the next set of input data to be placed in the next available row.. simple, right?

I know there must be an easy way to tell excel to choose the next row and have all data be entered into that row, but the extent of my macro-writing knowledge doesn't go much further than "record macro"

Sorry for the silly question, a girl's gotta learn somehow!! I'm also sure that this question has been answered here before, so maybe someone can just point me in the right direction?

 

[Zathras]

You're right, it has. See faq707-2112

Once you have the last row, you can use it in conjunction with the .Cells property of the worksheet object (see the help file).

BTW, you have no reason to be embarrassed. Everybody is a beginner at the beginning.

 

[SkipVought]

Hi,

Post your macro code.

There are many ways to define the NEXT available row. If you have contiguous data...

dim lNextRow as Long
With OutputSheet
   with .Cells(1,1).CurrentRegion
      lNextRow = .Row = .Rows.Count 
   end with
   .Cells(lNextRow, 1).value = MyColAValue
End with

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[megpow]

This is an excerpt of what I had --

'copy Mn data
Sheets("Concentration Calculator&quot.Select
Range("C9:C12&quot.Select
Selection.Copy

'find next blank cell in range on output sheet
Sheets("Operating scenarios&quot.Select
Range("A3:X476&quot.Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
'paste Mn data
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True



I want it to only look for blanks within the range A3:X500, but of course it finds a blank at Y1, outside of the range and starts pasting the data there..

 

[megpow]

So my Range("A3:X500&quot is in the wrong place .. its selecting that range, and then looking outside of it for blanks..

oh yeh, and I didn't mean to have it say X476 in my code and X500 in my explanation. The rows don't matter, they can go to infinity.. I just don't want it to consider any columns to the right of X ..

 

[PHV]

Your Find method is executed in the entire Cells collection, not the previous range selection.

Hope This Help
PH.

 

[megpow]

PH --
so how do I tell it to only search my range selection? I think I'm just having a problem with the syntax.

I also tried inserting Skips code instead, changing
.Cells(lNextRow, 1).value = MyColAValue to
.Cells(lNextRow, 1).select and then following with my paste.. I get an error on that line. Am I misunderstanding the code?

 

[PHV]

Simply like this:
Range("A3:X476&quot.Find(What:=

Hope This Help
PH.

 

[megpow]

That did it, PH! See, i knew it was something simple Thanks for your help!

--Meg

 

[tav1035]

Megpow,
What was your final code?
tav