So I am trying to do some code, but i am quite the novice. I have a masterfile which has dates (1/1/2012, 12/1/2011, etc) that correspond with months we do a specific task within these excel files call position exposures. In each 'position exposure' excel workbook I have a work sheet called "position exposure database". That excel sheet simply has month end dates in row 1 and in row 2 will have a complete or not complete depending on if it was completed. What I want this VBA code to do, is search for a series of dates in each of these excel workbooks in that specific worksheet, find that date, then offset by one row down, copy the contents (will be 'complete' or blank) and paste special the value inside my master file in a tab called monthlies in a cell which corresponds to the column which has that specific date in that column. At the end of all of this I want the code to go through 15 different excel work books and tell me whether or not they were completed in specific months. I started just trying to understand code so this may help in terms of what I am doing. This code below finds 1/1/2012, offsets the content by one row and than expands the finding for 3 columns (I was just tinkering with this column aspect) it then proceeds to paste it into a excel book called test run in a specific cell (which i'd want to be dynamic). Underneath this one piece of code i also will place another one I did which was opening the specific file name and closing it at the end.
Sub Find_First()
Dim rng As Range
Dim destsheet As Worksheet
Windows("b.xlsx").Activate
Set rng = Worksheets("position exposure database").Range("a1:zz1").Find(What:="1/1/2012", LookAt:=xlWhole, _
LookIn:=xlValues)
If rng Is Nothing Then
MsgBox "Data not found"
Exit Sub
Else ' the above tells you to set a range in the position exposure database tab as "x" and find a specific date if that
'is nothing than it will say not found
rng.offset(1, 0).Resize(1, 3).Copy 'this finds my variable offsets one row down and currently resizes it to 3 rows
Windows("test run.xlsm").Activate 'this is me activating the workbook I need
Set destsheet = Worksheets("monthlies") ' this is telling it this is the destination sheet
Range("d6").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End If
End Sub
___________OTHER CODE BELOW
Option Explicit
Sub Find_First()
Dim FindString As Date
Dim rng As Range
Dim test As Worksheet
Dim monthlies As Worksheet
Dim sourcesheet As Worksheet
Dim destsheet As Worksheet
'above are the things which i defined not sure what this does
Workbooks.Open Filename:="Z:APM COMMON FOLDERSMANAGER'S MATERIALSPosition ExposureEmoryb.xlsx"
Set sourcesheet = Worksheets("position exposure database")
FindString = "1/1/2012" 'you can also put ranges with # signs as well
If Trim(FindString) <> "" Then
With Sheets("position exposure database").Range("A1:z1")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End If
'above essentially is find the date in a specific range and then selecting that cell
Selection.offset(1, 0).Select
'above takes the selected cell and now offets by 1 row down so I can get the value
Selection.Copy
'i think this is self explantory
Windows("test run.xlsm").Activate
Set destsheet = Worksheets("monthlies")
Range("d6").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
'above is taking the value that is below the date and inputting it into the cell
Windows("b.xlsx").Activate
ActiveWorkbook.Close False
Sub Find_First()
Dim rng As Range
Dim destsheet As Worksheet
Windows("b.xlsx").Activate
Set rng = Worksheets("position exposure database").Range("a1:zz1").Find(What:="1/1/2012", LookAt:=xlWhole, _
LookIn:=xlValues)
If rng Is Nothing Then
MsgBox "Data not found"
Exit Sub
Else ' the above tells you to set a range in the position exposure database tab as "x" and find a specific date if that
'is nothing than it will say not found
rng.offset(1, 0).Resize(1, 3).Copy 'this finds my variable offsets one row down and currently resizes it to 3 rows
Windows("test run.xlsm").Activate 'this is me activating the workbook I need
Set destsheet = Worksheets("monthlies") ' this is telling it this is the destination sheet
Range("d6").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End If
End Sub
___________OTHER CODE BELOW
Option Explicit
Sub Find_First()
Dim FindString As Date
Dim rng As Range
Dim test As Worksheet
Dim monthlies As Worksheet
Dim sourcesheet As Worksheet
Dim destsheet As Worksheet
'above are the things which i defined not sure what this does
Workbooks.Open Filename:="Z:APM COMMON FOLDERSMANAGER'S MATERIALSPosition ExposureEmoryb.xlsx"
Set sourcesheet = Worksheets("position exposure database")
FindString = "1/1/2012" 'you can also put ranges with # signs as well
If Trim(FindString) <> "" Then
With Sheets("position exposure database").Range("A1:z1")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End If
'above essentially is find the date in a specific range and then selecting that cell
Selection.offset(1, 0).Select
'above takes the selected cell and now offets by 1 row down so I can get the value
Selection.Copy
'i think this is self explantory
Windows("test run.xlsm").Activate
Set destsheet = Worksheets("monthlies")
Range("d6").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
'above is taking the value that is below the date and inputting it into the cell
Windows("b.xlsx").Activate
ActiveWorkbook.Close False