Creating a new sheet and populating it with data at the same time - Help please!

[mackemforever]

Hi everybody, really needing some help here because I've been bashing my head against this problem for ages and got nowhere. Last time I used VBA was back in school, about 15 years ago.

What I'm trying to achieve is below:

Problem 1

Customer information entered in table from A1:B4 on New Info sheet.

Upon pressing the New Customer button I want the following to happen.

1 – New sheet is created named after Cell B2

2 – Cells A1:A4 are copied and pasted in A1:A4 on the new sheet

3 – Cells B1:B4 and cut and pasted in B1:B4 on the new sheet (or copied over and then cleared on Sheet 1 afterwards)

Now I have the first step sorted with the code below, but I can’t figure out the rest.

Sub Rectangle1_Click()
'Step 1: If Error
On Error GoTo MyError
'Step 2: New Sheet
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = Range("'New Info'!$B$2").Value
Exit Sub
'Step 3: Error Action
MyError:
MsgBox "There is already a sheet called that."
End Sub

Problem 2

On New Info sheet I want to be able to select a customer and then enter their most recent service type and date. Upon pressing a button this is then copied over to their customer sheet and updated on the master sheet.

When the button is pressed the following should happen:

1 – Cells D2:D3 should be copied to the sheet that matches the name selected on the drop-down menu in D1. On that customer sheet it is pasted on the first empty row of their services table.

2 – Cells D2:D3 are also copied to the row on the Master Sheet matching the name selected in D1

 

[Fluff]

Hi & welcome to MrExcel.
For part 1 how about

Sub mackemforever()
   With Sheets("New Info")
      If Evaluate("'" & .Range("B2").Value & "'!A1)") Then
         MsgBox "sheet name already exists"
         Exit Sub
      End If
      Sheets.Add(, Sheets(Sheets.Count)).Name = .Range("B2").Value
      .Range("A1:B4").Copy Range("A1")
      .Range("B1:B4").ClearContents
   End With
End Sub

 

[braindiesel]

1)

myTargSh =  Range("'New Info'!$D$1").Value
Range("D2:D3").copy
Sheets(myTargSh).activate
Range("A1").select ' change A1 to the top cell of your destination
If Activecell.Offset(1,0).value <> "" then selection.end(xldown).select
Activecell.Offset(1,0). PasteSpecial Paste:=xlPasteValues