Code skipping

b0t · Mar 28th 2018

Hi There,

I'm having trouble with a piece of code that i've used in the past but for some reason is now skipping without performing the intended formatting. The idea is to open each file in a specified folder and perform a formatting function before saving it again. Right after the Do While command, it just skips to the bottom and I can't understand why.

Code

Dim FolderPath As String
Dim FileName As String


FolderPath = "C:\Users\jjones\Desktop\Macros\Testing\Division Files"
FileName = Dir(FolderPath & "*.xls*")
Do While FileName <> ""


Workbooks.Open (FolderPath & FileName), UpdateLinks:=0
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
 With Workbook.Worksheets(2)
 Range("E19").Select
    ActiveWindow.FreezePanes = True
    Rows("18:18").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$18:$18"
        .PrintTitleColumns = ""
    End With


Workbooks(FileName).Close SaveChanges:=True
FileName = Dir()
Loop

Display More

Thanks for your help!!!

chirayuw · Mar 28th 2018

You're missing an End With. To be honest I'm surprised your code didn't give you a debug error. Is your page setup for current sheet & not worksheet(2)? cos your code seems to look that way. If t is for worksheet2 then change "Activesheet.PageSetup" to ".PageSetup"

Code

'Define Variables
Dim FolderPath As String
Dim FileName As String


'Set Variables
FolderPath = "C:\Users\jjones\Desktop\Macros\Testing\Division Files"
FileName = Dir(FolderPath & "*.xls*")


'Loop Start
Do While FileName <> ""


Workbooks.Open (FolderPath & FileName), UpdateLinks:=0


If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
With Workbook.Worksheets(2)
    Range("E19").Select
    ActiveWindow.FreezePanes = True
    Rows("18:18").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$18:$18"
        .PrintTitleColumns = ""
    End With
End With
   
    Workbooks(FileName).Close SaveChanges:=True
    FileName = Dir()


Loop

Display More

b0t · Mar 28th 2018

Hi Chirayuw!

Thanks for the quick feedback. I tried running the code both with the activesheet declaration and without. However I continue to get the same problem. Also, like you pointed out, no error message. A bit of a head scratcher for sure!

chirayuw · Mar 28th 2018

Is division files the folder name or the file name? if its the folder name then add a \ at the end of folderpath

b0t · Mar 28th 2018

Yup...that fixed the jumping. Getting an error message with:

Code

With Workbook.Worksheets(2)

whether i use the activesheet declaration or just .pagesetup. Once I remove it...the code runs alright. The files contain a hidden first sheet which is why I wanted to refer to worksheet (2) but i'm wondering if that is necessary.

Thanks a million!

chirayuw · Mar 28th 2018

depends on whether the first visible sheet is always selected in the files. if it is then u don't need it. if it isn't then you need to select it first.

secondly that code doesn't work cos u gotta use activeworkbook not workbook.

another thing to watch out for is the Worksheets(2) does not necessarily mean the 2nd sheet in the file, cos technically you can rename any sheet to whatever u want & move em around in the file but 2 will refer to what it shows as 2nd sheet in VBA module. so technically any sheet in the file could be sheet 2 in vba module

so to select first visible sheet - refer to below thread, 3rd post

https://www.mrexcel.com/forum/…rmine-1st-visible-ws.html

b0t · Mar 29th 2018

:eureka::eureka::eureka: Thanks man! Great info!

Code skipping

Participate now!

Share