Selecting entire rows contents

[GB900180]

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 where:

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!!!!!!!!!!!!!!!!!!!

 

[Tim Williams]

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]

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

 

[Tim Williams]

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