OK. I think I finally got the PAD working correctly to generate my .csv files.
I click OK and it continues. Next I get below.
I understand this. It is trying to put it in my Documents folder and I already ran it once, thus producing the file before.
However, I would like it to be put into a specific folder. ???
I click Yes.
Then it looks like it goes to the bottom of the file list and gives:
I click Debug, VBA window opens and I see:
I changed a few things in code. Hopefully doing it correctly. You had "formatID" defined but had "formattab" in code. I changed code to formatID.
The Balance sheet did format correctly. However, it was the only sheet to work.
I have a "formatting Worksheet" for each of my nine properties. I thought if I could get it to work for the first, it will work for the others.
Also, there is more things to add to macro. I have 6 sheets per property.Balance Sheet, Income Statement, Income Statement vs Budget, Income Statement MTM , Statement of cash flows, and GLTB.
On the three Income Statement sheets, I need to insert 5 rows (7-11) and copy 5 rows (7-11) from the formatting sheets. (info on these added rows changes each month dependent on another workbook).
Complicating things further, the MTM I copy A7-A10 and then C7-M10 (drop off oldest month), then copy L7-L10 (old M7-M10) to M7 so newest month number can be put in.
To complicate even further, 4 of the properties the above works (7-11, etc), but 5 of the properties download differently. Instead of 7-11, I must insert, copy etc from 9-13.
Complex? Yes. Incredible amount of time saving, though, once I can get it work.
Below is my VBA as it stands now:
Sub FormattingWorkbooks()
'
' FormattingWorkbooks Macro
' open each file (listed in Files tab by PAD), identify type of workbook, copy formatting from proper tab, save and close workbook
'
'
Sheets("Files").Select
'get last row to limit the loop run
Dim LR As Long
Range("A1").Select
Selection.End(xlDown).Select
LR = Selection.Row
'dim all variables before loop
Dim filepathandname As String 'the file path and name for the current file in the loop index
Dim formatID As String 'the text identified in the file name used to decide which formatting tab to use
Dim formattab As String 'the formatting tab from the macro workbook to be used to format the current file
Dim AWB As Workbook 'active workbook from filename
Dim i As Integer 'the index for the loop run
i = 1 'the loop will start on A1
'loop for each filename in column A of files (A1 to A & LR)
Do
Sheets("Files").Select
filepathandname = Range("A" & i).Value
Workbooks.Open Filename:=filepathandname
'get active workbook name for later window switch after copying cells from macro workbook
Set AWB = ActiveWorkbook
'get the Case variable from A2 of the opened file
formatID = Range("B2").Value
formatID = Trim(formatID) ' removes beginnning or ending spaces off the text
'select which formatting tab we are going to use based on the Case variable
Select Case formatID
Case "Balance Sheet"
formattab = "Balance Sheet"
Case "INCOME STATEMENT"
formattab = "Inc Stmnt"
Case "INCOME STATEMENT vs Budget"
formattab = "Inc Stmnt vs Budget"
Case "INCOME STATEMENT MTM"
formattab = "Inc Stmnt MTM"
Case "STATEMENT OF CASHFLOWS - IB"
formattab = "St Cash Flows"
Case "Account Number"
formattab = "GLTB"
Case Else
MsgBox ("Error - Format not found")
End Select
'copy formatting from format tab
Windows("Copy formatting.xlsm").Activate
Sheets(formattab).Select
Cells.Select
Selection.Copy
Windows(AWB.Name).Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Application.CutCopyMode = False
ActiveWorkbook.SaveAs , FileFormat:=51
ActiveWindow.Close
Windows("Copy formatting.xlsm").Activate
i = i + 1 'increase i to get the next filename until we reach LR
Loop Until i > LR
End Sub
Thank you,
David
