- Code: Select all
Sub PostToSheet()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets("Sheet2")
Set WS2 = Sheets("Jan15")
Set WS2 = Sheets("Dec14")
' Figure out which row is the next row
NextRow = WS2.Cells(Rows.Count, 44).End(xlUp).Row + 1
'Write the important values to the sheet 2
WS2.Cells(NextRow, 44).Resize(1, 5).Value = Array(WS1.Range("D1"), WS1.Range("B3"), WS1.Range("F3"), WS1.Range("E1"), WS1.Range("F1"))
'Sub CleardataIncreasenumber()
'
' CleardataIncreasenumber Macro
' Clears the pertinent data and increases the receipt number
'
'
Range("B3,F3").Select
Selection.ClearContents
Range("F1").Value = Range("F1").Value + 1
End Sub
I am trying to use the above VBA code to select a worksheet named Jan15 through to Dec15 which is generated by a cell (L1) located on the active sheet1.
The format for this cell is generated by the formula =text(D1,"MMMYY") where D1 is formatted with =now()
If I change the text in the statement Set WS2= Sheets("sheet2") to read Set WS2 = Sheets("Jan15") then the important data is written to the Worksheet (Jan15) or whatever sheet name that I have available.
How do I select the contents of the cell (L1) and get it to replace he Sheet2 value as a macro.