Selecting all rows of data that have info in

[dpcp]

Hi all,

I am having some trouble adapting the following code to select rows of data:

Sub CallData()
'
' CallData Macro
'

'
    Sheets("Ignore-this-sheet").Select
    Rows("1:1").Select
    Selection.Copy
    Windows("Master Template.xls").Activate
    Sheets("extracts").Select
    Rows("3:3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown

    
    
End Sub

Originally, it would have been enough to pull the top row of data from the "Ignore-this-sheet" worksheet.

Now, I need to adapt it.

Basically, how would I go about programming something like this:
select row 1 - copy, paste.
check if there is any info in row 2.
if there is info in row 2, then
select row 2 - copy, paste.
check if there is any info in row 3...
and so on.

I hope this makes sense.

Many thanks.

DP

[wamp]

try this:
it checks the last used row in "extractcs" sheet, then ammeds each row from sheet "ignore this sheet" that has a cell that contains value.

Sub CallData()

For Each cell In Sheets("Ignore-this-sheet").UsedRange
If cell.Value <> vbNullString Then
If cell.Row <> r Then

lastrow = Sheets("extracts").Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row
cell.EntireRow.Copy Destination:=Sheets("extracts").Rows(lastrow)

r = cell.Row
End If

End If

Next
  
End Sub

[dpcp]

Hi wamp

Thanks for the help

I get an error message:

Run-time error '9':
Subscript out of range

lastrow = Sheets("extracts").Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row

Perhaps this is because of the way my worksheets work -
I have worksheets in which the data is collected.
Then this data gets pulled into a separate Excel workbook, a "master" if you like (called "Master Template", and it is in the master file that all my macros are stored.

Maybe I just need to specify that it should be in the Master Template workbook?

[wamp]

if you get "subscript out of range" on the posted code, are you sure the sheetname is "extracts" ?

if you use another workbook, then I would change the code so it copies your relevant data to a temporary sheet, then copy it to the 'master' workbook or similar (a lot easier)

It would be a lot easier if you could you post an example of your workbook(s)?
and also in the workbooks point out what you're trying to achieve? (what goes where)

[dpcp]

Ok, still having no success, I am confused as to where to put a sort of "activate Master Template window" and then "select extracts worksheet", here is the code:

Sub CallData()
'
' CallData Macro
'

'

For Each cell In Sheets("Ignore-this-sheet").UsedRange
If cell.Value <> vbNullString Then
If cell.Row <> r Then

lastrow = Sheets("extracts").Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row
cell.EntireRow.Copy Destination:= Windows("Master Template.xls") .Sheets("extracts").Rows(lastrow)

r = cell.Row
End If

End If

Next
  
End Sub

But I don't know how to edit the code properly....

[dpcp]

OK I must've been typing my reply when you were typing yours, I'll add my workbooks in the next post. thanks.

[dpcp]

Activity Survey 2010 ex.xls

Master Template ex.xls


The Activity Survey is what is being sent out for people to fill in. in the "Ignore-this-sheet" worksheet, their responses are put into one single row of data.

This row of data is then copied and pasted into the "Master Template", on which there is only one sheet titled "extracts".

The issue is that sometimes the surveys come back with three or four (or more) responses, and the original macro that i'd written only collects the first row of the data in the "ignore-this-sheet" of the surveys.

Since we are expecting about 400 responses, it would be a lot easier if the macro could pick up all relevant rows of data in the "ignore" sheet and paste them into the Master Template.

Many thanks for all the help.

DP

[wamp]

how about this then:
assumes that there is a value in A1, and also that the sheet-names are static.
copies the -values- from the form to the masterpage.
(code to be put in a module in the masterpage)

Sub CallData()
'
' CallData Macro
'

'
Application.ScreenUpdating = False

Dim mp As String
Dim survey As String
survey = "Activity Survey 2010 ex.xls"
mp = "Master Template ex.xls"  ' could be "ThisWorkbook.Name" if the code is always started in the masterpage

Windows(survey).Activate

For Each cell In Sheets("Ignore-this-sheet").UsedRange
If cell.Row <> r Then
If cell.Value <> vbNullString Or cell.Value <> 0 Then


cell.EntireRow.Copy

Windows(mp).Activate
If Range("A2").Value <> vbNullString Then
Range("A1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
Else
Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
End If

Windows(survey).Activate
r = cell.Row
End If

End If

Next
  
Application.ScreenUpdating = True

  
End Sub

[dpcp]

The thing is, the Master Template has a macro which is basically used to open up all Excel files in a specified folder :

Sub ProcessAll()
    Dim Wb As Workbook, sFile As String, sPath As String
    Dim itm As Variant
    Dim strFileNames  As String
     
    sPath = "C:\Test1\"
     
     '    Retrieve the current xl files in directory
    sFile = Dir("C:\Test1\" & "*.xls")
    Do While sFile <> ""
        strFileNames = strFileNames & "," & sFile
        sFile = Dir()
     Loop
     
     '  Open each file found
    For Each itm In Split(strFileNames, ",")
        If itm <> "" Then
            Set Wb = Workbooks.Open(sPath & itm)
            Call CallData 'this runs my macro from above
            Wb.Close True
        End If
    Next itm
     
End Sub

And so the CallData macro should only be about copying and pasting, a bit like this I suppose:

Sub CallData()
'
' CallData Macro
'

'

For Each cell In Sheets("Ignore-this-sheet").UsedRange
If cell.Row <> r Then
If cell.Value <> vbNullString Or cell.Value <> 0 Then


cell.EntireRow.Copy

Windows("Master Template ex.xls").Activate
If Range("A2").Value <> vbNullString Then
Range("A1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
Else
Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
End If

Windows(survey).Activate
r = cell.Row
End If

End If

Next
  
Application.ScreenUpdating = True

  
End Sub

But the problem with the above CallData macro code is the

Windows(survey).Activate
r = cell.Row
End If

bit I suppose, which tells it to go back to the open survey window. Ideally, as in the ProcessAll macro, the xls files in a single folder should be opened, the correct rows copied over, and then the files closed. All done one by one.

[dpcp]

Ok, I have adjusted my "CallData" macro to the following:

Sub CallData()
'
' CallData Macro
'

'
    Sheets("Ignore-this-sheet").Select
    FinalRow = Range("A65536").End(xlUp).Row
    Range("A1:DP & FinalRow").Select
    Selection.Copy
    Windows("Master Template 2.xls").Activate
    Sheets("extracts").Select
    Rows("3:3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
 
    
End Sub

But I get an error msg:

Run-time error '1004':

Method 'Range' of object '_Global' failed

and the debugger points to the line:

Range("A1:DO & FinalRow").Select

Any suggestions? many thanks!

[dpcp]

Anyone? Help much appreciated!

[dpcp]

Hello,

just to say I found a solution, and here is the code in case anyone else has a similar issue:

    Sheets("Ignore-this-sheet").Select
    Range(Selection, Cells(1)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy

This ought to select right up to the last cell used in your worksheet, in the bottom-right so to speak.

DP