moving info to different sheets

[andrewb90]

Let me start with saying that I am running Excel 2010 on Win 7 and the user will be using Excel 2007 on Win 7.

In my first sheet 'Daily' I have a form and the info typed in is summed up in Column G. G11:G36 is the first of a few ranges I want to work with.

Now my other sheet 'Summary' has in column C (to start) a similar format and will have formulas to pull the sales info in from the daily sheet.

What I am trying to do is run a Macro that will clear out the entered data in Daily sheet and ready it to be typed in again for the next day. Now when it is clear out for the next day, the new date will already be in place on the sheet. What I want to happen is:
1. the info from the Daily sheet needs to stay in column C (can't be deleted when I clear the daily page)
2. the new information for the next day would need to go to the next column over on the summary page (column D,E,F,etc...)
3. Any formatting on column C in the Summary page would need to carry over to each column.
4. Ideally what would happen is a new column would be added in front of column C with all of the same formatting. So that if you had 30 days in a row, the 30th day of the month would be in column C and the 1st would be in column AF.

To clarify:
Starting on the 1st the info entered into the daily sheet will populate Column C in the Summary sheet. On the 2nd, A new Column C will be added and populated making the 1st column D, on the 3rd a new Column C would be added making Column D the 2nd and Column E the 1st.

I'm really open to any suggestions or ways of doing this. I was thinking that when I run my Macro it could copy what is on the Daily sheet and add a new Column C and enter it in there, but I'm not sure if that is doable or not.

Thanks,

Andrew

 

[JLGWhiz]

Another thought came to me that you might not be putting this code in the public module. If you are putting the code into the code module of a control, such as, a user form, then that could be why it is giving the error 400. But from your original post I gathered that you would be using the code in the general public code module.

 

[andrewb90]

Sorry! I guess that's what these errors were coming from. I didn't realize putting it in a different place would do that.

Now how would I specify a cell range to be copied instead of Column G, G11:G36 ?

 

[JLGWhiz]

Sorry! I guess that's what these errors were coming from. I didn't realize putting it in a different place would do that.

Now how would I specify a cell range to be copied instead of Column G, G11:G36 ?

The reason I used .EntireColumn was because if thought that you might not always have the same number of rows with data in column G. But if you will always have G11:G36 then the code would look like this:

Sub SendToSummary2()
Dim lr As Long
lr = Sheets("Daily").Cells(Rows.Count, 7).End(xlUp).Row
Sheets("Summary").Range("c2").EntireColumn.Insert
Sheets("Daily").Range("G11:G36").Copy
Sheets("Summary").Range("C2").PasteSpecial Paste:=xlPasteValues
With Sheets("Summary")
.Range("D1").EntireColumn.Copy
.Range("C1").PasteSpecial Paste:=xlPasteFormats
End With
Sheets("Daily").Range("D2:F" & lr).ClearContents
Application.CutCopyMode = False
End Sub

 

[andrewb90]

Ok cool, thank you!
So I have been trying to make some adjustments, but I ran into a problem

When I transfer the info to the Summary Page, I am trying to have it start filling in on Row 6 and go down (depending on how many rows are copied) from there. Is that possible?

 

[JLGWhiz]

Ok cool, thank you!
So I have been trying to make some adjustments, but I ran into a problem

When I transfer the info to the Summary Page, I am trying to have it start filling in on Row 6 and go down (depending on how many rows are copied) from there. Is that possible?

Just change this line:

Sheets("Summary").Range("C2").PasteSpecial Paste:=xlPasteValues

To this:

Sheets("Summary").Range("C6").PasteSpecial Paste:=xlPasteValues