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]

Based on your description of what you want for the summary sheet, I believe that a simple colum insert would would work. But I am reluctant to offer code because I don't fully understand the way your input sheet works. Do the number of columns that have data entered by the user vary, and if so, under what circumstances would more than one column be used to enter daily data?

 

[andrewb90]

On the Daily sheet the user will enter info into Columns D, E, & F the totals will be calculated in column G and that information is what I need to go to the Summary sheet. Every day the info they type in will be in the same place.

 

[JLGWhiz]

On the Daily sheet the user will enter info into Columns D, E, & F the totals will be calculated in column G and that information is what I need to go to the Summary sheet. Every day the info they type in will be in the same place.

Based on the information posted, I believe this will work. What it is supposed to do is copy column G of the daily sheet and insert that column into column C of the summary sheet. That should move all previous columns to the right. It then copies the immediate previous column and pastes formats only into Column C on the summary Sheet. Then it clears the fields in Columns D thru F of the daily sheet for new entries.

What you need to do:
1. make sue the sheet references are changed to those you are actually using.
2. test this on a copy before applying to your original.

Post back with any problem.

Code:
Sub SendToSummary()
Dim lr As Long
lr = Sheets(1).Cells(Rows.Count, 7).End(xlUp).Row
Sheets(1).Range("G2").EntireColumn.Copy
Sheets(2).Range("c2").EntireColumn.Insert
With Sheets(2)
.Range("D1").EntireColumn.Copy
.Range("C1").PasteSpecial Paste:=xlPasteFormats
End With
Sheets(1).Range("D2:F" & lr).ClearContents
End Sub

 

[andrewb90]

I am not supposed to change anything correct?

I ended receiving error code 400

 

[JLGWhiz]

I am not supposed to change anything correct?

I ended receiving error code 400

I am not sure why you would get a message that a form is already displayed from the code I provided, it doesn't call for a form.

Yes, you need to change the Sheet names if they are not sheets 1 and 2 for Daily and Summary repspectively.

I overlooked that Column G probably has formulas in it, so it would give you a column of #Ref instead of your numbers, so I revised the code to compensate for that. Here is the revision and it seemed to work OK in my test set up, but you need to test it in a copy of your workbook.

Code:
Sub SendToSummary()
Dim lr As Long
lr = Sheets(1).Cells(Rows.Count, 7).End(xlUp).Row
Sheets(2).Range("c2").EntireColumn.Insert
Sheets(1).Range("G2").EntireColumn.Copy
Sheets(2).Range("C2").EntireColumn.PasteSpecial Paste:=xlPasteValues
With Sheets(2)
.Range("D1").EntireColumn.Copy
.Range("C1").PasteSpecial Paste:=xlPasteFormats
End With
Sheets(1).Range("D2:F" & lr).ClearContents
End Sub

If you are inserting this into existing code, or if you have variables or forms with the same names as that of this Sub procedure or any variable in it, then it could cause an error. In that case you can change the ones in this code, but be sure you change all occurrences of it. Since there is only one procedure name and one variable, that should be easy.

 

[andrewb90]

using this code:

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

I get the error 'Object Required'

 

[JLGWhiz]

using this code:

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

I get the error 'Object Required'

Yes, VBA does not recognize Daily and Summary as valid objects in the way you have used them. The correct form would be

Sheets("Daily")
Sheets("Summary")

Try making those changes and see if it works.

 

[andrewb90]

Error code 400

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

 

[JLGWhiz]

Error code 400

Error code 400 is normally when you have a UserForm open and then attempt to open it again. So the only thing I can think of is that the macro name that I used for the code is the same as a user form name that you have in your workbook. When you get the error message, click the debug button on the error dialogue box and see which line of code is highlighted. Since none of the code actually calls a UserForm, I am baffled that you are getting that error message. But if it errors on a line other than the title line then somebody smarter than me will have to explain that. If it is the title line, then you can simply change the title by deleting one or two letters and it should work.