Results 1 to 14 of 14

Thread: Copy Worksheet & formatting

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Copy Worksheet & formatting

    Hi,

    Wish to copy the attached worksheet, (formatting, row/column size, No formula's) and pastevalue special into a new file called FM Report Builder. Worksheet - Freehold Managers.


    New file is saved in W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\FM Report builder.xlsx.

    Rgds
    Attached Files Attached Files

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copy Worksheet & formatting

    to change the file name you need to edit this line
    myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"
    but i am still not sure what result you want the code should create a file named
    FEB PM Template.xlsx please specify the exact file name you want
    as stated in the other thread it maybe there is no \ between the path and the filename, which would need to be fixed as suggested in that thread
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Copy Worksheet & formatting

    Hello.

    The issue of the file name has now been resolved. Hence why I've started a new thread. I simply removed Reports as you suggested.

    This is a now a new thread where I wish to copy that same worksheet and place into a new workbook as per #1 post.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copy Worksheet & formatting

    so you want an additional copy of the same workbook in another folder?

    Code:
    b.SaveAs myfile
    b.saveas "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\FM Report builder.xlsx"
    where the first line is in the existing code and the second line can be anywhere after that line
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Copy Worksheet & formatting

    No. I just want a new macro to be created. I want to copy the worksheet - FM Master....and paste into a new workbook called. FM report builder.xlsx.

    When copying the file and pasting. I want paste value special. So no formula's and keep the column width and row height the same as the original.

    Also paste the data into a worksheet called "FM" within FM report builder.

    Hope I made this clearer enough to understand? :-)

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copy Worksheet & formatting

    isn't that what the other macro did with out emailing the workbook? just change the path and filename and remove the emailing part
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Copy Worksheet & formatting

    Morning.

    Don't think its as simple as that.

    I've removed the email part and left with the below.

    The below code doesn't save the file in the new workbook under the new worksheet.



    Dim w As Worksheet, b As Workbook,
    Dim mypath As String, myfile As String,
    mypath = "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting"


    Set w = Sheets("Master FM")
    Set b = Workbooks.Add
    With w
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Cells(1, 1).Resize(lr, lc).Copy
    b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
    .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
    End With
    b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value2

    myfile = mypath & Format(Date, "MMM") & " Freeholders PM Template.xlsx"

    Workbooks("Freehold Manager Template").Sheets("Control").Activate
    Range("TimeStamp10").Value = Format(Now, "dd/mm/yy HH:mm:SS")

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Copy Worksheet & formatting

    So I've started writing the below and defined how to copy the sheet 'FM Master' in terms of formatting.

    Where I get stuck is how to define where to paste the copied sheet into my specific file within a specific folder.

    Now I've set B as the workbook, how do I write the code so that it pastes into a specific worksheet - Freehold Managers. Also Where do I write into the code where the file name FM report builder and where its saved?


    Sub SendPDFABC()

    Dim w As Worksheet, b As Workbook
    Dim mypath As String, myfile As String
    mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting"

    Set w = Sheets("Master FM")
    Set b = Workbooks
    With w
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Cells(1, 1).Resize(lr, lc).Copy
    b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
    .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
    End With
    b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value2



    Application.DisplayAlerts = False


    Workbooks("Freehold Manager Template").Sheets("Control").Activate
    Range("TimeStamp10").Value = Format(Now, "dd/mm/yy HH:mm:SS")

    End Sub

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copy Worksheet & formatting

    The below code doesn't save the file in the new workbook under the new worksheet.
    you have missed the line
    Code:
    b.saveas myfile
    looks like you should have a \ between mypath and the file name

    to specify the sheet FM, you need to change the name of the default sheet in the new workbook
    Code:
    b.sheets(1).name = "FM"
    if there is only going to be 1 worksheet then you can either use the name or the index of 1 to specify which sheet to
    in your previous macro the sheet was always specified by index of 1
    the line below specified to paste to cell A1 on sheet 1 (regardless on name)
    .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")

    Workbooks("Freehold Manager Template").Sheets("Control").Activate
    Range("TimeStamp10").Value = Format(Now, "dd/mm/yy HH:mm:SS")
    the first line of these should fail as the file name is not as saved, BUT the saved file should already be the active workbook
    the named range (timesamp 10) will not exist in the new workbook, you need to either define the named range or use the actual cell address
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Copy Worksheet & formatting

    ok. I've updated the code.

    Code is breaking on this line.

    Set C = Sheets("Freehold Managers") Subscript out of range.

    I checked the tab in the FM Report builderExample file and the sheet name is correct. so not sure why this is breaking.

    Also. after I've pasted into worksheet Freehold Managers i want to save this workbook. I tried adding this line but it didn't work. Method failure.

    b.Saveas myfile.


    Sub SendPDFABC()

    Dim w As Worksheet, b As Workbooks, C As Worksheets
    Dim mypath As String, myfile As String
    mypath = "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting"
    myfile = "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\FM Report builderExample"

    Set w = Sheets("Master FM")
    Set b = Workbooks
    Set C = Sheets("Freehold Managers")

    With w
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Cells(1, 1).Resize(lr, lc).Copy
    End With

    b.Open myfile

    C.Sheets.Range("a1").PasteSpecial xlPasteColumnWidths
    C.Sheets.Cells(4, 1).Resize(lr - 4, lc).Value = C.Sheets.Cells(4, 1).Resize(lr - 4, lc).Value2

    Application.DisplayAlerts = False

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copy Worksheet & formatting

    I tried adding this line but it didn't work. Method failure.

    b.Saveas myfile.
    because between post #7 an post #8 you changed the line Set b = Workbooks.Add

    Set C = Sheets("Freehold Managers") Subscript out of range.
    of course it would you do not specify which workbook contains that sheet, if it is in the new workbook there is no sheet by that name unless you rename the default worksheet, see post #9

    you have removed most of the code to copy the original data and column formatting
    the original code from your previous thread needed very little modification to achieve what you wanted

    you should consider taking lessons in programming, as you should not rely on forums to provide the code for commercial application
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Copy Worksheet & formatting

    because between post #7 an post #8 you changed the line Set b = Workbooks.Add

    Yes because in #7 I took out what I thought was required in-order to get the desired effect. I then tried to write my own code and posted in #8.

    As previously stated. I dont want to add a workbook. I want to OPEN a new workbook saved down in a different folder. Something I don't think you've quite grasped. Hence i why I posted #8 without the line you mentioned.


    of course it would you do not specify which workbook contains that sheet, if it is in the new workbook there is no sheet by that name unless you rename the default worksheet, see post #9

    Okay so show me how to write it then? Its obvious I'm having trouble with this so alittle help would be nice


    you have removed most of the code to copy the original data and column formatting
    the original code from your previous thread needed very little modification to achieve what you wanted


    No I haven't. all copying and formatting code has been left in. As previously stated. I only removed the email attachment and workbook add part. I don't understand why you couldn't simply write a new piece code to replicate what I require.

    you should consider taking lessons in programming, as you should not rely on forums to provide the code for commercial application....

    Well thats a flippant remark isn't it. Of course I need lessons and I'am. This is supposed to be a forum where people get help and let learn new skills and improve their understanding. WE CAN'T ALL BE GOOD AS YOU!!!

    I thought the name of this thread is office development? So we could develop our office skills!!!

    I don't use all of the code in here for commercial application. I only take the parts I want!!!!

    If you can't be botherd to help then I go elsewhere!!!!

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copy Worksheet & formatting

    I want to OPEN a new workbook
    there is 2 choices to work with a workbook that is not currently open, OPENan existing workbook, specifying the full path and filename, or ADDto open a new blank workbook

    No I haven't. all copying and formatting code has been left in
    if you say so!!
    I don't understand why you couldn't simply write a new piece code to replicate what I require.
    you answered this below
    This is supposed to be a forum where people get help and let learn new skills and improve their understanding
    the forum is to help people to learn, it is not really a free code writing service, i probably provide more direct code than most, i have posted code in just about all of your threads, a lot of others provide unrelated examples, to help with understanding, rather than actual code that can be pasted directly in and work



    Set C = Sheets("Freehold Managers")
    you stated you wanted the sheet to be named FM, and i already gave an example to rename the sheet post #9

    If you can't be botherd to help then I go elsewhere!!!!
    i think i have been very generous with my help, but it seems like you just want the whole code written, which will never help you to learn how to code


    WE CAN'T ALL BE GOOD AS YOU!!!
    i make many mistakes both in writing code and understanding what you actually want or asking the right question to get an exact answer without having several goes

    i have asked previously for you to post any code within code tags, but it seems too much trouble for you too even to learn how to do that, it is up to you to make some effort to
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Copy Worksheet & formatting

    ok well lets just agree to disagree on this.... You have helped in previous posts and I thank you for that.


    Rgds


    Goodbye

Posting Permissions

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



Click Here to Expand Forum to Full Width