Page 6 of 7.
Results 101...120 of 123
Sub MergewithAutoFilter() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long, FNum As Long Dim mybook As Workbook, BaseWks As Worksheet Dim sourceRange As Range, destrange As Range Dim rnum As Long, CalcMode As Long Dim rng As Range, SearchValue As String Dim FilterField As Integer, RangeAddress As String Dim ShName As Variant, RwCount As Long '************************************************************** '***Change these five lines of code before you run the macro*** '************************************************************** ' Change this to the pathfolder location of the files. MyPath = "network pathfolder name)" ' Fill in the name of the sheet containing the data. ' Use ShName = "Sheet Name" to use a sheet name instead if its ' index. This example uses the index of the first sheet in ' every workbook. ShName = "Pipeline" ' Fill in the filter range: A1 is the header of the first ' column and G is the last column in the range and will ' filter on all rows on the sheet. ' You can also use a fixed range such as A1:G2500. RangeAddress = Range("A1:I" & Rows.count).Address ' Set the field that you want to filter in the range ' "1 = column A" in this example because the filter range ' starts in column A. FilterField = 1 ' Fill in the filter value. Use the "<>" if you want to ' filter on the absence of a term. Or use wildcards such ' as "ron*" for cells that start with ron, or use ' "*ron*" if you look for cells where ron is a part of the ' cell value. SearchValue = "<>" '********************************************************** '********************************************************** ' Add a slash after MyPath if needed. If Right(MyPath, 1) <> "" Then MyPath = MyPath & "" End If ' If there are no Excel files in the folder, exit. FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If ' Fill the myFiles array with the list of Excel files in the ' folder. FNum = 0 Do While FilesInPath <> "" FNum = FNum + 1 ReDim Preserve MyFiles(1 To FNum) MyFiles(FNum) = FilesInPath FilesInPath = Dir() Loop ' Change application properties. With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With ' Select workbook and worksheet to paste the copied date into Set BaseWks = ActiveWorkbook.Sheets("BaseData") rnum = 1 ' Loop through all files in the myFiles array. If FNum > 0 Then For FNum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(FNum)) On Error GoTo 0 If Not mybook Is Nothing Then On Error Resume Next ' Set the filter range. With mybook.Worksheets(ShName) Set sourceRange = .Range(RangeAddress) End With If Err.Number > 0 Then Err.Clear Set sourceRange = Nothing End If On Error GoTo 0 If Not sourceRange Is Nothing Then ' Find the last row in target worksheet. rnum = RDB_Last(1, BaseWks.Cells) + 1 With sourceRange.Parent Set rng = Nothing ' Remove the AutoFilter. .AutoFilterMode = False ' Filter the range on the ' value in filter column. sourceRange.AutoFilter Field:=FilterField, _ Criteria1:=SearchValue With .AutoFilter.Range ' Check to see if there are results ' after after applying the filter. RwCount = .Columns(1).Cells. _ SpecialCells(xlCellTypeVisible).Cells.count - 1 If RwCount = 0 Then ' There is no data, only the ' header. Else ' Set a range without the ' header row. Set rng = .Resize(.Rows.count - 1, .Columns.count). _ Offset(1, 0).SpecialCells(xlCellTypeVisible) ' Copy the range and the file name ' in column A. If rnum + RwCount < BaseWks.Rows.count Then BaseWks.Cells(rnum, "A").Resize(RwCount).Value _ = mybook.Name rng.Copy BaseWks.Cells(rnum, "B") End If End If End With 'Remove the AutoFilter .AutoFilterMode = False End With End If ' Close the workbook without saving. mybook.Close savechanges:=False End If ' Open the next workbook. Next FNum ' Set the column width in the new workbook. BaseWks.Columns.AutoFit MsgBox "Look at the merge results in the workbook" & _ "after you click on OK." End If ' Restore the application properties. With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End SubWhat I would like to do is to check whether the worksheet that is receiving the data (the BaseData sheet specified in the code) is empty (apart from header row which starts at A1) and, if it isn't, to clear the data before pasting anything from the other workbooks.
Sub Merge() Dim i As Integer Dim Source As Workbook Dim Destination As Workbook Dim myFileName As String Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set Destination = ThisWorkbook With Application.FileSearch .NewSearch 'Change path to suit .LookIn = "C:Documents and SettingsAdministratorDesktoptestslave" .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then 'Workbooks in folder For i = 1 To 3 ' Loop through all 'Set Paste target first myFileName = Dir(.FoundFiles(i)) 'Open Souce file Set Source = Workbooks.Open(.FoundFiles(i)) With Destination.Sheets(1) '''''' Here I would then have to make an if to do a different selection based on what input file I have Select Case myFileName Case "Slave1.xls" 'Here selection for first file Case "Slave2.xls" 'Here selection for second file End Select Set Tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1) End With 'Copy and paste to destination Source.Sheets(1).Range("A4:D20").Copy Tgt 'Close source file without saving Source.Close False Next i End If End With On Error GoTo 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True Application.CutCopyMode = False End SubPlease help!!!
BidFile = Workbooks("Bid Sheet Creator").Sheets("Bid Sheet").Rows(3).Columns(2) Application.CutCopyMode = False Sheets("Bid Sheet").Copy ActiveSheet.Name = BidFileSo i am copying my "Bid Sheet" to a new file and giving it a name. I have code below this saving the file then closing it but i am wondering if there is a way to change my above code to do it all in the background?
Set AppWord = New Word.Application AppWord.Visible = True Set DocWord = AppWord.Documents.Add For Each ws In ActiveWorkbook.Worksheets If ws.Range("A11").Value = "INCOME STATEMENT" Then ws.Activate ActiveSheet.Range("A1:E132").Copy AppWord.Selection.PasteSpecial DataType:=wdPasteMetafilePicture, Placement:=wdInLine Application.CutCopyMode = False End If Next ws Set AppWord = Nothing Set DocWord = Nothing ActiveWorkbook.Close FalseThanks for the help.
Sub Merge() Dim wd As Object Dim wdocSource As Object Dim strWorkbookName As String On Error Resume Next Set wd = GetObject(, "Word.Application") If wd Is Nothing Then Set wd = CreateObject("Word.Application") End If On Error GoTo 0 Set wdocSource = wd.Documents.Open("C:Documents and Settingschristopher.klineDesktopWork FilesProjectsPendingAutomated Bank MemoAIB Bank Memo.doc") strWorkbookName = ThisWorkbook.Path & "" & ThisWorkbook.Name wdocSource.MailMerge.MainDocumentType = wdFormLetters wdocSource.MailMerge.OpenDataSource _ Name:=strWorkbookName, _ AddToRecentFiles:=False, _ Revert:=False, _ Format:=wdOpenFormatAuto, _ Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _ SQLStatement:="SELECT * FROM `Data$`" With wdocSource.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With wdocSource.Close SaveChanges:=True wd.Visible = False Set wdocSource = Nothing Set wd = Nothing If Len(Dir("P:LNL Finance 2008BANK MEMOSCreated and Sent" & "" & "BankTEST- " & Format(Date, "mm-dd-yy") & "-1.pdf")) Then ActiveDocument.ExportAsFixedFormat OutputFileName:="P:LNL Finance 2008BANK MEMOSCreated and Sent" & "" & "BankTEST- " & Format(Date, "mm-dd-yy") & "-2.pdf", ExportFormat:= _ wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _ wdExportOptimizeForPrint, Range:=wdExportAllDocument, _ Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _ CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ BitmapMissingFonts:=True, UseISO19005_1:=False Else ActiveDocument.ExportAsFixedFormat OutputFileName:="P:LNL Finance 2008BANK MEMOSCreated and Sent" & "" & "BankTEST- " & Format(Date, "mm-dd-yy") & "-1.pdf", ExportFormat:= _ wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _ wdExportOptimizeForPrint, Range:=wdExportAllDocument, _ Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _ CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ BitmapMissingFonts:=True, UseISO19005_1:=False End If ActiveDocument.Close (False) Word.Application.Quit End Sub
startYear = InputSheet.Range("Start_Year")with the code
startYear = ThisWorkbook.Worksheets("Input Sheet").Range("Start_Year")In other words, the worksheet code-named InputSheet was not yet properly visible to the compiler. It was visible if I stopped execution and recompiled, but by then, the initialization routines had already aborted. By using the indexed Worksheets method, it resolves objects at run-time and I was able to avoid having the compiler having to know about the object, but this is obviously a serious flaw. Also if a user changes the name of the worksheet tab, this workaround will fail, which is why I prefer using the worksheet's code-name.
VB: ActiveChart.location Where:=xlLocationAsNewSheet, name:=chart_name If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marinesor this one
VB: Charts.Add If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesEverything else works at the speed it always did but these two chart codes take much longer.
VB: Private Sub Workbook_BeforeClose(Cancel As Boolean) If Range("G").Value = "Yes" Then MsgBox ("Please leave a comment") Cancel = True 'cancels the save event End If If Range("G").Value = "No" Then MsgBox ("Please leave a comment") Cancel = True 'cancels the save event End If End Sub If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesI then saved this in VBA, went to the worksheet, selected "Other", left the comments field blank, and went to save the document, and not only did it let me, it let me close the document as well. No message box, nothing.
VB: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim mylogoff Set mylogoff = Sheet1.Range("A65536").End(xlUp).Offset(0, 1) mylogoff.FormulaR1C1 = "=NOW()" mylogoff.Copy mylogoff.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = Fals mylogoff.Offset(0, 1).FormulaR1C1 = "=RC[-1]-RC[-2]" Application.DisplayAlerts = False Sheets("Start Here").Select ThisWorkbook.Save ThisWorkbook.Close End Sub Private Sub Workbook_BeforeSave _ (ByVal SaveAsUI As Boolean, Cancel As Boolean) 'comment this out to be able to save as a seperate name 'this macro will allow to save elsewhere but not allow file name changes Dim NamePath As String Dim strName As String Dim lFind As Long If SaveAsUI = True Then Cancel = True With Application .EnableEvents = False NamePath = .GetSaveAsFilename strName = Mid(NamePath, InStrRev(NamePath, "", -1, vbTextCompare) + 1, 256) If NamePath = "False" Then .EnableEvents = True Exit Sub ElseIf strName Me.Name Then MsgBox "You cannot save as another name" .EnableEvents = True Exit Sub Else Me.SaveAs NamePath .EnableEvents = True End If End With End If End Sub If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesI hope this is somewhat explanitory enough, as I have had to figure out what he was attempting to do. He still should be able to have access to add and remove contents within the workbook. He just doesn't mess with the VB side of it, and I would like to keep it that way.
VB: Sub dailysalesfix() ' ' dailysalesfix Macro Application.ScreenUpdating = False ChDir "U:AccSalesDAILY SALES & MDS2007 SPRINGENT01" Workbooks.Open Filename:= _ "U:AccSalesDAILY SALES & MDS2007 SPRINGENT01116.xls", UpdateLinks:=3 Sheets("Sales Forecast").Select ActiveSheet.Unprotect Password:="profit" Application.Run "PERSONAL.XLS!Macro10" ActiveSheet.Protect Password:="profit" ActiveWorkbook.Save ActiveWorkbook.Close If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Page 6 of 7.
Results 101...120 of 123