Extract the name of a column (using VBA) from excel

[Kitty]

Hi

I have a question re: VBA macros, and I'm not very good at them. Anyway, it is one worksheet, with vertical page breaks and each column has a heading. So cell D1 is Person D, cell E1 is Person E etc to column AU. I want to use this column name to name my pdf docs. One pdf doc per person. Anyway, I have all of the code, EXCEPT how to get the column name out of the excel column. I've tried about 40 codes but i can't work it. e.g ReportPersonD.pdf ReportPersonE.pdf


ColumnName = sht.Range("B" & RwStart).Value
ReportName = "Report_" & ColumnName & "_" & n & ".pdf"

I've tried various ranges but obviously not using the correct formula. I did search the forum but maybe I didn't search on the correct terms? Thanks

 

[vletm]

Kitty
You could continue from this...

For c = 4 to 47
    ReportName = "Report_" & ActiveSheets.Cells(1,c) & ".pdf"
Next c

 

[Kitty]

Thanks, I'll give that a try

 

[Kitty]

Sorry I'm unsure what I am doing wrong but it is only picking up the last name of the column. I've attached a test file and my full macro.

Any thoughts?

Sub exportcolumnsv5test()

Set sht = ActiveSheet

ProfitDis = "C:\keep\"
NrPages = sht.VPageBreaks.Count + 1
For p = 1 To NrPages
    If p = 1 Then
        RwStart = 1
    Else
        RwStart = sht.VPageBreaks(p - 1).location.Row
    End If
    For c = 1 To 5
    ReportName = "ProfitDis_" & ActiveSheet.Cells(1, c) & "_" & p & ".pdf"

Next c
    sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ProfitDis & ReportName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=p, to:=p, OpenAfterPublish:=False

Next

Set sht = Nothing


End Sub

 

[vletm]

Kitty
1) In Your original message Names were from D1 to AU1 and
in Your file from A1 to ... hmm ... of course, The Cure is a band's name.
2) You have there two inside loops ..

ProfitDis = "C:\keep\"
for p = 1 to NrPages
    if ...
    endif
    for c = 1 to 5
        ReportName = "ProfitDis_" & ActiveSheet.Cells(1, c) & "_" & p & ".pdf"
    next c
'    A) above ALWAYS gives cell E1 Name!
'    B) my sample give as in Your original range from 4 to 47 !
'    create PDF with Filename:= ProfitDis & ReportName
next

Did You get any thoughts?

The code works always
... as it has written!
But, is it written
as we would like it to work?​

 

[Kitty]

Il

Kitty
1) In Your original message Names were from D1 to AU1 and
in Your file from A1 to ... hmm ... of course, The Cure is a band's name.
2) You have there two inside loops ..

ProfitDis = "C:\keep\"
for p = 1 to NrPages
    if ...
    endif
    for c = 1 to 5
        ReportName = "ProfitDis_" & ActiveSheet.Cells(1, c) & "_" & p & ".pdf"
    next c
'    A) above ALWAYS gives cell E1 Name!
'    B) my sample give as in Your original range from 4 to 47 !
'    create PDF with Filename:= ProfitDis & ReportName
next

Did You get any thoughts?

The code works always
... as it has written!
But, is it written
as we would like it to work?​

l try it tomorrow morning. Yes my original ref were different but I didn’t want to send a file with live data so I made up a test one. The principle is the same

 

[Kitty]

So even though I have given you a different file and different references, the general principle should be the same.

What in my coding is making it always point to cell E1? As I said, I'm not very experienced in macros and I'm trying to reduce a two hour job to a 10 minute one. Thanks

 

[vletm]

Kitty
As I tried to answer...
Find text A) below

ProfitDis = "C:\keep\"
for p = 1 to NrPages
   if ...
    endif
   for c = 1 to 5
        ReportName = "ProfitDis_" & ActiveSheet.Cells(1, c) & "_" & p & ".pdf"
   next c
'    A) above ALWAYS gives cell E1 Name!
'    B) my sample give as in Your original range from 4 to 47 !
'    create PDF with Filename:= ProfitDis & ReportNamenext

As I've tried to ask, where are Your 'ReportNames'?
If still not help, then make 'better' sample file and upload here.

 

[Kitty]

Ok, I'm clearly missing something. Thanks for trying. There is really not a huge difference between my sample file (other than more names and a few more numbers) and the real file. The principle remains the same.
I tried the 4-47 but I always got the name of the last cell and I don't understand why.

Thanks

 

[vletm]

Kitty
follow P ...
' P = 1
for P = 1 to NrPages

after that, You have loop
which give value for ReportName
from A1 to E1
It always given E1's value

Next You'll create that PDF
... and next You'll go back to 'for P -sentence' with value 2 and so on.

There no need to be even minor mistake that the result would be not wanted.
It's much easier to get not wanted result than 100% wanted result.

 

[Kitty]

I've give it a try in in the morning. thanks