Resolved >>> Auto-populate cells with data from other cells

My sheet goes from monday to friday on the coulombs. On the rows i have various data sections for each day. I would like the data on the last day (any day after monday) that there is data for a sheet to input that data into the monday slot when the "master date" is changed on the cheese sheet. The idea here being that the script will take the last entered data for a given sheet, and put it into the monday coulomb on the same sheet when the date is changed. This is kind of hard to explain so if you need clarification let me know. Attached is the sheet i'm working with. Thanks in advance for any help.

nate

Hi

Sorry, but don't follow.

Can you step through what you want to happen using your sheet as the example. So if you change cheese!A2 from 24/9/07 to 1/10/07 what would happen next? Data from where would go to where?


rylo

will do.

after you change the date on cheese!A2-

- It will save the sheet (by date) creating a new file named xxxxx-the date.xls
- each sheet will grab the set of data from the Friday coulomb
- Then it will insert that friday data in the monday coulomb
- it will then erase all data for days tuesday through friday
- thus we have a brand new page for the next week - with the previous weeks data inserted (since no changes will occur over the weekend) in the monday coulomb.

Let me know if more clarification is need. something is lost in translation via the internet, but thank you for your reply.

ciao,

nate

Hi

See how this goes.

Sub PrepForNewWeek()
  For Each sh In Worksheets
    If sh.Name = "Grommet Jr." Then
      sh.Range("F6:F33").Copy
      sh.Range("B6").PasteSpecial xlPasteValues
    Else
      sh.Range("F6:F32").Copy
      sh.Range("B6").PasteSpecial xlPasteValues
    End If
  Next sh
  
  'ChDrive Left(ThisWorkbook.Path, 1)
  'ChDir ThisWorkbook.Path
  ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(Sheets("Cheese").Range("A2"), "yyyymmdd") & ".xls", FileFormat:=xlNormal
  
End Sub

I've commented out a couple of rows that will change the default drive / directory and put the full path into the saveas file name. Gives you some code options in case you want the file to go somewhere else.


rylo

Thank you very much. This will help. I'll get back with any questions after i have a chance to look at the code.

Originally Posted by rylo

Hi

See how this goes.

Sub PrepForNewWeek()
  For Each sh In Worksheets
    If sh.Name = "Grommet Jr." Then
      sh.Range("F6:F33").Copy
      sh.Range("B6").PasteSpecial xlPasteValues
    Else
      sh.Range("F6:F32").Copy
      sh.Range("B6").PasteSpecial xlPasteValues
    End If
  Next sh
  
  'ChDrive Left(ThisWorkbook.Path, 1)
  'ChDir ThisWorkbook.Path
  ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(Sheets("Cheese").Range("A2"), "yyyymmdd") & ".xls", FileFormat:=xlNormal
  
End Sub

I've commented out a couple of rows that will change the default drive / directory and put the full path into the saveas file name. Gives you some code options in case you want the file to go somewhere else.


rylo

When i walk through the code it will copy and paste the correct cells data into the monday column. But when i change the date in cell Cheese!A2 it does not run. Is there a way to make this bit of code execute when the date is changed on the cheese A2 cell? Thank you for your help, this does exactly what i need.

Nate

Nate

Right click on the Cheese sheet tab, select view code and insert

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count = 1 And Target.Address = "$A$2" Then
    For Each sh In Worksheets
    If sh.Name = "Grommet Jr." Then
      sh.Range("F6:F33").Copy
      sh.Range("B6").PasteSpecial xlPasteValues
    Else
      sh.Range("F6:F32").Copy
      sh.Range("B6").PasteSpecial xlPasteValues
    End If
  Next sh
  
  'ChDrive Left(ThisWorkbook.Path, 1)
  'ChDir ThisWorkbook.Path
  ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(Sheets("Cheese").Range("A2"), "yyyymmdd") & ".xls", FileFormat:=xlNormal
  
  End If
End Sub

I deliberately left it away from an event. If you make a change to the cell and there is nothing in the Friday data, then it will overfill with nothing. Are you sure you don't want to be able to control the activity - say with a button???

rylo