VBA Macro - Copy Dynamic Range from Sheet 1 to next available row on sheet 2

[Meucci98]

Good day all, I hope this makes sense.

I have a workbook that contains 2 sheets to start:

One is called "Master" (Sheet1) - I have a macro that creates a copy of this sheet and renames it, so I hit the button, it creates a new sheet, I fill out the necessary cells, save it and do what I need.
On the new sheet (created from "Master") I have data in the following cells that I need to copy to sheet 2 ("Master List")starting at the next empty row
C2:C5, C12, (A19:J) (Dynamic Range)

Any insight would be greatly appreciated.

 

[Skyybot]

What do you name the "Created" sheet? (It would be Sheet 3 if I'm understanding you correctly).

 

[Meucci98]

What do you name the "Created" sheet? (It would be Sheet 3 if I'm understanding you correctly).

As per the macro the named gets changed automatically:

Sub x()

Dim lngLoop As Long
Dim wsTest As Excel.Worksheet
Dim wsSource As Excel.Worksheet

Dim strNewName As String

Const ROOTName As String = "23-0"
Const SourceSheet As String = "master"
Const MaxTries As Long = 1000

'// Set a reference to the source sheet. If it does not exist then
'// the standard debug window will be displayed
Set wsSource = Sheets(SourceSheet)

'// The code may raise errors - ignore, these are handled directly
'// by the code
On Error Resume Next

'// Loop a max number of times. I've never seen
'// a workbook with 150 worksheets, never mind 1000
'// as defined here.. Change the CONST declaration to
'// something a littl emore suitable for your needs
For lngLoop = 1 To MaxTries

'// Derive the new worksheet name
strNewName = ROOTName & CStr(lngLoop)

'// See if it exists already...
Set wsTest = Sheets(strNewName)

'// If wsTest is nothing then sheet SHCRx does not exist
If wsTest Is Nothing Then

'// Exit the loop. Finished here
Exit For
Else
'// It does exist. Clear the reference to the worksheet
Set wsTest = Nothing
'// Clear the error
Err.Clear
End If

'// If it gets to here then it loops again
Next

'// Last check - there's less than (1000) worksheets?
If lngLoop < MaxTries Then

'// Copy the source sheet and place at end of sheets tab.
wsSource.Copy After:=Worksheets(Worksheets.Count)

'// Rename the new sheet. Only a general ACTIVESHEET reference
'// as this will be the active sheet after the copy
With ActiveSheet
.Name = strNewName
.Range("AM3").Value = "23-0" & CStr(lngLoop)
'// OR
'// .Range("AI3").Value = strNewName
End With
Else

'// Tell user the sheet was not copied.
MsgBox "Are you really going to work with " & CStr(MaxTries) & " worksheets...?", vbExclamation, "Don't be silly"
End If

End Sub