+ Reply to Thread
Results 1 to 7 of 7

copy one row to another sheet then create pdf and excel Value only for this sheet

  1. #1
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,054

    copy one row to another sheet then create pdf and excel Value only for this sheet

    Good even all
    can help how can did the following action


    i have workbook have twoo sheet
    one is sheet1 that have around 3200 row
    i need to copy value from sheet1 to Template sheet in corresponign cell as in my attahced example
    then create pdf and excel file from template sheet with name of value from A9 in template sheet



    then loop this code for all avalible row



    i have notes if can make code add value in sheet1 in column N for row created
    so if probelm during code execusion happen , and start it again it will look for last row have value in column N and start from next
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,054

    Re: copy one row to another sheet then create pdf and excel Value only for this sheet

    i find one solution by create pdf file have form and use virtual pdf print, but its so slow, os pleaase can advice any other option

  3. #3
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: copy one row to another sheet then create pdf and excel Value only for this sheet

    Not sure if the code below give the result as you expected
    Please Login or Register  to view this content.
    The code will ask the user to type what number he wants to have the data to fill the template sheet
    Assumed that the user type number 101, then the code find that number in column A sheet Sheet1,
    then the code make some variables with a value based on the found cell (in this case, 101 is in cell A3 Sheet1)

    Next,
    the code fill sheet Template with those variables value
    copy Sheet Template to a new workbook
    save the new workbook as pdf and xlsx file to directory D:/test/ (drive D, folder test) with a name from cell A9 value of Sheet Template
    close the active workbook
    So this code create the new file just one at a time.

    I'm not sure if what you mean is to create the new file just one at a time,
    or you want the code create the new files as many as the number exist in column A of Sheet1.
    And if you want to create the two new files (based on sheet Template) from all the existing number in column A sheet 1,
    I think it will take times for the code to run.

    Below the code loop to each existing number in column A sheet 1 (starting from cell A2).
    Please Login or Register  to view this content.
    Another way is to put a vlookup formula on the sheet Template,
    based on cell A9 (the number) of sheet Template.
    So the code will just fill number value to cell A9 (sheet Template) based on each number exist in column A (sheet Sheet1),
    then the code save it as pdf and xlsx file.
    No need a variable here, but you need to write the vlookup formula in each needed cell of sheet Template first.
    Last edited by karmapala; 09-17-2021 at 03:06 AM.

  4. #4
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,054

    Re: copy one row to another sheet then create pdf and excel Value only for this sheet

    Dear karmapala
    thank you so much for your perfect solution
    i have two question
    how can change this line to open dialog to give ability to select folder

    Please Login or Register  to view this content.
    second the following line give me error as attahced in photo
    Please Login or Register  to view this content.
    can add line to check if this file present show message to replace or not ?



    there is another note

    when i select with second code number for example 732 its give me another line that conatin 732 (give me value related to 1732) so how can modifiy it to excat value
    Attached Images Attached Images
    Last edited by mazan2010; 09-18-2021 at 04:27 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: copy one row to another sheet then create pdf and excel Value only for this sheet

    Mazan,
    Quote Originally Posted by mazan2010 View Post
    Dear karmapala
    how can change this line to open dialog to give ability to select folder
    Please have a look the workbook I attached.
    It will ask the user to choose the directory.

    second the following line give me error as attahced in photo
    Althought in my side there is no warning like that,
    anyway I also updated the code in the attachment to ignore the warning.
    I hope it will not give you the warning again.


    can add line to check if this file present show message to replace or not ?
    The code in the attached workbook will tell the user if file already exist with option YES or NO.
    If the user click YES, it will replace the existing file
    If the user click NO, it will skip the saving process


    when i select with second code number for example 732
    its give me another line that conatin 732 (give me value related to 1732) so how can modifiy it to excat value
    Also in the attached workbook, I've modified the code to find the exact number, by adding "lookat:=xlwhole".
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,054

    Re: copy one row to another sheet then create pdf and excel Value only for this sheet

    Dear karmapala
    thank you so much for your reply and your support

    i have question about PDF file created by this way and pdf created using adobe , is there is any difference in between

    as i alreay make around 5000 pdf files using code but when try to sumbit SAP system its not accept
    althought i try to make one word file and save as pdf , this way it work , if any one have any explanation for this issue !!!!?

  7. #7
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: copy one row to another sheet then create pdf and excel Value only for this sheet

    Quote Originally Posted by mazan2010 View Post
    i have question about PDF file created by this way and pdf created using adobe , is there is any difference in between
    I'm very sorry as I don't know the answer, mazan.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 9
    Last Post: 03-28-2017, 08:30 PM
  2. excel macro - how to create copy one sheet to another sheet alternate row
    By jshgaonkar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2013, 09:12 AM
  3. Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1
    By leog1969 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-03-2013, 05:58 PM
  4. Copy Sheet / Create & Name New Sheet / Insert Before a Sheet / Paste Data
    By thinkspac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2012, 02:27 PM
  5. Macro to Look for Sheet, create if none, sort items from master & copy to new sheet.
    By ali.whitaker in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-25-2012, 12:58 PM
  6. Filter, copy filtered data into another sheet, create a file of that sheet..
    By titushanke in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-17-2012, 02:26 PM
  7. Copy Sheet & Create New Monthly Sheet From Present Sheet
    By unley in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-16-2010, 12:00 PM

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