Thanks for getting back to me Trevor.
I have to admit i never even knew the "Immediate window" existed.
It looks like a brilliant tool even though I'm not fully aware of it's full function.
I have tried the formula and noticed how it prints the month and day however am not able to go further with this. I have tried to use the "IF" statements to print a message box IF the month is 'XX' but with no luck.
I have to say that although I have played a fair bit with excel vba, I am still very much a beginner and there is still a lot i don't know about programming.
Even if I get this function to working, I'm not sure it's viable because I'd have to have several lines of code including "IF" statements for every month tab and every day cell to copy the value to.
I have searched very broadly for anything close to what I need to make this work and the closest possible function is listed below. It is from
http://stackoverflow.com/questions/...opy-rows-to-another-worksheet/6167187#6167187
However it uses sharepoint, which I have no idea of, but this is the model I tried to modify to do what I need to do, and again with no luck.
Below that I have pasted what I thought would be a working model of what I need, again with no luck.
I'm so far lost for even knowing where to look for information or where to begin learning this that I don't know what to do.
I say again, I really appreciate your help because I feel hopelessly lost with this.
<code>Public Sub MoveData(MonthNumber As Integer, SheetName As String)
Dim sharePoint As Worksheet
Dim Month As Worksheet
Dim spRange As Range
Dim cell As Range
Set sharePoint = Sheets("Sharepoint")
Set Month = Sheets(SheetName)
Set spRange = sharePoint.Range("A2")
Set spRange = sharePoint.Range("A2:" & spRange.End(xlDown).Address)
For Each cell In spRange
If Format(cell.Value, "MM") = MonthNumber Then
copyRowTo sharePoint.Range(cell.Row & ":" & cell.Row), Month
End If
Next cell
End Sub
Sub copyRowTo(rng As Range, ws As Worksheet)
Dim newRange As Range
Set newRange = ws.Range("A1")
If newRange.Offset(1).Value <> "" Then
Set newRange = newRange.End(xlDown).Offset(1)
Else
Set newRange = newRange.Offset(1)
End If
rng.Copy
newRange.PasteSpecial (xlPasteAll)
End Sub
</code>Below is MY interpretation of what I think should reflect my function.
'THIS SUB IS MEANT TO BE GETTING CALLED BY THE PRESS OF A BUTTON (WITHIN ANOTHER SUB)
<code>Private Sub MoveData(MonthNumber As Integer, SheetName As String) 'DOES NOT EVEN RUN WITH THE RANGES CALLED IN THE BRACKETS
Dim BalSheets As Worksheet 'TARGET WORKBOOK
Dim Month As Worksheet
Dim Day As Range 'USED TO DETERMINE THE DAY PART OF THE MONTH
Dim DateInv As Range 'INVOICE DATE ON THIS WORKBOOK'S WORKSHEET
Dim cell As Range
Set BalSheets = Sheets("Balance Sheets")
Set Month = BalSheets(SheetName)
Set DateInv = ThisWorkbook.Sheets("Sheet1").Range("d18") 'FINDS THE MONTH
Set Day = ThisWorkbook.Sheets("Sheet1").Range("d18") 'FINDS THE DAY?
'Set spRange = sharePoint.Range("A2:" & spRange.End(xlDown).Address) 'NOT LOOKING FOR A RANGE OF CELLS SO I LEFT THIS OUT
'For Each cell In spRange
'PRESUMING THIS LOCATES THE "MONTH" DATE WITHIN THIS WORKBOOK'S WORKSHEET(SHEET1) IN THE CELL D18
'NOW I NEED TO LOCATE THE "DAY" DATE FROM THE DATE WITHIN THE SAME CELL SO THAT THE INVOICE VALUE CAN BE COPIED TO IT'S RESPECTIVE DESTINATION CELL
'THE DESTINATION WORKSHEET WILL HAVE B4:P4 AND B22:Q22 AS THE DAYS 1 TO 15 AND 16-30 FOR EASY VIEWING
'ONCE THIS IS ALL FOUND, I WANT TO COPY THE INVOICE VALUE IN CELL H33 TO THE DESTINATION CELL DETERMINED BY THIS SUB.
If Format(cell.Value, "MM") = MonthNumber Then
copyRowTo BalSheets.Range(cell), Month 'TRYING TO COPY JUST THE ONE CELL, NOT A WHOLE RANGE OF CELLS
End If
'Next cell
End Sub
Private Sub copyRowTo(rng As Range, ws As Worksheet) 'NOT TOO SURE ABOUT HOW THIS SUB OPERATES
Dim newRange As Range
Set newRange = ws.Range("A2") 'THIS SHOULD BE THE DESTINATION CELL DETERMINED IN THE PREVIOUS SUB (BETWEEN B4:P4 AND B22"Q22)
'If newRange.Offset(1).Value <> "" Then 'NOT SURE IF THIS IS NEEDED
Set newRange = newRange.Day 'OR WHATEVER IT WOULD BE?
'Else
'Set newRange = newRange.Offset(1)
'End If
rng.Copy
newRange.PasteSpecial (xlPasteAll)
End Sub
</code>Cheers,
Alex
