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

mohanprabhus

New Member
Joined
Dec 7, 2016
Messages
14
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.
PDFNumberClient Name & AddressParticularTotalAmountTaxPDF Template
1India, 1234, Karnataka, IndiaLinkedin1000847152SamplePDF1
2Google, 4321, Karnataka, IndiaYahoo50042376PDFfile2
3Exce1, 12, Karnataka, IndiaBeetel20001694305Excel3
4Excelforum.com, 312, IndiaAptonia15601322238PDF4

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
3,963
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

Code:
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

New Member
Joined
Dec 7, 2016
Messages
14
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

New Member
Joined
Dec 7, 2016
Messages
14
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


Momentman said:
mohanprabhus said:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,090,549
Messages
5,415,219
Members
403,572
Latest member
ballardgirl

This Week's Hot Topics

Top