Macro/VB Help Needed
 

Hello,

I need help with a macro I'm trying to use. I'm not even sure I can do what
I want without VB (which I am nearly clueless at!) Anyone out there that can
help? Here's my situation:
I have a range (Glass Size!V33:AN33) that has formulae to calculate data.
I am trying to record a macro to take the calculated information in this
range and send it to a summary worksheet (Hardware!A3:S3) via a button
assigned to the macro, which is in the same workbook. After the data has
been updated in the summary W/S (Hardware!), I'd like the range on the Glass
Size! W/S to be reset (but keeping the formats/formulae) and ready to
calculate new figures, in turn ready to be sent to the next available row
Hardware!A4:S4) etc.
How can I do this?
TIA!

Macro/VB Help Needed
 

Hi- In which row are the formulae in the Glass size worksheet? I am assuming
33, but am not sure. I just want to make sure they are all in the same row
and not here and there within the range. Thanks.
FB

"olrustyxlsuser" wrote:

Hello,

I need help with a macro I'm trying to use. I'm not even sure I can do what
I want without VB (which I am nearly clueless at!) Anyone out there that can
help? Here's my situation:
I have a range (Glass Size!V33:AN33) that has formulae to calculate data.
I am trying to record a macro to take the calculated information in this
range and send it to a summary worksheet (Hardware!A3:S3) via a button
assigned to the macro, which is in the same workbook. After the data has
been updated in the summary W/S (Hardware!), I'd like the range on the Glass
Size! W/S to be reset (but keeping the formats/formulae) and ready to
calculate new figures, in turn ready to be sent to the next available row
Hardware!A4:S4) etc.
How can I do this?
TIA!

re
 

Filo,
Hello and thank you for responding so quickly. The relavent formulae are
all in row 33. range - V33:AN33

Macro/VB Help Needed
 

One way to approach it below. If the Hardware worksheet is empty, the first
line will start in cell A3 (I assume you have labels so the worksheet should
never be empty, but........). Also, note that if the formulae on the Glass
Size worksheet can evaluate to an empty string (ie ""), this could cause a
problem w/ a solution using something like Range(Rows.Count,
1).End(xlup).Offset(1,0) to get the next available cell (which is another
means to find the last row and I almost used myself, but thought better of
it).



Sub MyCopy()
Dim rngSource As Range
Dim rngDest As Range
Dim rngLastCell As Range

Set rngSource = Worksheets("Glass Size").Range("V33:AN33")


With Worksheets("Hardware")
Set rngLastCell = .Cells.Find( _
what:="*", _
after:=.Range("A1"), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False, _
matchbyte:=False)

If Not rngLastCell Is Nothing Then
If rngLastCell.Row = .Rows.Count Then
MsgBox ("No space available in destination worksheet.")
Exit Sub
Else
Set rngDest = .Cells(rngLastCell.Row, 1) _
(2, 1).Resize(rngSource.Rows.Count, _
rngSource.Columns.Count)
End If
Else: Set rngDest = .Range("A3").Resize(rngSource.Rows.Count, _
rngSource.Columns.Count)
End If
End With

rngDest.Value = rngSource.Value

End Sub




"olrustyxlsuser" wrote:

Hello,

I need help with a macro I'm trying to use. I'm not even sure I can do what
I want without VB (which I am nearly clueless at!) Anyone out there that can
help? Here's my situation:
I have a range (Glass Size!V33:AN33) that has formulae to calculate data.
I am trying to record a macro to take the calculated information in this
range and send it to a summary worksheet (Hardware!A3:S3) via a button
assigned to the macro, which is in the same workbook. After the data has
been updated in the summary W/S (Hardware!), I'd like the range on the Glass
Size! W/S to be reset (but keeping the formats/formulae) and ready to
calculate new figures, in turn ready to be sent to the next available row
Hardware!A4:S4) etc.
How can I do this?
TIA!

Macro/VB Help Needed
 

JMB - Thank you. This works very well. I've tested it out a few times and
haven't found any glitches with my worksheet.
If you or anyone would be willing to help, I've had a thread posted for a
while now with no response. It can be found as "copying numerous formats
from a VLOOKUP" in the Excel Programming section.
I wish I knew VB better! I'll keep learning, but until then I can't
express how helpful these discussion groups are!
Thank all of you!

Macro/VB Help Needed
 

I posted something that will hopefully get you started in that thread.

"JMB" wrote:

One way to approach it below. If the Hardware worksheet is empty, the first
line will start in cell A3 (I assume you have labels so the worksheet should
never be empty, but........). Also, note that if the formulae on the Glass
Size worksheet can evaluate to an empty string (ie ""), this could cause a
problem w/ a solution using something like Range(Rows.Count,
1).End(xlup).Offset(1,0) to get the next available cell (which is another
means to find the last row and I almost used myself, but thought better of
it).



Sub MyCopy()
Dim rngSource As Range
Dim rngDest As Range
Dim rngLastCell As Range

Set rngSource = Worksheets("Glass Size").Range("V33:AN33")


With Worksheets("Hardware")
Set rngLastCell = .Cells.Find( _
what:="*", _
after:=.Range("A1"), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False, _
matchbyte:=False)

If Not rngLastCell Is Nothing Then
If rngLastCell.Row = .Rows.Count Then
MsgBox ("No space available in destination worksheet.")
Exit Sub
Else
Set rngDest = .Cells(rngLastCell.Row, 1) _
(2, 1).Resize(rngSource.Rows.Count, _
rngSource.Columns.Count)
End If
Else: Set rngDest = .Range("A3").Resize(rngSource.Rows.Count, _
rngSource.Columns.Count)
End If
End With

rngDest.Value = rngSource.Value

End Sub




"olrustyxlsuser" wrote:

Hello,

I need help with a macro I'm trying to use. I'm not even sure I can do what
I want without VB (which I am nearly clueless at!) Anyone out there that can
help? Here's my situation:
I have a range (Glass Size!V33:AN33) that has formulae to calculate data.
I am trying to record a macro to take the calculated information in this
range and send it to a summary worksheet (Hardware!A3:S3) via a button
assigned to the macro, which is in the same workbook. After the data has
been updated in the summary W/S (Hardware!), I'd like the range on the Glass
Size! W/S to be reset (but keeping the formats/formulae) and ready to
calculate new figures, in turn ready to be sent to the next available row
Hardware!A4:S4) etc.
How can I do this?
TIA!