VBA newbie needs help

[adugent]

Hey y'all! VBA newbie here... I'm more familiar with R but trying to do something for work in Excel. I want to automatically have an entire row of data copied to a new sheet based off a value that is input in column D. My range is A-N and column names are in row 1. I have a master sheet and then 9 additional sheets, all labeled '1', '2', '3', etc.

What I'm trying to do is copy an entire row of data from the 'master' sheet to the respective numerical sheet based off of the number that is put in column d on the master sheet. I have a drop down in column d with labels 1-9 listed. So, for example, if 2 is entered into column d, the data on the 'master' sheet would be automatically copied to the next row available in sheet '2', and if 9 is entered into column d, the data on the 'master' sheet would be automatically copied to the next row available in sheet '9', etc. I need this code for all sheets labeled 1-9.

I do not want the rows deleted once copied to their respective sheets, but rather, want them to all stay on the master sheet.

Thank you SO much for any and all help you're able to offer! It is greatly appreciated!!

 

[Eric W]

Welcome to the MrExcel forum!

Open a copy of your workbook. From your master sheet, right click on the tab on the bottom and select "View Code". Paste this code in the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr as long
    
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    On Error GoTo BadName:
    
    Set newsht = Worksheets(Target.Value & "")
    lr = newsht.Cells(Rows.Count, "A").End(xlUp).Row + 1
    newsht.Cells(lr, "A").Resize(, 14).Value = Cells(Target.Row, "A").Resize(, 14).Value
    
BadName:
End Sub

Go back to Excel and try it out! This copies values but not formatting, but we can do that too if you want. Let me know how it works.

 

[adugent]

Thank you so much, Eric! I'm getting this error when trying to run the code

Any idea what to do from here? I really appreciate your patience and willingness to help an newb.

 

[adugent]

Welcome to the MrExcel forum!

Open a copy of your workbook. From your master sheet, right click on the tab on the bottom and select "View Code". Paste this code in the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr as long
   
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    On Error GoTo BadName:
   
    Set newsht = Worksheets(Target.Value & "")
    lr = newsht.Cells(Rows.Count, "A").End(xlUp).Row + 1
    newsht.Cells(lr, "A").Resize(, 14).Value = Cells(Target.Row, "A").Resize(, 14).Value
   
BadName:
End Sub

Go back to Excel and try it out! This copies values but not formatting, but we can do that too if you want. Let me know how it works.

Thank you so much, Eric! I'm getting this error when trying to run the code

Any idea what to do from here? I really appreciate your patience and willingness to help an newb.

 

[Eric W]

It sounds like you have some stray text before the Private Sub line. Make sure there's nothing else on the sheet other than what I gave you. If you want to put a comment in, make sure it starts with a ' character. You'll know it's a comment because it changes color (usually green).

 

[adugent]

Welcome to the MrExcel forum!

Open a copy of your workbook. From your master sheet, right click on the tab on the bottom and select "View Code". Paste this code in the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr as long
   
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    On Error GoTo BadName:
   
    Set newsht = Worksheets(Target.Value & "")
    lr = newsht.Cells(Rows.Count, "A").End(xlUp).Row + 1
    newsht.Cells(lr, "A").Resize(, 14).Value = Cells(Target.Row, "A").Resize(, 14).Value
   
BadName:
End Sub

Go back to Excel and try it out! This copies values but not formatting, but we can do that too if you want. Let me know how it works.

It sounds like you have some stray text before the Private Sub line. Make sure there's nothing else on the sheet other than what I gave you. If you want to put a comment in, make sure it starts with a ' character. You'll know it's a comment because it changes color (usually green).

I deleted the code, closed out, and restarted, and unfortunately I'm still having the same issue. I'm going to play around with it and see if I can't figure out what is wrong. Thanks again!!