I am copying over information from Sheet1 to Sheet2. I do subtotals in
Sheet1 but do not know how many items i need to calculate so I leave the
calculation in cell A200. I need to be able to copy over the information
from Sheet1 without copying over the empty rows. I also need to copy over
the value from the calculation. I need to copy over more than one subtotal
so when I am pasting the information into Sheet2 I need the program to search
for the next free row before pasting. Can anyone help me with this?

Sub copyData()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng2 as Range, j as Long, i as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set rng2 = sh2.cells(rows.count,1).End(xlup)(2)
j = 1
for i = 1 to 200
if isnumeric(sh1.cells(i,0)) then
if sh1.cells(i,0) 0 then
rng2(j).Value = sh1.cells(i,0).Value
j = j + 1
end if
end if
Next i
End sub

--
Regards,
Tom Ogilvy

"enyaw" wrote:

I am copying over information from Sheet1 to Sheet2. I do subtotals in
Sheet1 but do not know how many items i need to calculate so I leave the
calculation in cell A200. I need to be able to copy over the information
from Sheet1 without copying over the empty rows. I also need to copy over
the value from the calculation. I need to copy over more than one subtotal
so when I am pasting the information into Sheet2 I need the program to search
for the next free row before pasting. Can anyone help me with this?

Tom I am getting an error when I try to run this program.
It is highlighting this line:
If IsNumeric(Sh1.Cells(i, 0)) Then


"Tom Ogilvy" wrote:

Sub copyData()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng2 as Range, j as Long, i as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set rng2 = sh2.cells(rows.count,1).End(xlup)(2)
j = 1
for i = 1 to 200
if isnumeric(sh1.cells(i,0)) then
if sh1.cells(i,0) 0 then
rng2(j).Value = sh1.cells(i,0).Value
j = j + 1
end if
end if
Next i
End sub

--
Regards,
Tom Ogilvy

"enyaw" wrote:

I am copying over information from Sheet1 to Sheet2. I do subtotals in
Sheet1 but do not know how many items i need to calculate so I leave the
calculation in cell A200. I need to be able to copy over the information
from Sheet1 without copying over the empty rows. I also need to copy over
the value from the calculation. I need to copy over more than one subtotal
so when I am pasting the information into Sheet2 I need the program to search
for the next free row before pasting. Can anyone help me with this?

I can't imagine why I put a zero in there, but it should be a 1 in all cases

Sub copyData()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng2 as Range, j as Long, i as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set rng2 = sh2.cells(rows.count,1).End(xlup)(2)
j = 1
for i = 1 to 200
if isnumeric(sh1.cells(i,1)) then
if sh1.cells(i,1) 0 then
rng2(j).Value = sh1.cells(i,1).Value
j = j + 1
end if
end if
Next i
End sub

--
Regards,
Tom Ogilvy



"enyaw" wrote:

Tom I am getting an error when I try to run this program.
It is highlighting this line:
If IsNumeric(Sh1.Cells(i, 0)) Then


"Tom Ogilvy" wrote:

Sub copyData()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng2 as Range, j as Long, i as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set rng2 = sh2.cells(rows.count,1).End(xlup)(2)
j = 1
for i = 1 to 200
if isnumeric(sh1.cells(i,0)) then
if sh1.cells(i,0) 0 then
rng2(j).Value = sh1.cells(i,0).Value
j = j + 1
end if
end if
Next i
End sub

--
Regards,
Tom Ogilvy

"enyaw" wrote:

I am copying over information from Sheet1 to Sheet2. I do subtotals in
Sheet1 but do not know how many items i need to calculate so I leave the
calculation in cell A200. I need to be able to copy over the information
from Sheet1 without copying over the empty rows. I also need to copy over
the value from the calculation. I need to copy over more than one subtotal
so when I am pasting the information into Sheet2 I need the program to search
for the next free row before pasting. Can anyone help me with this?

Tom

How can I get this program to copy over more than just one column?
I also need to copy over the format of the text.


"Tom Ogilvy" wrote:

I can't imagine why I put a zero in there, but it should be a 1 in all cases

Sub copyData()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng2 as Range, j as Long, i as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set rng2 = sh2.cells(rows.count,1).End(xlup)(2)
j = 1
for i = 1 to 200
if isnumeric(sh1.cells(i,1)) then
if sh1.cells(i,1) 0 then
rng2(j).Value = sh1.cells(i,1).Value
j = j + 1
end if
end if
Next i
End sub

--
Regards,
Tom Ogilvy



"enyaw" wrote:

Tom I am getting an error when I try to run this program.
It is highlighting this line:
If IsNumeric(Sh1.Cells(i, 0)) Then


"Tom Ogilvy" wrote:

Sub copyData()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng2 as Range, j as Long, i as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set rng2 = sh2.cells(rows.count,1).End(xlup)(2)
j = 1
for i = 1 to 200
if isnumeric(sh1.cells(i,0)) then
if sh1.cells(i,0) 0 then
rng2(j).Value = sh1.cells(i,0).Value
j = j + 1
end if
end if
Next i
End sub

--
Regards,
Tom Ogilvy

"enyaw" wrote:

I am copying over information from Sheet1 to Sheet2. I do subtotals in
Sheet1 but do not know how many items i need to calculate so I leave the
calculation in cell A200. I need to be able to copy over the information
from Sheet1 without copying over the empty rows. I also need to copy over
the value from the calculation. I need to copy over more than one subtotal
so when I am pasting the information into Sheet2 I need the program to search
for the next free row before pasting. Can anyone help me with this?

Sub copyData()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng2 as Range, j as Long, i as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set rng2 = sh2.cells(rows.count,1).End(xlup)(2)
j = 1
for i = 1 to 200
if isnumeric(sh1.cells(i,1)) then
if sh1.cells(i,1) 0 then
sh1.cells(i,1).entirerow.copy
rng2(j).Pastespecial xlValues
rng2(j).Pastespecial xlFormats
j = j + 1
end if
end if
Next i
End sub

--
Regards,
Tom Ogilvy



"enyaw" wrote:

Tom

How can I get this program to copy over more than just one column?
I also need to copy over the format of the text.


"Tom Ogilvy" wrote:

I can't imagine why I put a zero in there, but it should be a 1 in all cases

Sub copyData()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng2 as Range, j as Long, i as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set rng2 = sh2.cells(rows.count,1).End(xlup)(2)
j = 1
for i = 1 to 200
if isnumeric(sh1.cells(i,1)) then
if sh1.cells(i,1) 0 then
rng2(j).Value = sh1.cells(i,1).Value
j = j + 1
end if
end if
Next i
End sub

--
Regards,
Tom Ogilvy



"enyaw" wrote:

Tom I am getting an error when I try to run this program.
It is highlighting this line:
If IsNumeric(Sh1.Cells(i, 0)) Then


"Tom Ogilvy" wrote:

Sub copyData()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng2 as Range, j as Long, i as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set rng2 = sh2.cells(rows.count,1).End(xlup)(2)
j = 1
for i = 1 to 200
if isnumeric(sh1.cells(i,0)) then
if sh1.cells(i,0) 0 then
rng2(j).Value = sh1.cells(i,0).Value
j = j + 1
end if
end if
Next i
End sub

--
Regards,
Tom Ogilvy

"enyaw" wrote:

I am copying over information from Sheet1 to Sheet2. I do subtotals in
Sheet1 but do not know how many items i need to calculate so I leave the
calculation in cell A200. I need to be able to copy over the information
from Sheet1 without copying over the empty rows. I also need to copy over
the value from the calculation. I need to copy over more than one subtotal
so when I am pasting the information into Sheet2 I need the program to search
for the next free row before pasting. Can anyone help me with this?

Is there a way of changing the formula so it picks up text instead of copying
just the numbers?

"enyaw" wrote:

Tom

How can I get this program to copy over more than just one column?
I also need to copy over the format of the text.


"Tom Ogilvy" wrote:

I can't imagine why I put a zero in there, but it should be a 1 in all cases

Sub copyData()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng2 as Range, j as Long, i as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set rng2 = sh2.cells(rows.count,1).End(xlup)(2)
j = 1
for i = 1 to 200
if isnumeric(sh1.cells(i,1)) then
if sh1.cells(i,1) 0 then
rng2(j).Value = sh1.cells(i,1).Value
j = j + 1
end if
end if
Next i
End sub

--
Regards,
Tom Ogilvy



"enyaw" wrote:

Tom I am getting an error when I try to run this program.
It is highlighting this line:
If IsNumeric(Sh1.Cells(i, 0)) Then


"Tom Ogilvy" wrote:

Sub copyData()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng2 as Range, j as Long, i as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set rng2 = sh2.cells(rows.count,1).End(xlup)(2)
j = 1
for i = 1 to 200
if isnumeric(sh1.cells(i,0)) then
if sh1.cells(i,0) 0 then
rng2(j).Value = sh1.cells(i,0).Value
j = j + 1
end if
end if
Next i
End sub

--
Regards,
Tom Ogilvy

"enyaw" wrote:

I am copying over information from Sheet1 to Sheet2. I do subtotals in
Sheet1 but do not know how many items i need to calculate so I leave the
calculation in cell A200. I need to be able to copy over the information
from Sheet1 without copying over the empty rows. I also need to copy over
the value from the calculation. I need to copy over more than one subtotal
so when I am pasting the information into Sheet2 I need the program to search
for the next free row before pasting. Can anyone help me with this?