Excel macro question

[Joan]

Hi,
Could anyone tell me how to write a macro that will automatically
copy two colums in an Excel worksheet and at the start of each day
insert them into a new worksheet with the new date on it? The columns
in the new worksheet are named differently than the previous day's
(i.e. Beginning bag count, instead of ending bag count). I've taken
a VB programming course but have not done any programming in Excel yet.
Any help with this would be most appreciated.

Joan

 

[Ruskin Hardie]

Have included an example sub routine, that might help (obviously not the
definitive solution, but it should get you started). Call it by;

createAndCopy "F", "Column 1", "C", "Column 2", Format(Now, "yyyymmdd")

Where column "F" contains the data to copy into the first column of the new
workbook and column "C" contains the data to copy into the second column of
the new workbook.


Sub createAndCopy(ByVal iColOne As String, _
ByVal iHeadOne As String, _
ByVal iColTwo As String, _
ByVal iHeadTwo As String, _
ByVal iFilename As String)
Dim strBookName As String
Dim newBook As Excel.Workbook
Dim newSheet As Excel.Worksheet

strBookName = ActiveWorkbook.Name
Set newBook = Application.Workbooks.Add
Set newSheet = newBook.Worksheets(1)

Workbooks(strBookName).Activate
Worksheets("Sheet1").Select
Range(iColOne & ":" & iColOne).Select
Selection.Copy
newBook.Activate
newSheet.Activate
newSheet.Range("A1").Select
newSheet.Paste
newSheet.Cells(1, 1) = iHeadOne

Workbooks(strBookName).Activate
Worksheets("Sheet1").Select
Range(iColTwo & ":" & iColTwo).Select
Selection.Copy
newBook.Activate
newSheet.Activate
newSheet.Select
newSheet.Range("B1").Select
newSheet.Paste
newSheet.Cells(1, 2) = iHeadTwo

Application.DisplayAlerts = False
newBook.SaveAs iFilename & ".xls"
Application.DisplayAlerts = True
End Sub

There are a variety of other properties of the newBook and newSheet objects,
such as closing the workbook after it's been created, etc. You will want to
change the "Sheet1" references to the name of the worksheet (of the source
workbook) that contains the data that you are copying from...