Marco moving data

[Daroh]

Hi, I need help designing a macro that will copy values on 'Sheet 2' (H7 - H23, H10 and H15 are blank) to G7, and then when new data is entered move the date from G7 to be moved to F7 and so on .. I have tried using the record macro feature and had no success. Any help will be greatly appreciated. Thanks

 

[JPARKHURST]

What is your intent? What is the overall purpose of the worksheet (in this aspect)?

And then are you looking for something which will find, copy and paste your data into the next column?

What is "New data is entered"? Where is data being entered? Are you backing up your columns - by coping current column to one column over so you can then enter data in H7?

Hi, I need help designing a macro that will copy values on 'Sheet 2' (H7 - H23, H10 and H15 are blank) to G7, and then when new data is entered move the date from G7 to be moved to F7 and so on .. I have tried using the record macro feature and had no success. Any help will be greatly appreciated. Thanks

 

[Daroh]

Hi JPARKHURST,

Thanks for the reply

I enter data into 'Sheet 1' which is linked to 'sheet 2', column H (H7 - H23). When I enter the "new" data I want the previous from colimn H to move the left to column G (G7 - G23), and same when I enter the next data and so on.. I hope this is explained somewhat ok.

Thanks.

 

[JPARKHURST]

Yes, it is.

A few questions:

First and foremost, are you only wanting to record/save the last 7 days? Will you be deleting the data when it is sitting on A7 and it is time to enter more data?
Can you use a command button, or does it need to fire when a user clicks into a cell? (this will ALWAYS happen if so, so accidental movements will also fire it, button, IMO, would be recommended).

Here is the process as I see it:

You may click "Enter New" or select the sell Sheet1!H7.
Data in A7:A23 will be deleted
Data in B7:H23 will be copied to A7:G23 - Can we just copy values, or do you need to have formulas?
H7:H23 will be erased
Data may now be entered into Sheet1!H7 and will then shw up in Sheet2!H7.

Does that sound like the process we want?

 

[JPARKHURST]

Try this

Create a module and past eth code below in it.

Sub MoveIt()
 Dim rSource As Range
 Dim rDest As Range
 Dim rRemove As Range
 Dim wsSource As Worksheet
 
 'Worksheets are currently static, can be changed easily with input or finding them
 Set wsSource = Sheets("Sheet2")
 
 'Data ranges are static, so we are hard-coding them instead of searchign for them.
 
 Set rRemove = Range("A7:A23")
 rRemove.Clear
 Set rRemove = Nothing
 
 
 Set rSource = wsSource.Range("B7:H23")
 Set rDest = wsSource.Range("A7:G23")
 rDest.Value = rSource.Value
 Set rSource = Nothing
 Set rDest = Nothing
 
 Set rRemove = wsSource.Range("H7:H23")
 rRemove.Clear
 
 rRemove.Cells(1, 1).Formula = "=Sheet1!H7"
 rRemove.FillDown
 
 Set rSource = Sheets("Sheet1").Range("h7:h23")
 rSource.Clear
 rSource.Cells(1, 1).Select
 Set rSource = Nothing


  
End Sub

Create a button, attach the following code to it:

Private Sub CommandButton1_Click()    Call MoveIt
End Sub

HTH,

Jon