Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
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" wrote: 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
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 "GoBucks" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
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. "dan dungan" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
Thank you JLG!! I will give this shot. Appreciate the help.
"JLGWhiz" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
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" wrote in message ... Thank you JLG!! I will give this shot. Appreciate the help. "JLGWhiz" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
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! "JLGWhiz" wrote: 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 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 "GoBucks" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
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!! "JLGWhiz" wrote: 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 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 "GoBucks" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros for copy and paste of a range
Please ignore this post...I added in the wrong thread.
"GoBucks" wrote: 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!! "JLGWhiz" wrote: 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 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 "GoBucks" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
event macros vs copy/paste | Excel Worksheet Functions | |||
copy and paste within macros | Excel Programming | |||
Problem with copy and paste together with macros | Excel Worksheet Functions | |||
Copy and Paste Macros | Excel Discussion (Misc queries) | |||
Copy & Paste Drop Down Box with Macros | Excel Programming |