Firstly a massive thank you for the help from this forum,
I've been handed the following today as well, which is a little out of my zone, but I am trying to learn it.
I have the following code in a sheet
I've been handed the following today as well, which is a little out of my zone, but I am trying to learn it.
I have the following code in a sheet
Code:
[INDENT=2]Option Explicit</SPAN>
Sub copySheet()</SPAN>
Dim ChangeRequestBooks() As Workbook</SPAN>
Application.ScreenUpdating = False</SPAN>
ChangeRequestBooks = openFiles() </SPAN>
If ChangeRequestBooks(0) Is Nothing Then</SPAN>
MsgBox "Update Cancelled"</SPAN>
Else</SPAN>
processFiles ChangeRequestBooks</SPAN>
MsgBox "Update Complete"</SPAN>
End If</SPAN>
Application.ScreenUpdating = True</SPAN>
End Sub</SPAN>
' Prompts the user for a list of files and returns a referece (in an array) to all the files.</SPAN>
' Opens all the files ready to be processed.</SPAN>
Function openFiles() As Workbook()</SPAN>
Dim Wb() As Workbook</SPAN>
Dim i As Long, c As Long</SPAN>
Dim FilesToOpen As Variant</SPAN>
' tell the user what we want them to do</SPAN>
MsgBox "Select workbook(s) to copy.", vbApplicationModal</SPAN>
' note that if you want to have the open dialogue start in a specific folder then</SPAN>
' use ChDrive and ChDir (uncoment following section and update it to the correct path)</SPAN>
'ChDrive "H:" ' note that if used on multiple systems the drive letter isn't always the same</SPAN>
'ChDir "H:\1. Finance Shared Services\FINANCE DATA & MI CHANGE\Financial Risk MI Change\Change Request Forms Recived" ' is the folder spelt wrong? Recived or Received?</SPAN>
' prompt the user with an open dialog</SPAN>
FilesToOpen = Application.GetOpenFilename(FileFilter:="Microsoft Excel (*.xl*),*.xl*", Title:="Please select all change request files required", MultiSelect:=True)</SPAN>
If Not IsArray(FilesToOpen) Then</SPAN>
ReDim openFiles(0) </SPAN>
Exit Function </SPAN>
End If</SPAN>
For i = LBound(FilesToOpen) To UBound(FilesToOpen)</SPAN>
ReDim Preserve Wb(c) ' need to create a space in the array for the file we're about to open</SPAN>
Set Wb(c) = Workbooks.Open(FileName:=FilesToOpen(i), UpdateLinks:=False, ReadOnly:=True)</SPAN>
c = c + 1</SPAN>
Next i
[INDENT=2]openFiles = Wb</SPAN>
End Function</SPAN>
Private Sub processFiles(Wb() As Workbook)</SPAN>
Dim i As Long</SPAN>
Dim ws As Worksheet</SPAN>
Dim SourceRange As range, TargetRange As range</SPAN>
Set ws = ThisWorkbook.Worksheets("SummarySheet")</SPAN>
Set TargetRange = ws.Cells(ws.Cells.Rows.Count, 2)</SPAN>
Set TargetRange = TargetRange.End(xlUp)</SPAN>
Set TargetRange = TargetRange.Cells(2, 1) </SPAN>
For i = LBound(Wb) To UBound(Wb)</SPAN>
With Wb(i) </SPAN>
If .Worksheets.Count >= 2 Then</SPAN>
Set ws = .Worksheets(2) </SPAN>
Set SourceRange = ws.Cells(2, 2) </SPAN>
Set SourceRange = ws.range(SourceRange, SourceRange.End(xlToRight))</SPAN>
SourceRange.Copy</SPAN>
TargetRange.PasteSpecial xlPasteValues</SPAN>
Application.CutCopyMode = False</SPAN>
Set TargetRange = TargetRange.Cells(2, 1)</SPAN>
Else</SPAN>
MsgBox "Filename: '" & .Name & "', is missing the sheet we need. Skipping it."</SPAN>
End If</SPAN>
' close the file we've finished with it now</SPAN>
.Close SaveChanges:=False ' false to not attempt to save changes (and not prompt the user for it)</SPAN>
End With</SPAN>
Next i</SPAN>
' by this point all the files that were opened should be closed and we should be looking at the completed SummarySheet table</SPAN>
End Sub</SPAN>
[/INDENT]
</SPAN>[/INDENT]Into this I need to add that the sheets the user selects are saved as a PDF to a folder that is named after the cell in column a that is the row it is appended to, in the same location as this log is kept
I've recorded this -
I can recognize the save as section
I'll be putting the publish to false as there could be multiple uploads at the same time so it would slow the process down a little I think.
Could you help me in terms of how I would go about this?
Fully appreciate this is big ask or at least feels like for me I’m afraid
Thank you for any help</SPAN>
And apologies for missing one of the most important rules
</SPAN>
</SPAN>I've recorded this -
Code:
[INDENT=2]Option Explicit</SPAN>
Sub Macro1()</SPAN>
'</SPAN>
' Macro1 Macro</SPAN>
'</SPAN>
'</SPAN>
ChDir _</SPAN>
"H:\1. Finance Shared Services\FINANCE DATA & MI CHANGE\Financial Risk MI Change\Change Request Forms Recived"</SPAN>
Workbooks.Open Filename:= _</SPAN>
"Same as above with filename"</SPAN>
Range("H10:J10").Select</SPAN>
ChDir _</SPAN>
"Same as above but with the filename on were its being saved"</SPAN>
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _</SPAN>
"H:\1. Finance Shared Services\FINANCE DATA & MI CHANGE\Financial Risk MI Change\Change Request Forms Recived\FS & MI 2\Change Request Form.pdf" _</SPAN>
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _</SPAN>
:=False, OpenAfterPublish:=True</SPAN>
Range("Q26").Select</SPAN>
ActiveWindow.Close</SPAN>
End Sub</SPAN>
[/INDENT]I can recognize the save as section
I'll be putting the publish to false as there could be multiple uploads at the same time so it would slow the process down a little I think.
Could you help me in terms of how I would go about this?
Fully appreciate this is big ask or at least feels like for me I’m afraid
Thank you for any help</SPAN>
And apologies for missing one of the most important rules
</SPAN>
\1. Finance Shared Services\FINANCE DATA & MI CHANGE\Change Request Log) it creates a folder which uses the the cell in colum A, on the row were the data is uploaded to to name the folder
