Need Help VBA

C

craig2525

Board Regular
Joined
Oct 30, 2018
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
I had a sheet set up to grab names in column A to create tabs of the names and copy rows with those names. It was column A but now I want to use column N. I can create the tabs and headers, but nothing copies. Where am I going wrong?


Option Explicit

Sub CreateSheets()

Dim Cell As Range
Dim RngBeg As Range
Dim RngEnd As Range
Dim Wks As Worksheet

Set RngBeg = Worksheets("Sheet1").Range("N2")
Set RngEnd = Worksheets("Sheet1").Cells(Rows.Count, "N").End(xlUp)

' Exit if the list is empty.
If RngEnd.Row < RngBeg.Row Then Exit Sub
Application.ScreenUpdating = False
For Each Cell In Worksheets("Sheet1").Range(RngBeg, RngEnd)
On Error Resume Next
' No error means the worksheet exists.
Set Wks = Worksheets(Format(Cell.Value, "[$-409]dmmmyy;@"))

' Add a new worksheet and name it.
If Err <> 0 Then
Set Wks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
Wks.Name = Format(Cell.Value, "[$-409]dmmmyy;@")
End If
On Error GoTo 0
Next Cell
Application.ScreenUpdating = True
MakeHeaders
End Sub

Sub MakeHeaders()
Dim srcSheet As String
Dim dst As Integer
srcSheet = "Sheet1"
Application.ScreenUpdating = False
For dst = 1 To Sheets.Count
If Sheets(dst).Name <> srcSheet Then
Sheets(srcSheet).Rows("1:1").Copy
Sheets(dst).Activate
Sheets(dst).Range("a1").PasteSpecial xlPasteValues
'ActiveSheet.PasteSpecial xlPasteValues
Sheets(dst).Range("a1").Select
End If
Next
Application.ScreenUpdating = True
CopyData
End Sub

Sub CopyData()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
On Error Resume Next
Lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
Dim ans2 As String

NoVisi

For i = 2 To Lastrow
ans = Sheets("Sheet1").Cells(i, 1).Value
ans2 = Format(ans, "[$-409]dmmmyy;@")
Sheets("Sheet1").Rows(i).Copy Sheets(ans2).Rows(Sheets(ans2).Cells(Rows.Count, "A").End(xlUp).Row + 1)
Next

Visi

Application.ScreenUpdating = True

Sheets("Sheet1").Activate
Sheets("Sheet1").Range("N1").Select

Exit Sub

Application.ScreenUpdating = True

End Sub

Sub NoVisi()
Dim CommandButton1 As Object

CommandButton1.Visible = False

End Sub

Sub Visi()
Dim CommandButton1 As Object

CommandButton1.Visible = True
End Sub

Private Sub CommandButton1_Click()

End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Try this modification:

VBA Code: 
Option Explicit

Sub CreateSheets()

Dim Cell As Range
Dim RngBeg As Range
Dim RngEnd As Range
Dim Wks As Worksheet

Set RngBeg = Worksheets("Sheet1").Range("N2")
Set RngEnd = Worksheets("Sheet1").Cells(Rows.Count, "N").End(xlUp)

' Exit if the list is empty.
If RngEnd.Row < RngBeg.Row Then Exit Sub
Application.ScreenUpdating = False
For Each Cell In Worksheets("Sheet1").Range(RngBeg, RngEnd)
On Error Resume Next
' No error means the worksheet exists.
Set Wks = Worksheets(Format(Cell.Value, "[$-409]dmmmyy;@"))

' Add a new worksheet and name it.
If Err <> 0 Then
Set Wks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
Wks.Name = Format(Cell.Value, "[$-409]dmmmyy;@")
End If
On Error GoTo 0
Next Cell
Application.ScreenUpdating = True
MakeHeaders
End Sub

Sub MakeHeaders()
Dim srcSheet As String
Dim dst As Integer
srcSheet = "Sheet1"
Application.ScreenUpdating = False
For dst = 1 To Sheets.Count
If Sheets(dst).Name <> srcSheet Then
Sheets(srcSheet).Range("N1").Copy
Sheets(dst).Activate
Sheets(dst).Range("A1").PasteSpecial xlPasteValues
'ActiveSheet.PasteSpecial xlPasteValues
Sheets(dst).Range("N1").Select
End If
Next
Application.ScreenUpdating = True
CopyData
End Sub

Sub CopyData()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long, LR As Long
On Error Resume Next
Lastrow = Sheets("Sheet1").Cells(Rows.Count, "N").End(xlUp).Row
Dim ans As String
Dim ans2 As String

NoVisi

For i = 2 To Lastrow

ans = Sheets("Sheet1").Cells(i, 14).Value
LR = Sheets(ans).Range("A" & Rows.Count).End(xlUp).Row
Sheets(ans).Range("A" & LR + 1).Value = ans
Next

Visi

Application.ScreenUpdating = True

Sheets("Sheet1").Activate
Sheets("Sheet1").Range("N1").Select

Exit Sub

Application.ScreenUpdating = True

End Sub

Sub NoVisi()
Dim CommandButton1 As Object

CommandButton1.Visible = False

End Sub

Sub Visi()
Dim CommandButton1 As Object

CommandButton1.Visible = True
End Sub

Private Sub CommandButton1_Click()

End Sub
 
Upvote 0
To clarify, are you saying that your data had the names in column A but they are now in Column N, or are you wanting to paste the name in each tab in column N?

My modification assumes your list of name in Sheet1 is in column N and that you want to paste the results in Column A on their respective sheet.
 
Upvote 0
Correct. My original names were in column A but now in column N. That is working as far as the tabs are being created for each name. The header is being copied into each tab. No data is being copied. Top line is copied but the rows below are not.
 

Attachments

  • Screenshot 2024-08-20 154908.png
    Screenshot 2024-08-20 154908.png
    11.1 KB · Views: 5
Upvote 0
See if this works for you :

VBA Code: 
Option Explicit

Sub CreateSheets()

    Dim Cell    As Range
    Dim RngBeg  As Range
    Dim RngEnd  As Range
    Dim Wks     As Worksheet

        Set RngBeg = Worksheets("Sheet1").Range("N2")
        Set RngEnd = Worksheets("Sheet1").Cells(Rows.Count, "N").End(xlUp)

        ' Exit if the list is empty.
        If RngEnd.Row < RngBeg.Row Then Exit Sub
Application.ScreenUpdating = False
        For Each Cell In Worksheets("Sheet1").Range(RngBeg, RngEnd)
            On Error Resume Next
                ' No error means the worksheet exists.
                Set Wks = Worksheets(Format(Cell.Value, "[$-409]dmmmyy;@"))

                ' Add a new worksheet and name it.
                If Err <> 0 Then
                    Set Wks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
                    Wks.Name = Format(Cell.Value, "[$-409]dmmmyy;@")
                End If
            On Error GoTo 0
        Next Cell
Application.ScreenUpdating = True
MakeHeaders
End Sub

Sub MakeHeaders()
Dim srcSheet As String
Dim dst As Integer
srcSheet = "Sheet1"
Application.ScreenUpdating = False
For dst = 1 To Sheets.Count
    If Sheets(dst).Name <> srcSheet Then
    Sheets(srcSheet).Rows("1:1").Copy
    Sheets(dst).Activate
    Sheets(dst).Range("A1").PasteSpecial xlPasteValues
    'ActiveSheet.PasteSpecial xlPasteValues
    Sheets(dst).Range("A1").Select
    End If
Next
Application.ScreenUpdating = True
CopyData
End Sub

Sub CopyData()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
On Error Resume Next
Lastrow = Sheets("Sheet1").Cells(Rows.Count, "N").End(xlUp).Row
Dim ans As String
Dim ans2 As String

NoVisi

    For i = 2 To Lastrow
    ans = Sheets("Sheet1").Cells(i, 14).Value
    ans2 = Format(ans, "[$-409]dmmmyy;@")
        Sheets("Sheet1").Rows(i).Copy Sheets(ans2).Rows(Sheets(ans2).Cells(Rows.Count, "A").End(xlUp).Row + 1)
    Next
    
Visi

Application.ScreenUpdating = True

Sheets("Sheet1").Activate
Sheets("Sheet1").Range("A1").Select

Exit Sub

Application.ScreenUpdating = True

End Sub

Sub NoVisi()
Dim CommandButton1 As Object

CommandButton1.Visible = False

End Sub

Sub Visi()
Dim CommandButton1 As Object

CommandButton1.Visible = True
End Sub
 
Upvote 0
A bit confused on my end.

1. Are you trying to copy just the names which were in Column A, but are now in Column N and paste that result in the respective sheets in Column A?
2. Are you trying to copy the whole row where the name is found and paste the whole row in the respective sheets?
3. Something else?

My modification assumed you had something along these lines:

1724183618277.png


And wanted this:

1724183676497.png
 
Upvote 0
Tried to run it.
 

Attachments

  • Screenshot 2024-08-20 155727.png
    Screenshot 2024-08-20 155727.png
    15.3 KB · Views: 5
Upvote 0
Hi Craig,

In your original code there are still some lines that are referring to Col. A which presumably is empty.

Try changing these lines...
VBA Code: 
Lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
ans = Sheets("Sheet1").Cells(i, 1).Value

...to these:
VBA Code: 
Lastrow = Sheets("Sheet1").Cells(Rows.Count, "N").End(xlUp).Row
ans = Sheets("Sheet1").Cells(i, 14).Value

Note - you rarely need to create separate tabs with the exact same layout so maybe you could give the workbook's logic a rethink (maybe just filter each unique name in Sheet1).

Regards,

Robert
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

C
  • Question Question
Copy Data (Almost there)
Replies
2
Views
421
craig2525
C
I
  • Question Question
Opening worksheet in another workbook advice
Replies
1
Views
178
ipbr21054
I
I
  • Solved
Replaced CLICK event for CALL event but now see Run time error
Replies
6
Views
286
igold
igold
I
  • Solved
Code moves worksheets columns to the left thus not showing column A
Replies
5
Views
197
Peter_SSs
Peter_SSs
O
  • Question Question
User-defined type not defined - vba/excel error
Replies
4
Views
211
RoryA
RoryA

Forum statistics

Threads
1,223,882
Messages
6,175,166
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top