Copy Paste VBA

[RMXByker]

Good Morning Everyone,

I am working on my first VBA project here at work. I need the following.

Copy Data from Worksheet "WORKSHEET"
Range is ("B4:B33")

The data in this range are sum calculations. I only want to copy the value, not the calculation so a paste special seems to be needed.

Now onto where the values will be pasted to.

Worksheet "HRS BY WEEK"

Now comes the more difficult part for me...

I want the macro to look in cell range ("C5:C34"). If there is information there, go to ("D5:D34") and so forth. The first column within that range (5 to 34) that doesn't have information is going to be the paste location.


I thought this was going to be an easy VBA for me to be my first try but for some reason it just is not working for me. Any help is appreciated.

Thanks all,
RMXByker

 

[smartguy]

Use Record macro !!!

 

[RMXByker]

Tried, but apparently what is coming from Record is not functioning. Here is what record is giving me which is just the basic copy/paste function and for some reason its not functioning either.

Sub COPY()
'
' COPY Macro
' Macro recorded 4/1/2010 by RMXByker
'
Range("B4:B33").Select
Selection.COPY
Sheets("HRS BY WEEK").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[smartguy]

Try this !!!

Sub COPY()
'
' COPY Macro
' Macro recorded 4/1/2010 by RMXByker
'
Sheets("WORKSHEET").Select
Range("B4:B33").Select
Selection.COPY
Sheets("HRS BY WEEK").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[RMXByker]

Works great. Found it was actually my security level bugging out. Anyways, how do I the part now that if there is data there to move over one column?

Thanks again for your help,
RMX

 

[smartguy]

Change code range here

Range("C5").Select

 

[RMXByker]

I realized that was the location that needed to be edited but since I am new to VBA, I have no idea what code to put in there. Is it an IF statement and if so what is it?

 

[smartguy]

Try this :

Sub COPY()
'
' COPY Macro
' Macro recorded 4/1/2010 by RMXByker
'
Sheets("WORKSHEET").Select
Range("B4:B33").Select
Selection.COPY
Sheets("HRS BY WEEK").Select
Range("D5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[RMXByker]

That code will only paste the information in the D column. I believe I may not have made my needs clear.

The WORKSHEET is going to be filled out weekly. The data copied is going into a week column in HRS BY WEEK. What I would like is not to have to specify the week but just have the paste look to see IF there is any data in the first week. If so, move to the next until it finds that range empty.

 

[smartguy]

oh Ok.... I got Wait ...