vba moving last worksheet to the front of the book

[dgavilanes]

Hi All,

Looking to move last worksheet to the front of the book on multiple workbooks and save them all as a PDF's

Any help would be greatly appreciated


Regards, Dennis

 

[Luke M]

Individual bits to learn/know:

1. How to loop through files in a folder
2. How to move worksheets in a workbook
3. How to make pdfs

1 & 3 can both be found on Chandoo with a quick search, or a Google search for more results. #2 is a single code line:

Worksheets(Worksheets.Count).move before:=worksheets(1)

 

[dgavilanes]

Thanks for the code for line 2.
I am just a novice on vba, This is what I would like to succeed using a macro.

1.) rename a tab on several worksheets example tab6 to tab1
2.) move tab6 to tab1 position
3.) where do I enter the directory within the vba code so it can run it from temp_master worksheet.
4.) if I have several worksheets within the directory do I repeat same code for each.

Worksheets(Worksheets.Count).move before:=worksheets(1)

directory=Dennis/My Documents/testing/test

Please be patience, I'm not an expert that is why I, here trying to learn

Thanks

Dennis

 

[dgavilanes]

This what I have so far

Sub action()

Application.ActiveWorkbook.Path
ActiveSheet.Name = ActiveSheet.Range("A1").Value
Worksheets(Worksheets.Count).Move before:=Worksheets(1)


End Sub

Couple problems. I'm getting a compile error on .Path and every time I run it.... And once the last tab moved to the first tab, how do I tell the code that the job is completed.


C:\Users\Dennis\Documents\testing is there a way I can add this directory to the path?

Thanks

Dennis

 

[Luke M]

Hi Dennis,
Apologies if my first reply came off harsh, that was not my intent. I was trying to encourage you to be bold and look around, see what you could learn. VB is daunting at first, but people can gain confidence as they play around with it and learn more.

In your posted code, the error is because the code doesn't say what to do with the Path name. In this case, Path returns a string, but we don't assign that string to any VB variable. Instead, we could do one of these:

Dim myPath as String 'Declare our variable

'Assign the variable a value one of 2 ways
myPath = ThisWorkbook.Path
myPath = "C:\Users\Dennis\Documents\testing\" 'Don't forget ending slash

To save the entire workbook as a PDF, the code could look something like this:

Sub MakePDF()
Dim myPath as String
Dim myName as String
'Where do we save the pdf?
myPath = ThisWorkbook.Path
'What do we call the pdf?
myName = "My Pdf " & Format(Date,"yyyy.mm.dd") 'Attach date stamp to name

'Export as pdf
ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    myPath & myName & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
End Sub