Posts by VBAndrey

VBAndrey · Oct 31st 2019

Hi Guys,

I'm trying to recreate tabs for the same layout but different name.

The names are in cells i46:i92 in the current sheet.

Current tab name Marine.

Also I have a question. Could macro help me solve another problem when Im copying over the tab the Microsoft Excel window keep appearing asking

The name already exists. Click Yes to use that version of the name or click No to rename version of youre moving or copying.

Any way to solve this problem in the macro too ? If not I will have to use a stapler and leave my pc idle for a couple minutes

Thanks !

VBAndrey · Aug 9th 2019

Is there no way to do it based on the format set out in the file though ?

Im pretty sure there must be ?

I have used sum ifs to sum the rows

however I am struggling to be able to change the column based on the name in the cell K5.

VBAndrey · Aug 9th 2019

Attached

VBAndrey · Aug 9th 2019

Hi Guys,

I have a table :
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 64"]Practice[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"]Marine[/TD]

[/tr]

[tr]

[td]

Revenue

[/td]

[td][/td]

[TD="align: right"]6[/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"]0[/TD]

[/tr]

[tr]

[TD="colspan: 2"]Fixed Cost[/TD]
[TD="align: right"] [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 64, align: right"]861[/TD]

[/tr]

[/TABLE]
[/TD]

[/tr]

[/TABLE]

Data sheet below :

[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 64"] [/TD]
[TD="width: 64"]Marine[/TD]
[TD="width: 64"]P&C[/TD]

[/tr]

[tr]

[td]

Revenue

[/td]

[td]

1.00

[/td]

[td][/td]

[/tr]

[tr]

[td]

Revenue

[/td]

[td]

2.00

[/td]

[td]

2.00

[/td]

[/tr]

[tr]

[td]

Revenue

[/td]

[td]

3.00

[/td]

[td]

3.00

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

Fixed Cost

[/td]

[td]

123.00

[/td]

[td][/td]

[/tr]

[tr]

[td]

Fixed Cost

[/td]

[td]

123.00

[/td]

[td][/td]

[/tr]

[tr]

[td]

Fixed Cost

[/td]

[td]

123.00

[/td]

[td]

123.00

[/td]

[/tr]

[tr]

[td]

Fixed Cost

[/td]

[td]

123.00

[/td]

[td]

123.00

[/td]

[/tr]

[tr]

[td]

Fixed Cost

[/td]

[td]

123.00

[/td]

[td]

123.00

[/td]

[/tr]

[tr]

[td]

Fixed Cost

[/td]

[td]

123.00

[/td]

[td]

123.00

[/td]

[/tr]

[tr]

[td]

Fixed Cost

[/td]

[td]

123.00

[/td]

[td]

123.00

[/td]

[/tr]

[/TABLE]

I want it to auto change the total numbers numbers if I type into the practice row P&C.

Thanks a lot.

Andrey

VBAndrey · Aug 8th 2019

Roy,

Yes, but this time im trying to name it as "Flash" and I want to use a month but the reference to that is sheet 1 cell D10.

VBAndrey · Aug 8th 2019

Hi guys, Trying to add a month but as per value in Sheet1 Cell D10 but no luck =( Could anyone help amend the code I believe in bold ...

Code

Sub PDFActiveSheet()


Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler


Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "dd.mm.yy")


'get active workbook folder, if saved
strPath = "G:\Board\2019\"


'replace spaces and periods in sheet name
strName = "Flash"


'create default name for savng file
strFile = strName & " " & strTime & ".pdf"
strPathFile = strPath & strFile


'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")


'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    'confirmation message with file info
    MsgBox "PDF file has been created"
    
End If


exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

Display More

[/CODE]

VBAndrey · Aug 8th 2019

Hi guys, Trying to add a month but as per value in Sheet1 Cell D10 but no luck =( Could anyone help amend the code I believe in bold ... sub PDFActiveSheet() Dim wsA As Worksheet Dim wbA As Workbook Dim strTime As String Dim strName As String Dim strPath As String Dim strFile As String Dim val As String Dim strPathFile As String Dim myFile As Variant On Error GoTo errHandler Set wbA = ActiveWorkbook Set wsA = ActiveSheet strTime = Format(Now(), "yyyy") 'get active workbook folder, if saved strPath = "S:\Dufus\2019" 'replace spaces and periods in sheet name strName = "Board Pack" 'create default name for savng file strFile = strName & " " & strTime & ".pdf" strPathFile = strPath & strFile 'use can enter name and ' select folder for file myFile = Application.GetSaveAsFilename _ (InitialFileName:=strPathFile, _ FileFilter:="PDF Files (*.pdf), *.pdf", _ Title:="Select Folder and FileName to save") 'export to PDF if a folder was selected If myFile <> "False" Then wsA.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=myFile, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False 'confirmation message with file info MsgBox "PDF file has been created" End If exitHandler: Exit Sub errHandler: MsgBox "Could not create PDF file" Resume exitHandler End Sub

VBAndrey · Aug 7th 2019

Hi Guys,

Very new to VBA.

Could you help me and point how I change the name of the file to Sales

and the path where it saves it to G:\Board\2019\Month End

THANKS A TONN!

Code

sub PDFActiveSheet()


Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler


Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "ddmmyyyy")


'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & ""




'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")


'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile


'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")


'export to PDF if a folder was selected
If myFile <> "False" Then
wsA.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& myFile
End If


exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub

Display More

VBAndrey · Aug 7th 2019

Hi Guys,

Very new to VBA.

Could you help me and point how I change the name of the file to Sales

and the path where it saves it to G:\Board\2019\Month End

THANKS A TONN!

Code

sub PDFActiveSheet()


Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler


Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "ddmmyyyy")


'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & ""




'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")


'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile


'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")


'export to PDF if a folder was selected
If myFile <> "False" Then
wsA.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& myFile
End If


exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub

Display More

Posts by VBAndrey

Copying Current Tab and Renaming Based on Cell Values

How to sum the rows but based on different columns ?

How to sum the rows but based on different columns ?

How to sum the rows but based on different columns ?

Adding a cell value to the file name

Adding a cell value to the file name

Adding a cell value to the file name

Change the name and location of the file

Change the name and location of the file.