I've spent a lot of time trying to learn the language of VBA, through watching Mr. Excel videos, googling, and forum surfing. I'm looking to combine macros to run. I've picked up so far that it's as simple as:
and incorporated it into my macros. The problem I'm having is that I constantly either get an error on the sub dimension (xxx in this case) or that it doesn't understand the "ws" after the final Next? In my used code (bottom of post), it's supposed to cause that macro to loop through all of the sheets in the workbook, after the workbook is created by the first macro. When I try running them together, the macro stops on the "ws" as mentioned before, but separately, the first part runs fine, and the second part still gets stuck.
It makes logical sense to me that it should work, and according to all of the places online that I've been fortunate enough to come across, it's the same logical argument/variables, so it should all work, but for whatever reason, it just doesn't seem to be working for me.
Anyway, here is the code I'm using, scraped together from wonderful people who have been kind enough to post it on the web to share with the world:
Code1:
Code 2
code 3
I'm trying to tie them together. I understand that right now Code 1 and 2 are tied together, and executed by selecting macro "RDB_Copy_Sheet"
I've tried adding "Call Fill_All_Blanks" before the end of Code 2, and I've tried by adding the full code for Fill_All_Blanks after the end of Code 2, with VBA showing a break there between them, recognizing it as a different command(?).
The point of this whole project is to pull all individual spreadsheets dumped in "C:\Documents and Settings\xxx\My Documents\xxx" which were an output of another program, all starting with 123, and putting them in one excel workbook, then applying macro 3 to the sheets, filling in data that the original program leaves in the spreadsheet as a result of how it outputs its query. I only need the third item since I'll be using the outputs to import into further sheets which then have vlookups run on them, and it needs to be able to pull all of that info for the vlookups to work.
Basically I'm very new to this, this is my first big project, and I've gone through dozens of tutorials, demos, and projects from all over the web to learn how to properly execute this. I hope this forum can help, since it seems to be full of a wealth of information from very talented individuals.
Thank you in advance for your help!
Code:
sub xxx()[INDENT]Dim ws As Worksheet
For Each ws in ActiveWorkbook.Worksheets
[/INDENT][INDENT][INDENT]xxx code xxx
[/INDENT][/INDENT][INDENT]Next ws
[/INDENT]End SubIt makes logical sense to me that it should work, and according to all of the places online that I've been fortunate enough to come across, it's the same logical argument/variables, so it should all work, but for whatever reason, it just doesn't seem to be working for me.
Anyway, here is the code I'm using, scraped together from wonderful people who have been kind enough to post it on the web to share with the world:
Code1:
Code:
Option Explicit
Private myFiles() As String
Private Fnum As Long
Function Get_File_Names(MyPath As String, Subfolders As Boolean, _
ExtStr As String, myReturnedFiles As Variant) As Long
Dim Fso_Obj As Object, RootFolder As Object
Dim SubFolderInRoot As Object, file As Object
'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If
'Create FileSystemObject object
Set Fso_Obj = CreateObject("Scripting.FileSystemObject")
Erase myFiles()
Fnum = 0
'Test if the folder exist and set RootFolder
If Fso_Obj.FolderExists(MyPath) = False Then
Exit Function
End If
Set RootFolder = Fso_Obj.GetFolder(MyPath)
'Fill the array(myFiles)with the list of Excel files in the folder(s)
'Loop through the files in the RootFolder
For Each file In RootFolder.Files
If LCase(file.Name) Like LCase(ExtStr) Then
Fnum = Fnum + 1
ReDim Preserve myFiles(1 To Fnum)
myFiles(Fnum) = MyPath & file.Name
End If
Next file
'Loop through the files in the Sub Folders if SubFolders = True
If Subfolders Then
Call ListFilesInSubfolders(OfFolder:=RootFolder, FileExt:=ExtStr)
End If
myReturnedFiles = myFiles
Get_File_Names = Fnum
End Function
Sub ListFilesInSubfolders(OfFolder As Object, FileExt As String)
'Origenal SubFolder code from Chip Pearson
'http://www.cpearson.com/Excel/RecursionAndFSO.htm
'Changed by Ron de Bruin, 27-March-2008
Dim SubFolder As Object
Dim fileInSubfolder As Object
For Each SubFolder In OfFolder.Subfolders
ListFilesInSubfolders OfFolder:=SubFolder, FileExt:=FileExt
For Each fileInSubfolder In SubFolder.Files
If LCase(fileInSubfolder.Name) Like LCase(FileExt) Then
Fnum = Fnum + 1
ReDim Preserve myFiles(1 To Fnum)
myFiles(Fnum) = SubFolder & "\" & fileInSubfolder.Name
End If
Next fileInSubfolder
Next SubFolder
End Sub
Function RDB_Last(choice As Integer, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
Dim lrw As Long
Dim lcol As Integer
Select Case choice
Case 1:
On Error Resume Next
RDB_Last = rng.Find(What:="*", _
after:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
Case 2:
On Error Resume Next
RDB_Last = rng.Find(What:="*", _
after:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
Case 3:
On Error Resume Next
lrw = rng.Find(What:="*", _
after:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
On Error Resume Next
lcol = rng.Find(What:="*", _
after:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
On Error Resume Next
RDB_Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
If Err.Number > 0 Then
RDB_Last = rng.Cells(1).Address(False, False)
Err.Clear
End If
On Error GoTo 0
End Select
End Function
Code:
Option Explicit
'The example below will copy the first worksheet from each file in a new workbook
'It copy as values because the PasteAsValues argument = True
'First we call the Function "Get_File_Names" to fill a array with all file names
'There are three arguments in this Function that we can change
'1) MyPath = the folder where the files are
'2) Subfolders = True if you want to include subfolders
'3) ExtStr = file extension of the files you want to merge
' ExtStr examples are: "*.xls" , "*.csv" , "*.xlsx"
' "*.xlsm" ,"*.xlsb" , for all Excel file formats use "*.xl*"
' Do not change myReturnedFiles:=myFiles
'Then if there are files in the folder we call the macro "Get_Sheet"
'There are three arguments in this macro that we can change
'1) PasteAsValues = True to paste as values (recommend)
'2) SourceShName = sheet name, if "" it will use the SourceShIndex
'3) SourceShIndex = to avoid problems with different sheet names use the index (1 is the first worksheet)
' Do not change myReturnedFiles:=myFiles
Sub RDB_Copy_Sheet()
Dim myFiles As Variant
Dim myCountOfFiles As Long
myCountOfFiles = Get_File_Names( _
MyPath:="C:\Documents and Settings\xxx\My Documents\xxx", _
Subfolders:=False, _
ExtStr:="123*.*", _
myReturnedFiles:=myFiles)
If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If
Get_Sheet _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
myReturnedFiles:=myFiles
End Sub
' Note: You not have to change the macro below, you only
' edit and run the RDB_Copy_Sheet above.
Sub Get_Sheet(PasteAsValues As Boolean, SourceShName As String, _
SourceShIndex As Integer, myReturnedFiles As Variant)
Dim mybook As Workbook, BaseWks As Worksheet
Dim CalcMode As Long
Dim SourceSh As Variant
Dim sh As Worksheet
Dim I As Long
'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
On Error GoTo ExitTheSub
'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
'Check if we use a named sheet or the index
If SourceShName = "" Then
SourceSh = SourceShIndex
Else
SourceSh = SourceShName
End If
'Loop through all files in the array(myFiles)
For I = LBound(myReturnedFiles) To UBound(myReturnedFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(myReturnedFiles(I))
On Error GoTo 0
If Not mybook Is Nothing Then
'Set sh and check if it is a valid
On Error Resume Next
Set sh = mybook.Sheets(SourceSh)
If Err.Number > 0 Then
Err.Clear
Set sh = Nothing
End If
On Error GoTo 0
If Not sh Is Nothing Then
sh.Copy after:=BaseWks.Parent.Sheets(BaseWks.Parent.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
If PasteAsValues = True Then
With ActiveSheet.UsedRange
.Value = .Value
End With
End If
End If
'Close the workbook without saving
mybook.Close savechanges:=False
End If
'Open the next workbook
Next I
' delete the first sheet in the workbook
Application.DisplayAlerts = False
On Error Resume Next
BaseWks.Delete
On Error GoTo 0
Application.DisplayAlerts = True
ExitTheSub:
'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub
Code:
Sub replaceallblanks()
'
' ReplaceAllBlanks Macro
' Macro recorded 6/17/2003 by xxx
'
' Keyboard Shortcut: Ctrl+m
'
Dim LastRow As Integer
Dim LastColumn As String
Dim WorkSheetName As String
LastColumn = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
LastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
nrows = LastRow
LastColumn = LastColumn
'This removes the bad characters
For x = 1 To LastColumn
For n = 2 To nrows
If IsError(Cells(n, x)) Then
Cells(n, x) = 0
Else
End If
If Cells(n, x) = "" Then
Cells(n, x) = Cells((n - 1), x)
Else
End If
Next
Next
End SubI've tried adding "Call Fill_All_Blanks" before the end of Code 2, and I've tried by adding the full code for Fill_All_Blanks after the end of Code 2, with VBA showing a break there between them, recognizing it as a different command(?).
The point of this whole project is to pull all individual spreadsheets dumped in "C:\Documents and Settings\xxx\My Documents\xxx" which were an output of another program, all starting with 123, and putting them in one excel workbook, then applying macro 3 to the sheets, filling in data that the original program leaves in the spreadsheet as a result of how it outputs its query. I only need the third item since I'll be using the outputs to import into further sheets which then have vlookups run on them, and it needs to be able to pull all of that info for the vlookups to work.
Basically I'm very new to this, this is my first big project, and I've gone through dozens of tutorials, demos, and projects from all over the web to learn how to properly execute this. I hope this forum can help, since it seems to be full of a wealth of information from very talented individuals.
Thank you in advance for your help!
, or even allow me to help others with their assignments/projects. I now see the potential that there is in the workplace for VBA to really automate/assist in some of the more boring and simple tasks that I imagine most office workers who deal with any data encounter regularly. We need a marketing campaign for people to see the potential!
