+ Reply to Thread
Results 1 to 4 of 4

copy template and/or values into multiple sheets

  1. #1
    Registered User
    Join Date
    07-02-2010
    Location
    Houston, TX
    MS-Off Ver
    Excel 2007
    Posts
    3

    copy template and/or values into multiple sheets

    The problem I'm having is that I have this Excel Worksheet in one workbook which I want to use for a template, and I have to make all the other worksheets, which are like 19 workbooks. The template has a title of "blahblahblah" and it should appear in bold between cells B1 to K1 with the cells being merged and the font being bold. The template has a lot more information in it and I want to apply it to all the worksheets in all the workbooks. I got it so far to run through all the worksheets in all the workbooks, and I have tried everything I know how to get the cells to do something, but nothing happens. Here is the code I have:


    Option Explicit
    Public Sub WBFormat()

    Dim strMasterFile As String
    Dim strWBNames(19)
    Dim strPath As String
    Dim n As Integer
    Dim objWS As Worksheet
    Dim workname As Worksheet
    Dim cell1, cell2, rng As Range
    Dim bk As Worksheet

    On Error Resume Next

    'set master file name
    strMasterFile = "template2.xls"

    'set path to files (note closing "\")
    strPath = "C:\Daddys_stuff\"

    'setup file names
    strWBNames(1) = "D1.xls"
    strWBNames(2) = "D2.xls"
    strWBNames(3) = "D3.xls"
    strWBNames(4) = "D4.xls"
    strWBNames(5) = "D5.xls"
    strWBNames(6) = "J1.xls"
    strWBNames(7) = "J2.xls"
    strWBNames(8) = "J3.xls"
    strWBNames(9) = "J4.xls"
    strWBNames(10) = "J5.xls"
    strWBNames(11) = "NoM.xls"
    strWBNames(12) = "R1.xls"
    strWBNames(13) = "R2.xls"
    strWBNames(14) = "R3.xls"
    strWBNames(15) = "R4.xls"
    strWBNames(16) = "R5.xls"
    strWBNames(17) = "R6.xls"
    strWBNames(18) = "R7.xls"
    strWBNames(19) = "R8.xls"


    'Copy formatting from each worksheet in Copy_Master.xls to
    'each worksheet in the named workbooks

    'Set cell1 = bk.Cells(1, 2)
    'Set cell2 = bk.Cells(12, 22)
    'Set rng = Range(cell1, cell2)

    For n = 1 To 19
    workname = (strPath & strWBNames(n))
    Workbooks.Open (strPath & strWBNames(n))

    For Each objWS In workname
    'If workname.Range("B1", "K1") <> " " Then
    workname.Range("B1:K1").Offset(1, 0).Select
    workname.Range("B1:K1").Value = "blahblahblah"
    workname.Range("B1:K1").Merge
    workname.Range("B1:K1").Font.Bold = True

    'Else

    'workname.Range("B1", "K1").Value = "blah,blah,blah"
    'workname.Range("B1", "K1").Merge
    'workname.Range("B1", "K1").Font.Bold = True

    'End If


    'Workbooks(strWBNames(n)).Worksheets(objWS.Name).Range("A1", "O12") _
    .PasteSpecial Paste:=xlPasteFormats
    Next objWS
    'Workbooks(strWBNames(n)).Save
    Next n
    End Sub


    I have commented some code out and this the best I know how to do for someone who has practically no experience in VBA. This has to be finished in a couple of days so I would appreciate your help.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: copy template and/or values into multiple sheets

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    07-02-2010
    Location
    Houston, TX
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: copy template and/or values into multiple sheets

    I didn't know about the rule.
    Last edited by VBAv4; 07-02-2010 at 01:50 AM. Reason: was confused

  4. #4
    Registered User
    Join Date
    07-02-2010
    Location
    Houston, TX
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: copy template and/or values into multiple sheets

    I hope this helps - I am very new at this and VBA forums and I don't want to break any rules.




    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1