PDA

View Full Version : Sleeper: Help with macro please



MonkeyMe
06-13-2005, 06:29 AM
hi, i need some help writing a macro that cuts and pastes some rows into a new sheet and saves it accordingly to a cell name.

e.g.

Repeating
Vendor ID
Number 1
total: $15

Repeating
Vendor ID
Number 2
total $20



so "Repeating" is the marker and i need it to take that certain chunk and save it as the "Vendor ID" name so the first one would be named "Number 1.xls"

thanks for the help!

MWE
06-13-2005, 09:41 AM
hi, i need some help writing a macro that cuts and pastes some rows into a new sheet and saves it accordingly to a cell name.

e.g.

Repeating
Vendor ID
Number 1
total: $15

Repeating
Vendor ID
Number 2
total $20



so "Repeating" is the marker and i need it to take that certain chunk and save it as the "Vendor ID" name so the first one would be named "Number 1.xls"

thanks for the help!
If I understand your need correctly, you wish to copy a block of info from the active sheet into a new workbook and saves the new workbook with a particular name extracted from the original info. Note in your original post you stated " ... into a new sheet and saves it ..." Questions:
</p>


how will the macro know where to find the original info? Current selection? look for &quot;Repeating&quot; on the active sheet? something else?
if not current selection, how many rows are involved? If not a consistent number, how will the macro know how many rows to copy and paste?
how does the macro know that &quot;Repeating&quot; is the marker? If the market is a &quot;pointer&quot; to the start of the target block, is setting the pointer text to be part of the macro?
why is &quot;Number 1&quot; the name for the new workbook?
what should be the name of the worksheet into which the info is posted?

Can you post a sample (and possibily sanitized) workbook?

MonkeyMe
06-13-2005, 09:48 AM
hope this helps, i have this so far:


Sub test()
Dim oThis As Worksheet
Dim oWB As Workbook
Dim iLastRow As Long
Dim iStart As Long
Dim i As Long
Set oThis = ActiveSheet
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value = "Repeating" Then
If iStart > 0 Then
Set oWB = Workbooks.Add
oThis.Range("A" & iStart & ":A" & i - 1).Copy
oWB.Worksheets(1).Range("A1")
oWB.SaveAs oThis.Cells(iStart + 2, "A") & ".xls"
End If
iStart = i
End If
Next i
End Sub


but i keep getting the error:
Run-time error '438'
Object doesn't support this property or method

any ideas?

mdmackillop
06-13-2005, 09:58 AM
There is some confusion in your question betwewn new Sheet and new Book
For a new book, select the appropriate Reeating cell, and run the following. It will save the new workbook into C:\Myfiles


Sub CutPasteName()
ActiveCell.Rows("1:4").EntireRow.Cut
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Move
ActiveSheet.Name = "Sheet1"
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\MyFiles\" & Cells(3, 1) & ".xls"
End Sub

mdmackillop
06-13-2005, 10:00 AM
Hi MonkeyMe,
If you select your code and click on the VBA button, it formats the code, making it more readable. (as edited)

MonkeyMe
06-13-2005, 10:02 AM
yeah, sorry guys new workbook

so should i insert this new code into the code above somewherE?

mdmackillop
06-13-2005, 10:11 AM
I posted my code before I saw yours, so mine is intended as standalone. Which way you go depends upon your data. if it is always the same number of rows, a variation of my code should suffice.
I think the problem with your code is that iStart is not set until after Repeating is located, and on the next loop, the focus has left the Repeating cell.

Bob Phillips
06-13-2005, 10:33 AM
but i keep getting the error:
Run-time error '438'
Object doesn't support this property or method





Sub test()
Dim oThis As Worksheet
Dim oWB As Workbook
Dim iLastRow As Long
Dim iStart As Long
Dim i As Long
Set oThis = ActiveSheet
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value = "Repeating" Then
If iStart > 0 Then
Set oWB = Workbooks.Add
oThis.Range("A" & iStart & ":A" & i - 1).Copy _
oWB.Worksheets(1).Range("A1")
oWB.SaveAs oThis.Cells(iStart + 2, "A") & ".xls"
End If
iStart = i
End If
Next i
End Sub

MonkeyMe
06-13-2005, 10:42 AM
but i keep getting the error:
Run-time error '438'
Object doesn't support this property or method





Sub test()
Dim oThis As Worksheet
Dim oWB As Workbook
Dim iLastRow As Long
Dim iStart As Long
Dim i As Long
Set oThis = ActiveSheet
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value = "Repeating" Then
If iStart > 0 Then
Set oWB = Workbooks.Add
oThis.Range("A" & iStart & ":A" & i - 1).Copy _
oWB.Worksheets(1).Range("A1")
oWB.SaveAs oThis.Cells(iStart + 2, "A") & ".xls"
End If
iStart = i
End If
Next i
End Sub



??:dunno

vonpookie
06-13-2005, 01:35 PM
Sorry, guys. Looks like the OP cross-posted. :confused2:

I answered this here:
http://www.mrexcel.com/board2/viewtopic.php?t=151312

Bob Phillips
06-13-2005, 06:51 PM
??:dunno

I added a _ in a line that had spread over two lines, so as to continue it.

Zack Barresse
06-14-2005, 09:52 AM
MonkeyMe, if you feel the need to cross-post to other forums, please take the time to post a link to the other posts in their respective forums. This is a courtesy to people who take their time to answer these questions for free.

It does make it seem 'fruitless' if you're answering a question whilst somebody else is also answering the same question. A lot of people work multiple forums, and duplication of efforts is not anybody's strong point. Don't get me wrong, it's not bad. We just want to know about it. :)

Thanks for your understanding. :thumb

MonkeyMe
06-15-2005, 01:15 PM
ooops, sorry about that guys/girls!
i'll remember next time!

Zack Barresse
06-15-2005, 04:31 PM
ooops, sorry about that guys/girls!
i'll remember next time!
Not a problem! It's just a courtesy is all. :yes