I am using vba in spreadsheet1 to open and count data from an excel
spreadsheet located online (spreadsheet2). Once that data is counted in
spreadsheet2, I want that value to be printed in spreadsheet1 where the macro
was initially run from. I have the following code so far, but it will
(obviously) print the data in spreadsheet2 rather than spreadsheet1:

Sub openn()
Dim Countt As Long
Workbooks.Open Filename:="sample filename.xls"

Countt = CountColor(Range("D4:GE4"), 45, False) +
CountColor(Range("D4:GE4"), 10, False) + CountColor(Range("d4:GE4"), 8, False)

Range("k53").Value = Countt

End Sub

How do I get it to put that value in spreadsheet1?

Sub openn()
Dim Countt As Long
Dim sh as worksheet

set sh = Activesheet
Workbooks.Open Filename:="sample filename.xls"

Countt = CountColor(Range("D4:GE4"), 45, False) +
CountColor(Range("D4:GE4"), 10, False) + CountColor(Range("d4:GE4"), 8, False)

sh.Range("k53").Value = Countt

End Sub

--
Regards,
Tom Ogilvy


"Jayne22" wrote:

I am using vba in spreadsheet1 to open and count data from an excel
spreadsheet located online (spreadsheet2). Once that data is counted in
spreadsheet2, I want that value to be printed in spreadsheet1 where the macro
was initially run from. I have the following code so far, but it will
(obviously) print the data in spreadsheet2 rather than spreadsheet1:

Sub openn()
Dim Countt As Long
Workbooks.Open Filename:="sample filename.xls"

Countt = CountColor(Range("D4:GE4"), 45, False) +
CountColor(Range("D4:GE4"), 10, False) + CountColor(Range("d4:GE4"), 8, False)

Range("k53").Value = Countt

End Sub

How do I get it to put that value in spreadsheet1?

Try using this

http://www.rondebruin.nl/copy7.htm

"Jayne22" wrote:

I am using vba in spreadsheet1 to open and count data from an excel
spreadsheet located online (spreadsheet2). Once that data is counted in
spreadsheet2, I want that value to be printed in spreadsheet1 where the macro
was initially run from. I have the following code so far, but it will
(obviously) print the data in spreadsheet2 rather than spreadsheet1:

Sub openn()
Dim Countt As Long
Workbooks.Open Filename:="sample filename.xls"

Countt = CountColor(Range("D4:GE4"), 45, False) +
CountColor(Range("D4:GE4"), 10, False) + CountColor(Range("d4:GE4"), 8, False)

Range("k53").Value = Countt

End Sub

How do I get it to put that value in spreadsheet1?

wow, thanks for your help! This site has been an excellent way to get my
questions answered since I am new to VBA.

"Tom Ogilvy" wrote:

Sub openn()
Dim Countt As Long
Dim sh as worksheet

set sh = Activesheet
Workbooks.Open Filename:="sample filename.xls"

Countt = CountColor(Range("D4:GE4"), 45, False) +
CountColor(Range("D4:GE4"), 10, False) + CountColor(Range("d4:GE4"), 8, False)

sh.Range("k53").Value = Countt

End Sub

--
Regards,
Tom Ogilvy


"Jayne22" wrote:

I am using vba in spreadsheet1 to open and count data from an excel
spreadsheet located online (spreadsheet2). Once that data is counted in
spreadsheet2, I want that value to be printed in spreadsheet1 where the macro
was initially run from. I have the following code so far, but it will
(obviously) print the data in spreadsheet2 rather than spreadsheet1:

Sub openn()
Dim Countt As Long
Workbooks.Open Filename:="sample filename.xls"

Countt = CountColor(Range("D4:GE4"), 45, False) +
CountColor(Range("D4:GE4"), 10, False) + CountColor(Range("d4:GE4"), 8, False)

Range("k53").Value = Countt

End Sub

How do I get it to put that value in spreadsheet1?