Macros for copy and paste of a range

[GoBucks]

I am a novice to VB/Macros and was looking for some assistance. I would like
to run a couple of macros through command buttons on a worksheet. I have a
range of data (L6:BL155) with a header row (L5:BL5) consisting of work week
end dates (Fridays: L5 = 5/8/09 ---> BL5= 5/7/10).

I would like to have Macro #1 to do the following:

1) copy all of the values in the range M6:BL155 and paste in the range
L6:BK155
2) clear the contents of the cells in range BL6:BL155
3) adjust the header dates +7 days (L5=5/15/09, M5=5/22/09....BL5=5/12/10)

In summary, I am trying to roll forward the data range one week at a time
after clicking the button.


I would like to have Macro #2 to do the following:

1) for each row in the range (L5:BL5), find the last cell value in the row
(left to right) and copy that value all of the way back to the first cell in
the row of the range.

For example: If in row 10, cell P10 = 100 and Q10 thru BL10 is blank, then
copy 100 and paste from L10:O10. Then, look in Row 11 and execute the same
logic.

To me, this seems complex but hopefully is pretty straighforward. Any
assistance is greatly appreciated.

 

[dan dungan]

I'm not clear on a couple points:

<I would like to have Macro #1 to do the following:

<1) copy all of the values in the range M6:BL155 and paste in the
range
<L6:BK155

You want to copy the range and move it one column to the left?

<2) clear the contents of the cells in range BL6:BL155

Then you want to clear the last column (BL) of the original data?

<3) adjust the header dates +7 days (L5=5/15/09, M5=5/22/09....BL5=5</
12/10)

Then you want to add 7 to each date in your header row?

I'm wondering what that would do for you. Was the original data
entered under the wrong dates?

Do you want to do the every day, week or month?

I'd like to see your description of what you are trying to accomplish,
rather that how you want to accomplish it.

There a lots of references in the archives about copying and moving
ranges.

Have you searched the newsgroups for an answer?

Hope this helps

Dan

 

[JLGWhiz]

1) for each row in the range (L5:BL5), find the last cell value in the row
(left to right) and copy that value all of the way back to the first cell in
the row of the range.


Should this not be range("L6:BL155") ? L5:BL5 is row 5, which I believe you
said is your header row.

 

[GoBucks]

Sorry, yes the range is L6:BJ155. That was a typo on my part. Looking to
begin on row 5, Columns L thru BL, do the logic and then move to line 6, etc.
Also, forgot to mention that if a row has no values, do nothing and move to
next line. Hope this helps.

 

[JLGWhiz]

Create your buttons from the Control Tool Box so you can inser the code into
their code module by right clicking and selecting View Code.

Private Sub CommandButton1_Click()
ActiveSheet.Range("M5:BL155").Cut Range("L5")
ActiveSheet.Range("BL5").Value = Range("Bk5").Value + 1
End Sub


Private Sub CommandButton2_Click()
For i = 6 To 155
If ActiveSheet.Range("BL" & i) > "" Then
ActiveSheet.Range("L" & i & ":BK" & i) _
= ActiveSheet.Range("BL" & i).Value
Else
x = ActiveSheet.Range("BL" & i).End(xlToLeft).Value
eCol = ActiveSheet.Range("BL" & i).End(xlToLeft) _
.Offset(0, -1).Address
ActiveSheet.Range("L" & i & ":" & eCol) = x
End If
Next
End Sub

 

[JLGWhiz]

P.S. run the code on a copy of your file, in case it does not do what you
want, your original file will be intact. If it works like you want, then
install it permanently.

 

[GoBucks]

Thanks Dan. I'll respond to your ?'s.

1) Yes, basically, I trying to copy a range (M6:BL155) and move it on
column to the left (L6:BM155)
2) Then clear the last column of data (BL)
3) I was looking to have the column header (week end date) move along with
the corresponding data. I guess I should of included the header row in the
main range.

Just to summarize what I'm trying to accomplish is that I attempting to
replicate / enhance an existing Capdacity Planning report. The report is a
rolling 12-month data set which consists of allocated %'s per work week for
individual consultants. I was envisioning a user (HR Manager) running this
macro on a weekly basis to roll the data set forward so that the first column
in the range is the current week. I was looking at keeping a specified # of
columns in a range for the fact I have to add a lot conditional formatting
formulas to assist to make more user-friendly. The set # of columns makes it
easier for me to apply these formulas.

For my other macro, I was looking for a way to make the data set more
consistent. The users of this report only put a % value in cell for a week
end date (i.e. 5/25) and ASSUME all of the prior week values are also the
same % from current week to this date. For what I'm trying to do, I need all
of the values input in the cells. So if a consultant is going to 100%
utilized from 5/8 to 5/29, I need to have 100 input in Column L (current week
= 5/8) thru Column O (5/29). There are over 150 consultiants tracked on this
sheet, so the user does not want to input a lot of the cells just the cell
where the consultant will be done with a project.

Hope this helps.

 

[GoBucks]

Thank you JLG!! I will give this shot. Appreciate the help.

 

[JLGWhiz]

Just noticed that I only increased the date by one day, Use this modified
version to get a full week.

Private Sub CommandButton1_Click()
ActiveSheet.Range("M5:BL155").Cut Range("L5")
ActiveSheet.Range("BL5").Value = Range("Bk5").Value + 7
End Sub

 

[GoBucks]

JLGWhiz ...Thanks again! This is really close to working. I found a couple of
issues that hopefully are correctable.

1) If a row does NOT have any values in the range L:BL, then the code copied
the value in Column K and pastes it both Columns L & J respectively.

2) If a row does NOT have any values from A:BL, then a Run-time error '1004'
occurs (Application-def or object-def error). In the range of L6:BL155, there
will be some rows with no values to allow the user to input new info as time
goes by.

Is this fixable?? Thx!

 

[GoBucks]

JLGWhiz...After doing some testing, I found one issue with the code for the
CommandButton2 and was wondering how to fix. The code works great unless
whenever a value is input in Column L, it updates the cell to the left in
Column K. Any other time a value is placed in Columns M through BL, it
updates only back to L. Is this correctable?? Thx!!

 

[GoBucks]

Please ignore this post...I added in the wrong thread.