String Array

[wethegoodvibes]

Hello

I am working on an excel file with 7 sheets. The main interaction that a user will have with the worksheet is through a user form. The user form as multiple pages and one page is already completely functional. The next page, however, is giving me a lot of problems. The first problem I am working through not being able to paste a collection cells that is on a reference sheet (sheet 7). The collection of cells is a template of sorts with colored cells and a border in certain spots. It is 2 columns x 12 rows. I need to paste that template every time the user hits the “New Day” button on the user form.

So far I understand that the following code may work to go to a new line every time the “New Day” button is pressed. I have defined the template selection as “StartUpTemplate” on sheet 7. My thought is that it will paste that template in the D and E columns every time the “New Day” button is pressed. But it doesn’t know what “StartUpTemplate” is. So how do I store that template into a variable to paste on command?

Private Sub NewDayCommandButton_Click()

Dim NewDay As Range
Dim StartUpData As Worksheet

Set StartUpData = Sheet5
Set NewDay = StartUpData.Range("D65356").End(xlUp).Offset(13, 0)
NewDay.Offset(0, 0).Value = StartUpTemplate

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Can you please supply links to all other sites where you have asked this question.
Thanks

 

[trunten]

Perhaps something like this? I've assumed the template sheet is called "Template"

Sub newDay()
    Dim sht As Worksheet, x As Long
    x = 1
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    On Error Resume Next
    Set sht = Sheets("New Day (" & x & ")")
    While Not sht Is Nothing
        x = x + 1
        Set sht = Nothing
        Set sht = Sheets("New Day (" & x & ")")
    Wend
    On Error GoTo 0
    Sheets(Sheets.Count).Name = "New Day (" & x & ")"
End Sub

 

[wethegoodvibes]

Hi & welcome to MrExcel.
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Can you please supply links to all other sites where you have asked this question.
Thanks

https://www.excelforum.com/excel-programming-vba-macros/1288203-string-array.html#post5185605
http://www.vbaexpress.com/forum/showthread.php?65790-String-Array&p=393895&posted=1#post393895

 

[Fluff]

Cheers

 

[wethegoodvibes]

This is what ended up working.

Private Sub NewDayCommandButton_Click()


Dim StartUpTemPlate As Range, SUTPaste As Range, SUDate As Range


Dim StartUpData As Worksheet


Set StartUpTemPlate = Sheet1.Range("e2:f13")
Set StartUpData = Sheet5
Set SUTPaste = StartUpData.Range("D65356").End(xlUp).Offset(0, 0)


StartUpTemPlate.Copy
SUTPaste.Offset(0, 0).PasteSpecial

End Sub

 

[Fluff]

Glad you got it sorted & thanks for letting us know the solution.