• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to export and save worksheets' format/text only in a new workbook with loop?

Otawata

New Member
Hi!

I have a master workbook full of formulas and reference worksheets. I had to strip down to attach the example file, here so there won't be any formulas.

I'd like to export and save 3 of the worksheets into a new workbook - keeping the formats and values, only. They are:
"Totals"; "Baked goods"; "Knit goods"

If possible, I'd like to run this in a loop, so that a file is created for each seller in the drop-down selector located on the "Totals" tab of the attached example.

Please let me know if you can help or where I might start. Thank you kindly!
 

Attachments

Otawata

New Member
I have managed to use some VBA to copy from the master to a template, but if there's a way to also append a file name based on the seller's name and loop through all the seller options, I'd be most appreciative for the assist.

>>> use code - tags <<<
Code:
Sub Copy_Method()
    'Copy range to another workbook
    Workbooks("Master.xlsm").Worksheets("Totals").Range("A1:BA500").Copy
    Workbooks("Template.xlsm").Worksheets("Totals").Range("A1").PasteSpecial Paste:=xlPasteFormats
    Workbooks("Master.xlsm").Worksheets("Totals").Range("A1:BA500").Copy
    Workbooks("Template.xlsm").Worksheets("Totals").Range("A1").PasteSpecial Paste:=xlPasteValues
  
    Workbooks("Master.xlsm").Worksheets("Baking goods").Range("A1:BA500").Copy
    Workbooks("Template.xlsm").Worksheets("Baking goods").Range("A1").PasteSpecial Paste:=xlPasteFormats
    Workbooks("Master.xlsm").Worksheets("Baking goods").Range("A1:BA500").Copy
    Workbooks("Template.xlsm").Worksheets("Baking goods").Range("A1").PasteSpecial Paste:=xlPasteValues
  
    Workbooks("Master.xlsm").Worksheets("Knit goods").Range("A1:BG500").Copy
    Workbooks("Template.xlsm").Worksheets("Knit goods").Range("A1").PasteSpecial Paste:=xlPasteFormats
    Workbooks("Master.xlsm").Worksheets("Knit goods").Range("A1:BG500").Copy
    Workbooks("Template.xlsm").Worksheets("Knit goods").Range("A1").PasteSpecial Paste:=xlPasteValues
  
End Sub
 
Last edited by a moderator:

vletm

Excel Ninja
Otawata
Do You still have an idea to get only in a new workbook ( = one new workbook )?
or what matters number of sellers?
Could You send a sample of expected result?
 

Otawata

New Member
Otawata
Do You still have an idea to get only in a new workbook ( = one new workbook )?
or what matters number of sellers?
Could You send a sample of expected result?
My goal is to generate and save one new workbook per seller containing only the sheets of using a loop.

I haven’t been able to figure out how to do this (or how to maintain column and row sizes if they aren’t preformatted in the destination).

thanks for your response and any help you may provide
 

vletm

Excel Ninja
Otawata
Your I have a master workbook full of formulas and reference worksheets.
If Your master workbook is Book2.xlsx - then where are those formulas or references?

My goal is to generate and save one new workbook per seller containing only the sheets of using a loop.
Based You file - is Your goal to get three files, which are same? Yes / No?

Seems You couldn't send a sample expected result...
 

Otawata

New Member
Yes, I had to take out all my formulas to upload the existing example because the file was too big otherwise. Using the data that’s in the example, the output files will look the same for each seller.

In reality, each seller’s data will load on all tabs when they are selected from the drop-down in the “totals” tab.

I don’t have access to upload an example output file right now.

thank you!
 

vletm

Excel Ninja
Otawata
Because Your given file is 'different' than Your normal used file,
I had to guess some features as well as I cannot verify do this work as You've thought.
Wasn't Your given file almost like Your expected result ... which You cannot send here?
Because only You know Your original file,
You can run something like Your export with this file.
Open this > press the [ button ] > Select Your normal used file (which have to have same layout as Your given file) > You could get Your files.
 

Attachments

Otawata

New Member
Hi vletm,

Thanks so much for your work so far. I can open the doc you sent and I have tried to use it with the original file I sent, but nothing occurs and I can't figure out why. Since a large part of this is no doubt on me not providing enough information in the example, I went back and redid the Master, attached below. I also attached an example output file for the seller "Krista". Please let me know if you have any questions and if this helps. Thank you, again, for your efforts and assistance.
 

Attachments

vletm

Excel Ninja
Otawata
To which doc do You refer?

Did You read my instructions?
Open this
> press the [ button ]
> Select Your normal used file (
which have to have same layout as Your given file)
> You could get Your files.


You seems to send different layout file (Master) this time ... and that won't work.
Screenshot 2021-12-13 at 19.27.18.png
Why did You change Your files layout or sent something different with Your #1 reply?
Is Your 'Master' every time different or how?
 

Otawata

New Member
Yes, I followed the instructions using the file you sent with the original attachment (Otawata_Book2). I couldn't get the files when I followed the instructions. Nothing happened.


I also had to make updates to the Master file layout, which I included (Master). I also added back the formulas and an example output file that would accompany it.
 

vletm

Excel Ninja
Otawata
I sent file which name was/is Otawata.xlsb ... You didn't confirm to use same file ...
Did You save that my sent file (Otawata.xlsb) somewhere?
... to same folder where is Your used file as Your sent Book2.xlsx .. Yes/No?
Did You open Otawata.xlsb file?
Did You press that button?
Did You select Your sent Book2.xlsx-file?
... or someone other file, which have same layout?
Did You noticed that anything happen?

You wrote that You modified Your Master.xlsx -file
---- then that won't have same layouts and that won't work.

You skipped my the last line question.

Codes do just as those should be coded ... those won't guess.
Questions needs answers.
 

Otawata

New Member
sent file which name was/is Otawata.xlsb ... You didn't confirm to use same file ...
Did You save that my sent file (Otawata.xlsb) somewhere? YES
... to same folder where is Your used file as Your sent Book2.xlsx .. Yes/No? YES
Did You open Otawata.xlsb file? YES
Did You press that button? YES
Did You select Your sent Book2.xlsx-file? YES
... or someone other file, which have same layout? n/a
Did You noticed that anything happen? Nothing happened

You wrote that You modified Your Master.xlsx -file
---- then that won't have same layouts and that won't work. I know, and I wasn't trying to use it with the existing code you sent. I thought it may help.

You skipped my the last line question. Sorry, the Master will not change again

Codes do just as those should be coded ... those won't guess.
Questions needs answers.
 

vletm

Excel Ninja
Next sample ...
With this sample You could test and see ... what is going on... or not?
#1 Save this file to same folder with Master.xlsx-file or any same kind of file
#2 Open this file
#3 If needed - Run with "test"
... select cell A1
... there will come some messages, which shows - how do this work there?
... here this works.
#4 Press that button
#5 Select Your Master-file or any same kind of file
#6 You should get three files
 

Attachments

Otawata

New Member
#1 Save this file to same folder with Master.xlsx-file or any same kind of file DONE
#2 Open this file DONE
#3 If needed - Run with "test" I don't know how to do this
... select cell A1
... there will come some messages, which shows - how do this work there?
... here this works.
#4 Press that button DONE
#5 Select Your Master-file or any same kind of file DONE
#6 You should get three files A number of dialog boxes appear that I have to click through and then the file closes with no output files generated
 

vletm

Excel Ninja
Otawata
Testing is much more than clicking...
Because I cannot see those Your results of messages - You should read those.
Did any "Err.number" -message boxes any values except 0 (zero)?
 

vletm

Excel Ninja
Otawata
Did You have challenges to do that test?
Do You use Windows or Mac?
Try to test this sample - it will have only one test value, which You should record - if there are any challenges.
( Use same instructions as with #13 reply )
 

Attachments

Top