Sleeper: Help with macro please

[MonkeyMe]

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]

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>

1. how will the macro know where to find the original info? Current selection? look for &quot;Repeating&quot; on the active sheet? something else?
2. 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?
3. 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?
4. why is &quot;Number 1&quot; the name for the new workbook?
5. what should be the name of the worksheet into which the info is posted?

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

[MonkeyMe]

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]

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]

Hi MonkeyMe,
If you select your code and click on the VBA button, it formats the code, making it more readable. (as edited)

[MonkeyMe]

yeah, sorry guys new workbook

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

[mdmackillop]

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]

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]

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

??

[vonpookie]

Sorry, guys. Looks like the OP cross-posted. :

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

[Bob Phillips]

??

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

[Zack Barresse]

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.

[MonkeyMe]

ooops, sorry about that guys/girls!
i'll remember next time!

[Zack Barresse]

ooops, sorry about that guys/girls!
i'll remember next time!

Not a problem! It's just a courtesy is all.