Sub copydata()
Dim sh1 as worksheet, sh2 as worksheet
Dim numrows as string
sh1 = Workbooks("Book1.xls").Worksheets("sheet1")
sh2 = Workbooks("Book2.xls").Worksheets("Sheet1")
numrows = Application.InputBox("Enter number of rows")
if isnumber(numrows) then
j = 20
for i = 1 to int(numrows)
sh2.Cells(j,"A").Value = sh1.Cells(j,"E").Value
sh3.Cells(j+1,"A").Value = sh1.Cells(j,"T").Value
sh2.Cells(j,"C").Value = sh1.Cells(j,"Y").Value
sh2.Cells(j+1,"C").Value = sh1.Cells(j,"AK").Value
j = j + 2
next
end if
End sub


--
Regards,
Tom Ogilvy


"Hawkdriver" wrote in message
...
Thank you tom for replying:
After trying to work with your suggestion here is the rest of my problem:
All the cells in wb 1 & 2 are merged rows and columns so i am working with
a
range instead of a single cell. The starting row number in wb1 where the
data is coming from changes and is not always the same (another input box
for
start row?). Workbook 2 ranges will always be the same. The macro is in
my
personal.xls workbook so i was trying to use workbooks(2) & (3) to move
between the sheets. The cells are merged rows of 2 like-(E20:G21) here is
what the ranges would look like in the first row of wb1:
(E20:S21) & (T20:X21)-this is what i want merged in (A20:A21)of wb2 and
(Y20:AJ21) & (AK20:AN21) in (C20:C21) in wb2. This would be "1 row" worth
of
data.

Many thanks

Jason

"Tom Ogilvy" wrote:

Sub copydata()
Dim sh1 as worksheet, sh2 as worksheet
Dim numrows as string
sh1 = Workbooks("Book1.xls").Worksheets("sheet1")
sh2 = Workbooks("Book2.xls").Worksheets("Sheet1")
numrows = Application.InputBox("Enter number of rows")
if isnumber(numrows) then
for i = 1 to int(numrows)
sh2.Cells(i,1) = sh1.Cells(i,1).Text & " " & sh1.cells(i,2).Text
next
end if
End sub


Saying Merged cell is ambiguous. two cells are merged, 5 cells are
merged,
across rows, down columns? Where is cell1, where is cell2

in the example, it goes down column A and gets values from column A and
column B for each row up to numrows. Using the .Text property returns a
formatted string.
--
Regards,
Tom Ogilvy


"Hawkdriver" wrote:

I am trying to copy and paste from two different workbooks but I need
to
format the data before the paste.
Workbook1 has a varying range of rows that contains the data that I
need,
cell 1 is a merged cell with dropdown list of choices and contains data
that
looks like this:

Any Town USA
( gps coordinate )

And cell 2 - 24hr time:
14:50

I need to merge that data into workbook2 into a single cell so that the
data
looks like:
Any Town USA 14:50

I would also like to be able to input the number of rows in wb1 that
need to
be copied to wb2 with a txtbox at the beginning of the macro

Thanks

Tom, using your suggestions here is what i have:
Sub AAR()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim numrows As String
Dim startrow As String
Set sh1 = Workbooks(2).Worksheets(1)
Set sh2 = Workbooks(3).Worksheets("AAR")
startrow = Application.InputBox("Enter the first row number")
numrows = Application.InputBox("Enter number of legs")
If (numrows) 0 Then
j = Int(startrow)
k = 20
For i = 1 To Int(numrows)
sh2.Cells(k, 1) = sh1.Cells(j, "E").Text & " " & sh1.Cells(j, "T").Text
j = j + 2
k = k + 2
Next
End If
End Sub

I'm not sure of the syntax to formatt "sh1.Cells(j,"E").text which i only
want data that is left of the data that is in ( ). in other words i need to
fomat to keep only data left of the "(".

Thank you
Jason

"Tom Ogilvy" wrote:

Sub copydata()
Dim sh1 as worksheet, sh2 as worksheet
Dim numrows as string
sh1 = Workbooks("Book1.xls").Worksheets("sheet1")
sh2 = Workbooks("Book2.xls").Worksheets("Sheet1")
numrows = Application.InputBox("Enter number of rows")
if isnumber(numrows) then
j = 20
for i = 1 to int(numrows)
sh2.Cells(j,"A").Value = sh1.Cells(j,"E").Value
sh3.Cells(j+1,"A").Value = sh1.Cells(j,"T").Value
sh2.Cells(j,"C").Value = sh1.Cells(j,"Y").Value
sh2.Cells(j+1,"C").Value = sh1.Cells(j,"AK").Value
j = j + 2
next
end if
End sub


--
Regards,
Tom Ogilvy


"Hawkdriver" wrote in message
...
Thank you tom for replying:
After trying to work with your suggestion here is the rest of my problem:
All the cells in wb 1 & 2 are merged rows and columns so i am working with
a
range instead of a single cell. The starting row number in wb1 where the
data is coming from changes and is not always the same (another input box
for
start row?). Workbook 2 ranges will always be the same. The macro is in
my
personal.xls workbook so i was trying to use workbooks(2) & (3) to move
between the sheets. The cells are merged rows of 2 like-(E20:G21) here is
what the ranges would look like in the first row of wb1:
(E20:S21) & (T20:X21)-this is what i want merged in (A20:A21)of wb2 and
(Y20:AJ21) & (AK20:AN21) in (C20:C21) in wb2. This would be "1 row" worth
of
data.

Many thanks

Jason

"Tom Ogilvy" wrote:

Sub copydata()
Dim sh1 as worksheet, sh2 as worksheet
Dim numrows as string
sh1 = Workbooks("Book1.xls").Worksheets("sheet1")
sh2 = Workbooks("Book2.xls").Worksheets("Sheet1")
numrows = Application.InputBox("Enter number of rows")
if isnumber(numrows) then
for i = 1 to int(numrows)
sh2.Cells(i,1) = sh1.Cells(i,1).Text & " " & sh1.cells(i,2).Text
next
end if
End sub


Saying Merged cell is ambiguous. two cells are merged, 5 cells are
merged,
across rows, down columns? Where is cell1, where is cell2

in the example, it goes down column A and gets values from column A and
column B for each row up to numrows. Using the .Text property returns a
formatted string.
--
Regards,
Tom Ogilvy


"Hawkdriver" wrote:

I am trying to copy and paste from two different workbooks but I need
to
format the data before the paste.
Workbook1 has a varying range of rows that contains the data that I
need,
cell 1 is a merged cell with dropdown list of choices and contains data
that
looks like this:

Any Town USA
( gps coordinate )

And cell 2 - 24hr time:
14:50

I need to merge that data into workbook2 into a single cell so that the
data
looks like:
Any Town USA 14:50

I would also like to be able to input the number of rows in wb1 that
need to
be copied to wb2 with a txtbox at the beginning of the macro

Thanks

Tom, I figured it out!
Thanks for all your help

"Hawkdriver" wrote:

Tom, using your suggestions here is what i have:
Sub AAR()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim numrows As String
Dim startrow As String
Set sh1 = Workbooks(2).Worksheets(1)
Set sh2 = Workbooks(3).Worksheets("AAR")
startrow = Application.InputBox("Enter the first row number")
numrows = Application.InputBox("Enter number of legs")
If (numrows) 0 Then
j = Int(startrow)
k = 20
For i = 1 To Int(numrows)
sh2.Cells(k, 1) = sh1.Cells(j, "E").Text & " " & sh1.Cells(j, "T").Text
j = j + 2
k = k + 2
Next
End If
End Sub

I'm not sure of the syntax to formatt "sh1.Cells(j,"E").text which i only
want data that is left of the data that is in ( ). in other words i need to
fomat to keep only data left of the "(".

Thank you
Jason

"Tom Ogilvy" wrote:

Sub copydata()
Dim sh1 as worksheet, sh2 as worksheet
Dim numrows as string
sh1 = Workbooks("Book1.xls").Worksheets("sheet1")
sh2 = Workbooks("Book2.xls").Worksheets("Sheet1")
numrows = Application.InputBox("Enter number of rows")
if isnumber(numrows) then
j = 20
for i = 1 to int(numrows)
sh2.Cells(j,"A").Value = sh1.Cells(j,"E").Value
sh3.Cells(j+1,"A").Value = sh1.Cells(j,"T").Value
sh2.Cells(j,"C").Value = sh1.Cells(j,"Y").Value
sh2.Cells(j+1,"C").Value = sh1.Cells(j,"AK").Value
j = j + 2
next
end if
End sub


--
Regards,
Tom Ogilvy


"Hawkdriver" wrote in message
...
Thank you tom for replying:
After trying to work with your suggestion here is the rest of my problem:
All the cells in wb 1 & 2 are merged rows and columns so i am working with
a
range instead of a single cell. The starting row number in wb1 where the
data is coming from changes and is not always the same (another input box
for
start row?). Workbook 2 ranges will always be the same. The macro is in
my
personal.xls workbook so i was trying to use workbooks(2) & (3) to move
between the sheets. The cells are merged rows of 2 like-(E20:G21) here is
what the ranges would look like in the first row of wb1:
(E20:S21) & (T20:X21)-this is what i want merged in (A20:A21)of wb2 and
(Y20:AJ21) & (AK20:AN21) in (C20:C21) in wb2. This would be "1 row" worth
of
data.

Many thanks

Jason

"Tom Ogilvy" wrote:

Sub copydata()
Dim sh1 as worksheet, sh2 as worksheet
Dim numrows as string
sh1 = Workbooks("Book1.xls").Worksheets("sheet1")
sh2 = Workbooks("Book2.xls").Worksheets("Sheet1")
numrows = Application.InputBox("Enter number of rows")
if isnumber(numrows) then
for i = 1 to int(numrows)
sh2.Cells(i,1) = sh1.Cells(i,1).Text & " " & sh1.cells(i,2).Text
next
end if
End sub


Saying Merged cell is ambiguous. two cells are merged, 5 cells are
merged,
across rows, down columns? Where is cell1, where is cell2

in the example, it goes down column A and gets values from column A and
column B for each row up to numrows. Using the .Text property returns a
formatted string.
--
Regards,
Tom Ogilvy


"Hawkdriver" wrote:

I am trying to copy and paste from two different workbooks but I need
to
format the data before the paste.
Workbook1 has a varying range of rows that contains the data that I
need,
cell 1 is a merged cell with dropdown list of choices and contains data
that
looks like this:

Any Town USA
( gps coordinate )

And cell 2 - 24hr time:
14:50

I need to merge that data into workbook2 into a single cell so that the
data
looks like:
Any Town USA 14:50

I would also like to be able to input the number of rows in wb1 that
need to
be copied to wb2 with a txtbox at the beginning of the macro

Thanks