Firstly, I need a macro that will copy and paste data from cells AS4:AS11into
column B in another worksheet. However, each time the macro will be run, I
need the results to appear in the next available column, i.e. column C, then
column D, etc.

Secondly, is there any way, before the macro is run, to prompt the user with
an input field whereby they must enter the current date. I would then like
that date value to be input into the relevant column header, to indicate when
that macro was run. The macro can then continue as normal.

Any ideas?

Which rows in Column B (etc)? And cannot date be simply TODAY() rather than
asking for date?

"Sarah (OGI)" wrote:

Firstly, I need a macro that will copy and paste data from cells AS4:AS11into
column B in another worksheet. However, each time the macro will be run, I
need the results to appear in the next available column, i.e. column C, then
column D, etc.

Secondly, is there any way, before the macro is run, to prompt the user with
an input field whereby they must enter the current date. I would then like
that date value to be input into the relevant column header, to indicate when
that macro was run. The macro can then continue as normal.

Any ideas?

Hi Sarah.........
Excel knows what today's date is. If you prompt the user to input a date,
they may input an erroneous one. This macro should do as you want, provided
there is not any other date in the range B4:b11

Sub CopyRangeAddDate()
Range("AS4:AS11").Select
Selection.Copy
Range("As4").Select
Selection.End(xlToLeft).Select
Selection.Offset(0, 1).Select
ActiveSheet.Paste
Selection.Offset(-1, 0).Select
ActiveCell.Value = Date
End Sub


Vaya con Dios,
Chuck, CABGx3


"Sarah (OGI)" wrote:

Firstly, I need a macro that will copy and paste data from cells AS4:AS11into
column B in another worksheet. However, each time the macro will be run, I
need the results to appear in the next available column, i.e. column C, then
column D, etc.

Secondly, is there any way, before the macro is run, to prompt the user with
an input field whereby they must enter the current date. I would then like
that date value to be input into the relevant column header, to indicate when
that macro was run. The macro can then continue as normal.

Any ideas?

Copies data row 2 and puts date in row ! from B onwards. There is no check
that macro has run more than once in a given day i.e. does a copy for TODAY()
aready exist.


Sub Copy_AS4_AS11()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim nextcol As Integer


Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

nextcol = Application.Max(2, ws2.Cells(1,
Columns.Count).End(xlToLeft).Column + 1)
ws1.Cells(4, "AS").Resize(8, 1).Copy ws2.Cells(2, nextcol) ' <== copies to
row 2
ws2.Cells(1, nextcol) = Format(Now(), "dd/mm/yyyy")

End Sub

"CLR" wrote:

Hi Sarah.........
Excel knows what today's date is. If you prompt the user to input a date,
they may input an erroneous one. This macro should do as you want, provided
there is not any other date in the range B4:b11

Sub CopyRangeAddDate()
Range("AS4:AS11").Select
Selection.Copy
Range("As4").Select
Selection.End(xlToLeft).Select
Selection.Offset(0, 1).Select
ActiveSheet.Paste
Selection.Offset(-1, 0).Select
ActiveCell.Value = Date
End Sub


Vaya con Dios,
Chuck, CABGx3


"Sarah (OGI)" wrote:

Firstly, I need a macro that will copy and paste data from cells AS4:AS11into
column B in another worksheet. However, each time the macro will be run, I
need the results to appear in the next available column, i.e. column C, then
column D, etc.

Secondly, is there any way, before the macro is run, to prompt the user with
an input field whereby they must enter the current date. I would then like
that date value to be input into the relevant column header, to indicate when
that macro was run. The macro can then continue as normal.

Any ideas?