0

I'm having a problem. At my work we often get spreadsheets with locations. We need to spread out each of the locations to a different worksheet. Because then we list nearby businesses to advertise to. This wasnt much of a problem when they gave us spreadsheets with 5 locations, but more recently we've been getting sheets with upwards of 100 locs on one sheet.

I want to make a function that takes a given row, takes the first value and make it's content (location number) into a new sheet. Copy that row to the sheet named for it, and paste it. This is my current code. I'm having problems with "Type mismatch" and "Object required". Any suggestions on these issues?


Sub TestRowThree()
    Dim row As Integer
    row = 3
    CopyRow (row)
End Sub

Sub CreateNewSheet(strName As String)
    Sheet.Add.Name = strName
End Sub

Sub CopyRow(rowNum As Integer)
    Dim mainSheetName As String
    mainSheetName = ActiveWorkbook.ActiveSheet.Name
    Dim newSheetName As String
    newSheetName = Sheets(mainSheetName).Range("$A$" & rowNum).Value
    Sheets(mainSheetName).Select
    Range("A" & rowNum & ":H" & rowNum).Cut
    CreateNewSheet (newSheetName)
    Sheets(newSheetName).Select
    Range("A" + rowNum + ":H" + rowNum).Select
    ActiveSheet.Paste
End Sub
1

Found a few small, but crucial errors in the code.

  1. In "CreateNewSheet", Sheet needed to be Sheets Sheet wasn't Referencing anything, Hence the "Object Required" error
  2. When selecting the new range to paste into, I had mistakenly used + instead of & Yielding my type mismatch. Note: I then realized once I could execute my code that I was copying them onto their original row, Rather Annoying, Fixed it by copying to the same row.

I posted my changed code below.


Sub TestRowThree()
    Dim row As Integer
    row = 3
    CopyRow (row)
End Sub

Sub CreateNewSheet(strName As String)
    Sheets.Add.Name = strName
End Sub

Sub CopyRow(rowNum As Integer)
    Dim mainSheetName As String
    mainSheetName = ActiveWorkbook.ActiveSheet.Name
    Dim newSheetName As String
    newSheetName = Sheets(mainSheetName).Range("$A$" & rowNum).Value
    Sheets(mainSheetName).Select
    Range("A" & rowNum & ":H" & rowNum).Copy
    CreateNewSheet (newSheetName)
    Sheets(newSheetName).Select
    Range("A1:H1").Select
    ActiveSheet.Paste
End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.