VBA Match Cell Range to Worksheet Name and copy/paste & create pdf

[mohanprabhus]

I'm trying to write a macro that matches an F Column cell in the with the sheet name and then pulls data from Active sheet and then copies to the exact sheet Name where I want to insert the cell value & create pdf based on the sheet
SHEETNAMEs mentioned below
SamplePDF1
PDFfile2
Excel3
PDF4
When i double click on the SamplePDF1 automatically the cells enter in particular Sheet Name (SamplePDF1) in Given Cell value & data Create a PDF.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PDFNumber[/TD]
[TD]Client Name & Address[/TD]
[TD]Particular[/TD]
[TD]Total[/TD]
[TD]Amount[/TD]
[TD]Tax[/TD]
[TD]PDF Template[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]India, 1234, Karnataka, India[/TD]
[TD]Linkedin[/TD]
[TD]1000[/TD]
[TD]847[/TD]
[TD]152[/TD]
[TD]SamplePDF1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Google, 4321, Karnataka, India[/TD]
[TD]Yahoo[/TD]
[TD]500[/TD]
[TD]423[/TD]
[TD]76[/TD]
[TD]PDFfile2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Exce1, 12, Karnataka, India[/TD]
[TD]Beetel[/TD]
[TD]2000[/TD]
[TD]1694[/TD]
[TD]305[/TD]
[TD]Excel3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Excelforum.com, 312, India[/TD]
[TD]Aptonia[/TD]
[TD]1560[/TD]
[TD]1322[/TD]
[TD]238[/TD]
[TD]PDF4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Momentman]

This should give you an idea

The code assumes that the Sheet names are in column F, say F2 to F100 for example
it also copies from that row (column A to column F) to the appropriate Worksheet but copies to cell A1(you can change this) and prints to PDF

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("F2:F100")) Is Nothing Then
        Range(Cells(Target.Row, 1), Cells(Target.Row, 6)).Copy Worksheets(Target.Value).Range("A1")
        Range("A1").Select
        Worksheets(Target.Value).ExportAsFixedFormat Type:=xlTypePDF, Filename:=Target.Value, Openafterpublish:=True
    End If
End Sub

 

[mohanprabhus]

Thank you so much, It works!. Instead of Copy complete row & take a pdf, Based on Different Sheet Format, I would like to send to the respective cell. Would you please help me

 

[mohanprabhus]

Dear Sir,


Sorry, Please find the below mentioned details,


1.) I need a file name as Eg: SamplePDF1_Linkedin_28_12_2018_1.pdf (or)
PDFfile2_28_12_2018_2.pdf (or)
Excel3_28_12_2018_3.pdf (or)
PDF4_28_12_2018_4.pdf
(SamplePDF1 is in column PDF Template, Linkedin is column Particular & today date whenever we generate that date i.e., =TODAY() & 1 is column PDFNumber)


2.) I need a Cell Value like mentioned below Examples


a.)
Linkedin(Particular) Field should be copied in Sheet SamplePDF1 in Cell B12,
1000 (Total) Field should be copied in Sheet SamplePDF1 in Cell D12
152(Tax) Field should be copied in Sheet SamplePDF1 in Cell D13
India, 1234, Karnataka, India(Client Name & Address) Field should be copied in Sheet SamplePDF1 in Cell C20


b.)
Beetel(Particular) Field should be copied in Sheet Excel3 in Cell C12,
2000(Total) Field should be copied in Sheet Excel3 in Cell E12
305(Tax) Field should be copied in Sheet Excel3 in Cell F13
Exce1, 12, Karnataka, India(Client Name & Address) Field should be copied in Sheet Excel3 in Cell A20


3.) File Path Location & no Creation While Already Exists Until we Overwrite Alert


c.) Saving Path Location C:\Users\XYZ\Documents\PDF\
d.) Same file name should not create
example: 2 Time Same File Should not create SamplePDF1_Linkedin_28_12_2018.pdf


Regards,
mohanprabhus

Dear Sir,




I would request you, please help has mentioned below
Pdf File Name while exporting with SheetName, Particular Sheet Cell Value, date, month & year
Copy cells to Respective format Cell Field based on Sheet Format
File Path, no duplicate creation or file overlapping
Auto increment PDF number


Regards,
mohanprabhus

You would have to be a little more specific--------


Particular sheet cell value, date month year.....i am not sure i get this
Copy cell to respective format cell field based on sheet format? can you give an example

Thank you so much, It works!. Instead of Copy complete row & take a pdf, Based on Different Sheet Format, I would like to send to the respective cell. Would you please help me