Macro to copy & paste-special-values data to selected worksheets

[tomhelle]

I have a workbook containing worksheets called “List-1â€, “List-2â€, “List-3â€,
“List-4†and many other worksheet “templatesâ€. I need a macro to perform the
following:

1. On any of the particular worksheet templates, copy the data on the
entire row for all rows within the range A1:H50 only if the value for that
row in column H is greater than 0, and then “paste special values†the data
on the worksheet called “List-1†starting at cell A1. In other words, for any
row within the range that has a value in column H that is greater than 0, the
entire row of data in column A-H will be copied and pasted (paste special
values) on “List-1â€.

2. Perform the same routine as step 1 except select the data in cells
A51:H100 and paste-special-values on “List-2†starting at cell A1.

3. Perform the same routine as step 1 except select the data in cells
A101:H150 and paste-special-values on “List-3†starting at cell A1.

4. Perform the same routine as step 1 except select the data in cells
A151:H200 and paste-special-values on “List-4†starting at cell A1.

5. Return back to the original worksheet template, select cell A1, and
escape to eliminate any evidence of the range selection (dashed borders) that
was used to copy data in step 1-4.

I’ll use a button to activate the macro on each individual worksheet
template. When the user activates the macro on the first template worksheet,
the data will be copied, and pasted (paste special values) to each of the
lists as noted above. When the user activates the macro on any of the
subsequent templates, the data will copied and pasted (paste special values)
on each of the lists starting at the next empty row in column A. In other
words, I want the user to be able to continue to add data to “List-1â€,
“List-2â€, List-3†and “List-4†from any of the template worksheets.

I don’t have much experience with vba therefore, any help for a novice to
apply this would be greatly appreciated.

Thanks in advance,

Tom

 

[PY & Associates]

I have a workbook containing worksheets called “List-1”, “List-2”, “List-3”,
“List-4” and many other worksheet “templates”. I need a macro to perform the
following:

1.  On any of the particular worksheet templates, copy the data on the
entire row for all rows within the range A1:H50 only if the value for that
row in column H is greater than 0, and then “paste special values” the data
on the worksheet called “List-1” starting at cell A1. In other words,for any
row within the range that has a value in column H that is greater than 0,the
entire row of data in column A-H will be copied and pasted (paste special
values) on “List-1”.

2.  Perform the same routine as step 1 except select the data in cells
A51:H100 and paste-special-values on “List-2” starting at cell A1.

3.  Perform the same routine as step 1 except select the data in cells
A101:H150 and paste-special-values on “List-3” starting at cell A1.

4.  Perform the same routine as step 1 except select the data in cells
A151:H200 and paste-special-values on “List-4” starting at cell A1.

5.  Return back to the original worksheet template, select cell A1, and
escape to eliminate any evidence of the range selection (dashed borders) that
was used to copy data in step 1-4.

I’ll use a button to activate the macro on each individual worksheet
template. When the user activates the macro on the first template worksheet,
the data will be copied, and pasted (paste special values) to each of the
lists as noted above. When the user activates the macro on any of the
subsequent templates, the data will copied and pasted (paste special values)
on each of the lists starting at the next empty row in column A. In other
words, I want the user to be able to continue to add data to “List-1”,
“List-2”, List-3” and “List-4” from any of the template worksheets.

I don’t have much experience with vba therefore, any help for a novice to
apply this would be greatly appreciated.

Thanks in advance,

Tom

Air code:

for i=1 to 4
set rng=range("H" & (i-1)*50+1 & ":H" & (i*50))
for each c in rng
if c>0 then
c.entirerow.copy
worksheets("List-" & i).cells(rows.count,
1).end(xlup).offset(1).pastespecial
end if
next c
next i

Application.CutCopyMode = False
Range("A1").Select