Selecting entire rows contents
 

Guys,

First time on here & I'm a bit of a VBA newbie. I'm trying t
construct a pretty simple macro to scroll down through the contents o
a worksheet and where a given cell is "" to take the entire row
contents to another sheet. So far I'm up to here ...


RownumberEndWorksheet = 14
Do Until Sheets("All Transfer Dat
F24.89_3").Cells(RownumberEndWorksheet, 5) = ""
If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet
13) = "" Then

At this point I've nominated all the individual cells to be taken fro
"All Transfer Data F24.89_3" by specifyin
cells(RownumberEndWorksheet, applicable column ref). This is tediou
as there's lots. Then I look to the sheet to paste data & find th
next available line as follows ;

RownumberNext = 5
Do Until Sheets("Paste Data").Cells(RownumberNext, 2) = ""
RownumberNext = RownumberNext + 1
Loop

Worksheets("Paste Data").Cells(RownumberNext, 2) = VarBarcode

etc.



Please can someone show me how to select the entire contents of a ro
when I've found one whe

If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet
13) = ""

and then get the data in to the paste sheet.


I'm sorry this is probably childs play to someone with some experienc
but I'm struggling here. Much appreciated i
advance!!!!!!!!!!!!!!!!!!!

--
GB90018
-----------------------------------------------------------------------
GB900180's Profile: http://www.excelforum.com/member.php...fo&userid=3042
View this thread: http://www.excelforum.com/showthread.php?threadid=50190

Selecting entire rows contents
 

Try this (untested but compiled OK)

Sub Test()

Dim shtCopy As Worksheet
Dim shtPaste As Worksheet
Dim rowcopy As Long, numrows As Long

Set shtCopy = ThisWorkbook.Sheets("All Transfer Data F24.89_3")
Set shtPaste = ThisWorkbook.Sheets("Paste Data")

rowcopy = 14
numrows = shtCopy.Rows.Count

Do While shtCopy.Cells(rowcopy, 5).Value < ""

If shtCopy.Cells(rowcopy, 13).Value = "" Then

shtCopy.Rows(rowcopy).Copy _
Destination:=shtPaste.Cells(numrows,
2).End(xlUp).Offset(1, -1)

End If

rowcopy = rowcopy + 1
Loop


End Sub




Tim

"GB900180" wrote in
message ...

Guys,

First time on here & I'm a bit of a VBA newbie. I'm trying to
construct a pretty simple macro to scroll down through the contents of
a worksheet and where a given cell is "" to take the entire rows
contents to another sheet. So far I'm up to here ...


RownumberEndWorksheet = 14
Do Until Sheets("All Transfer Data
F24.89_3").Cells(RownumberEndWorksheet, 5) = ""
If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet,
13) = "" Then

At this point I've nominated all the individual cells to be taken from
"All Transfer Data F24.89_3" by specifying
cells(RownumberEndWorksheet, applicable column ref). This is tedious
as there's lots. Then I look to the sheet to paste data & find the
next available line as follows ;

RownumberNext = 5
Do Until Sheets("Paste Data").Cells(RownumberNext, 2) = ""
RownumberNext = RownumberNext + 1
Loop

Worksheets("Paste Data").Cells(RownumberNext, 2) = VarBarcode

etc.



Please can someone show me how to select the entire contents of a row
when I've found one whe

If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet,
13) = ""

and then get the data in to the paste sheet.


I'm sorry this is probably childs play to someone with some experience
but I'm struggling here. Much appreciated in
advance!!!!!!!!!!!!!!!!!!!!


--
GB900180
------------------------------------------------------------------------
GB900180's Profile:
http://www.excelforum.com/member.php...o&userid=30423
View this thread: http://www.excelforum.com/showthread...hreadid=501905

Selecting entire rows contents
 

Tim,

Cheers for the reply mate. I figured a solution myself as follows
works at the moment ...

Dim VarPaste, VarBarcode As String
Dim RownumberEndWorksheet, RownumberNext As Integer

RownumberEndWorksheet = 14
Do Until Sheets("All Transfer Data
F24.89_3").Cells(RownumberEndWorksheet, 5) = ""
'
'Select only those rows where a transfer form has not been returned
'
If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet,
13) = "" Then
'
'Define "VarPasteEntireRow"
'
VarPaste = Sheets("All Transfer Data
F24.89_3").Rows(RownumberEndWorksheet).EntireRow
'
'Find an empty row on the destination worksheet (exception report)
'
RownumberNext = 5
Do Until Sheets("Paste Data").Cells(RownumberNext, 5) = ""
RownumberNext = RownumberNext + 1
Loop
'
'Copy and paste the applicable data in to the desired format
'
Sheets("Paste Data").Rows(RownumberNext).EntireRow = VarPaste

End If
'
'Move to the next row on the "All Transfer Data F24.89_3" worksheet
'
RownumberEndWorksheet = RownumberEndWorksheet + 1
'
'Loop and end the sub
'
Loop
End Sub




My only remaining question is that if I remove the "VarBarcode" from my
declaring variables section I get an error and the macro debugs.
Guessing the VarPaste didn't ought to be string as am selecting a whole
row - should it be an object? It works at the moment & i need it pronto
so don't want to change anything? Suggestions? Cheers


--
GB900180
------------------------------------------------------------------------
GB900180's Profile: http://www.excelforum.com/member.php...o&userid=30423
View this thread: http://www.excelforum.com/showthread...hreadid=501905

Selecting entire rows contents
 

VarPaste is not a string: if you declare two variables on the same line like
this

Dim Var1, Var2 as String

then only Var2 is a string. By default Var1 is a variant-type (good choice
actually).
When you run your code it picks up the "value" of the copy range: this takes
the form of a 2-D array.

When you remove VarBarcode it has the effect of declaring VarPaste as
String, which is the wrong type for your needs.

Cheers,

Tim


"GB900180" wrote in
message ...

Tim,

Cheers for the reply mate. I figured a solution myself as follows
works at the moment ...

Dim VarPaste, VarBarcode As String
Dim RownumberEndWorksheet, RownumberNext As Integer

RownumberEndWorksheet = 14
Do Until Sheets("All Transfer Data
F24.89_3").Cells(RownumberEndWorksheet, 5) = ""
'
'Select only those rows where a transfer form has not been returned
'
If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet,
13) = "" Then
'
'Define "VarPasteEntireRow"
'
VarPaste = Sheets("All Transfer Data
F24.89_3").Rows(RownumberEndWorksheet).EntireRow
'
'Find an empty row on the destination worksheet (exception report)
'
RownumberNext = 5
Do Until Sheets("Paste Data").Cells(RownumberNext, 5) = ""
RownumberNext = RownumberNext + 1
Loop
'
'Copy and paste the applicable data in to the desired format
'
Sheets("Paste Data").Rows(RownumberNext).EntireRow = VarPaste

End If
'
'Move to the next row on the "All Transfer Data F24.89_3" worksheet
'
RownumberEndWorksheet = RownumberEndWorksheet + 1
'
'Loop and end the sub
'
Loop
End Sub




My only remaining question is that if I remove the "VarBarcode" from my
declaring variables section I get an error and the macro debugs.
Guessing the VarPaste didn't ought to be string as am selecting a whole
row - should it be an object? It works at the moment & i need it pronto
so don't want to change anything? Suggestions? Cheers


--
GB900180
------------------------------------------------------------------------
GB900180's Profile:
http://www.excelforum.com/member.php...o&userid=30423
View this thread: http://www.excelforum.com/showthread...hreadid=501905