Slightly complicated question - I'll explain as best as I can.

I have two sheets in a file, one is the weekly sales (all data on one line)
and the other is the previous weeks sales which have been hard coded (1st
week on line 1, 2nd week on line two...52nd week on line 52).

I want the weekly sales sheet to remain the same as it has links into
another report. What I need is a macro to copy the line of data from the
weekly sheet into the relevant weeks line on the previous weeks sheet.

I can only create macro's that take the data from the weekly sheet and
enters it into a specified line in the other sheet. So I need something that
can enter the data in line relevant to the week. I thought about creating a
Combo Box with the dates and using the output of 1,2,3 etc as a guide for the
macro to input the data into lines 1,2,3 etc of the previous week sheet?

Any help would be greatly appreciated.

Do you have a date on the weekly sales line that can be used to determine the
destination line in the previous week sales sheet?

"Luke" wrote:

Slightly complicated question - I'll explain as best as I can.

I have two sheets in a file, one is the weekly sales (all data on one line)
and the other is the previous weeks sales which have been hard coded (1st
week on line 1, 2nd week on line two...52nd week on line 52).

I want the weekly sales sheet to remain the same as it has links into
another report. What I need is a macro to copy the line of data from the
weekly sheet into the relevant weeks line on the previous weeks sheet.

I can only create macro's that take the data from the weekly sheet and
enters it into a specified line in the other sheet. So I need something that
can enter the data in line relevant to the week. I thought about creating a
Combo Box with the dates and using the output of 1,2,3 etc as a guide for the
macro to input the data into lines 1,2,3 etc of the previous week sheet?

Any help would be greatly appreciated.

No date at present David, but I can add one in if that will make it work?

"David Hepner" wrote:

Do you have a date on the weekly sales line that can be used to determine the
destination line in the previous week sales sheet?

"Luke" wrote:

Slightly complicated question - I'll explain as best as I can.

I have two sheets in a file, one is the weekly sales (all data on one line)
and the other is the previous weeks sales which have been hard coded (1st
week on line 1, 2nd week on line two...52nd week on line 52).

I want the weekly sales sheet to remain the same as it has links into
another report. What I need is a macro to copy the line of data from the
weekly sheet into the relevant weeks line on the previous weeks sheet.

I can only create macro's that take the data from the weekly sheet and
enters it into a specified line in the other sheet. So I need something that
can enter the data in line relevant to the week. I thought about creating a
Combo Box with the dates and using the output of 1,2,3 etc as a guide for the
macro to input the data into lines 1,2,3 etc of the previous week sheet?

Any help would be greatly appreciated.

Lets start with this to see if I am on the right track.

Here is some code that will ask you what row to copy the data to:
(You will need to change the sheet names and range refrences)

Sub Macro1()
Dim RowNum As Integer

'Copies data
'Change range references here
Range("A2:M2").Select
Selection.Copy
Sheets("PreviousWeekSales").Select
'Pastes values
RowNum = InputBox("Enter destination row number.")
'Change range references here
Range("A" & RowNum & ":M" & RowNum).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Returns to main sheet
Sheets("WeeklySales").Select
Range("A3").Select
Application.CutCopyMode = False
End Sub





"Luke" wrote:

No date at present David, but I can add one in if that will make it work?

"David Hepner" wrote:

Do you have a date on the weekly sales line that can be used to determine the
destination line in the previous week sales sheet?

"Luke" wrote:

Slightly complicated question - I'll explain as best as I can.

I have two sheets in a file, one is the weekly sales (all data on one line)
and the other is the previous weeks sales which have been hard coded (1st
week on line 1, 2nd week on line two...52nd week on line 52).

I want the weekly sales sheet to remain the same as it has links into
another report. What I need is a macro to copy the line of data from the
weekly sheet into the relevant weeks line on the previous weeks sheet.

I can only create macro's that take the data from the weekly sheet and
enters it into a specified line in the other sheet. So I need something that
can enter the data in line relevant to the week. I thought about creating a
Combo Box with the dates and using the output of 1,2,3 etc as a guide for the
macro to input the data into lines 1,2,3 etc of the previous week sheet?

Any help would be greatly appreciated.

David

That is exactly what I need.

Now is there anyway to automate which line the macro enters the data into
rather than having the options box?

"David Hepner" wrote:

Lets start with this to see if I am on the right track.

Here is some code that will ask you what row to copy the data to:
(You will need to change the sheet names and range refrences)

Sub Macro1()
Dim RowNum As Integer

'Copies data
'Change range references here
Range("A2:M2").Select
Selection.Copy
Sheets("PreviousWeekSales").Select
'Pastes values
RowNum = InputBox("Enter destination row number.")
'Change range references here
Range("A" & RowNum & ":M" & RowNum).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Returns to main sheet
Sheets("WeeklySales").Select
Range("A3").Select
Application.CutCopyMode = False
End Sub





"Luke" wrote:

No date at present David, but I can add one in if that will make it work?

"David Hepner" wrote:

Do you have a date on the weekly sales line that can be used to determine the
destination line in the previous week sales sheet?

"Luke" wrote:

Slightly complicated question - I'll explain as best as I can.

I have two sheets in a file, one is the weekly sales (all data on one line)
and the other is the previous weeks sales which have been hard coded (1st
week on line 1, 2nd week on line two...52nd week on line 52).

I want the weekly sales sheet to remain the same as it has links into
another report. What I need is a macro to copy the line of data from the
weekly sheet into the relevant weeks line on the previous weeks sheet.

I can only create macro's that take the data from the weekly sheet and
enters it into a specified line in the other sheet. So I need something that
can enter the data in line relevant to the week. I thought about creating a
Combo Box with the dates and using the output of 1,2,3 etc as a guide for the
macro to input the data into lines 1,2,3 etc of the previous week sheet?

Any help would be greatly appreciated.

Try this:

Sub Macro1()
Dim RowNum As Integer

'Copies data
Range("A2:M2").Select
Selection.Copy
Sheets("PreviousWeekSales").Select
'Pastes values

Range("A2").End(xlDown).Select
RowNum = ActiveCell.Row + 1
Range("A" & RowNum & ":M" & RowNum).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Returns to main sheet
Sheets("WeeklySales").Select
Range("A3").Select
Application.CutCopyMode = False
End Sub



"Luke" wrote:

David

That is exactly what I need.

Now is there anyway to automate which line the macro enters the data into
rather than having the options box?

"David Hepner" wrote:

Lets start with this to see if I am on the right track.

Here is some code that will ask you what row to copy the data to:
(You will need to change the sheet names and range refrences)

Sub Macro1()
Dim RowNum As Integer

'Copies data
'Change range references here
Range("A2:M2").Select
Selection.Copy
Sheets("PreviousWeekSales").Select
'Pastes values
RowNum = InputBox("Enter destination row number.")
'Change range references here
Range("A" & RowNum & ":M" & RowNum).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Returns to main sheet
Sheets("WeeklySales").Select
Range("A3").Select
Application.CutCopyMode = False
End Sub





"Luke" wrote:

No date at present David, but I can add one in if that will make it work?

"David Hepner" wrote:

Do you have a date on the weekly sales line that can be used to determine the
destination line in the previous week sales sheet?

"Luke" wrote:

Slightly complicated question - I'll explain as best as I can.

I have two sheets in a file, one is the weekly sales (all data on one line)
and the other is the previous weeks sales which have been hard coded (1st
week on line 1, 2nd week on line two...52nd week on line 52).

I want the weekly sales sheet to remain the same as it has links into
another report. What I need is a macro to copy the line of data from the
weekly sheet into the relevant weeks line on the previous weeks sheet.

I can only create macro's that take the data from the weekly sheet and
enters it into a specified line in the other sheet. So I need something that
can enter the data in line relevant to the week. I thought about creating a
Combo Box with the dates and using the output of 1,2,3 etc as a guide for the
macro to input the data into lines 1,2,3 etc of the previous week sheet?

Any help would be greatly appreciated.