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.
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
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
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
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
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
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)
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
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
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
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!!!!
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