copy and paste
 

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?

copy and paste
 

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?

copy and paste
 

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?

copy and paste
 

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?

copy and paste
 

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?

copy and paste
 

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?

copy and paste
 

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?

copy and paste
 

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 application.CountA(rows(i)) < 0 then
sh1.cells(i,1).entirerow.copy
rng2(j).Pastespecial xlValues
rng2(j).Pastespecial xlFormats
j = j + 1
end if
Next i
End sub

--
Regards,
Tom Ogilvy


"enyaw" wrote:

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?

copy and paste
 

Tom

I have vlookups in some of the columns and even if there is nothing in the
row it is still being copied over. Any way of skipping these rows?

"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 application.CountA(rows(i)) < 0 then
sh1.cells(i,1).entirerow.copy
rng2(j).Pastespecial xlValues
rng2(j).Pastespecial xlFormats
j = j + 1
end if
Next i
End sub

--
Regards,
Tom Ogilvy


"enyaw" wrote:

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?

copy and paste
 

Tom

I have vlookups in some of the columns and even if there is nothing in the
row it is still being copied over. Any way of skipping these rows?
Not all of the lookup rows will return a value and some of them have an N/A value in them. I hid the N/A value but the code is still picking up these rows with N/A in them. ny way of skipping these rows?

"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 application.CountA(rows(i)) < 0 then
sh1.cells(i,1).entirerow.copy
rng2(j).Pastespecial xlValues
rng2(j).Pastespecial xlFormats
j = j + 1
end if
Next i
End sub

--
Regards,
Tom Ogilvy


"enyaw" wrote:

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?

copy and paste
 

Sorry, I am tired of playing guess what my sheet looks like.

If you can figure out a specific criteria about what gets copied and what
doesn't, then I am willing to help, but

"guess again"
is getting old.

--
Regards,
Tom Ogilvy


"enyaw" wrote:

Tom

I have vlookups in some of the columns and even if there is nothing in the
row it is still being copied over. Any way of skipping these rows?
Not all of the lookup rows will return a value and some of them have an N/A value in them. I hid the N/A value but the code is still picking up these rows with N/A in them. ny way of skipping these rows?

"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 application.CountA(rows(i)) < 0 then
sh1.cells(i,1).entirerow.copy
rng2(j).Pastespecial xlValues
rng2(j).Pastespecial xlFormats
j = j + 1
end if
Next i
End sub

--
Regards,
Tom Ogilvy


"enyaw" wrote:

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?