String Array

[YeeVibes]

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]

Cross posted on multiple sites

[YeeVibes]

Cross posted on multiple sites

Yes! I need help fast.

[Fluff]

In that case care to post links to all other sites where you have asked this.
as per the rules
http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

[YeeVibes]

https://www.excelforum.com/excel-programming-vba-macros/1288203-string-array.html#post5185605
https://www.mrexcel.com/forum/excel-questions/1108428-string-array.html

[Kenneth Hobs]

Thanks for posting links.

You can use copy/paste or the commented copy/pastespecial method. I guess sheet7 was a codename as well.


Private Sub NewDayCommandButton_Click()
Dim NewDay As Range, StartUpData As Worksheet
Dim r As Range, rr As Range

Set r = sheet7.Range("StartUpTemplate")
Set StartUpData = Sheet5
Set NewDay = StartUpData.Cells(Rows, Count, "D").End(xlUp).Offset(13, 0)
Set rr = NewDay.Resize(r.Rows.Count, r.Columns.Count)

r.Copy rr
'r.Copy
'rr.PasteSpecial xlPasteFormats
'rr.PasteSpecial xlPasteColumnWidths
'rr.PasteSpecial xlPasteFormats

Application.CutCopyMode = False
End Sub

[YeeVibes]

Thank you so much for you reply! I learned from your method!

[YeeVibes]

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