copy info in excell

May 1, 2009 at 16:25:34
Specs: Windows XP
I have an excel spreadsheet with several worksheets. I am importing data into the 1st worksheet and then performing formulas etc into another sheet where I have a macro to hide rows that don't match certain criteria. What I now want to do is take the information in this new sheet through to an order sheet, but without the hidden rows. There could be up to 1000 rows and the information could be in any of those rows, but I only want the filtered rows to show in the order sheet. Can this be done with another macro?
Thanks

See More: copy info in excell

Report •


#1
May 1, 2009 at 20:59:46
Sub CopyVisible()
'Determine Last Row based on Column A
 LastRow = Range("A" & Rows.Count).End(xlUp).Row
'Loop from Row 1 to Last Row
  For myRow = 1 To LastRow
'If the row isn't Hidden then copy it to Sheet 2
   If Cells(myRow, 1).EntireRow.Hidden = False Then
    newRow = newRow + 1
    Cells(myRow, 1).EntireRow.Copy Destination:=Sheets(2).Cells(newRow, 1)
   End If
  Next
End Sub


Report •

#2
May 3, 2009 at 16:07:52
Thanks for that, it seems to work, except for the fact that Row1 has headers.
I have tried to change the code to suit by changing the row to 2, but then it fails by saying the paste area is not the same as the copy area?
Any further help would be appreciated.

Report •

#3
May 3, 2009 at 16:13:39
re: I have tried to change the code to suit by changing the row to 2

Considering the fact that my code references rows in both sheets, it might help if you told us how you changed the code.


Report •

Related Solutions

#4
May 3, 2009 at 16:46:51
Sorry my fault:

Sub CopyVisible()
'Determine Last Row based on Column A
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'Loop from Row 2 to Last Row
For myRow = 2 To LastRow
'If the row isn't Hidden then copy it to Sheet 4
If Cells(myRow, 2).EntireRow.Hidden = False Then
newRow = newRow + 1
Cells(myRow, 2).EntireRow.Copy Destination:=Sheets(4).Cells(newRow, 2)
End If
Next
End Sub

Thanks


Report •

#5
May 3, 2009 at 17:16:13
You are not understanding the Cells property correctly.

The syntax is Cells(RowIndex,ColumnIndex)

Keep in mind that myRow and newRow are just variable names that I made up. By using the word Row as part of the variable name it helps me remember what the variable is used for.

In this case, myRow and newRow are the RowIndex values for the Cell property.

By changing the ColumnIndex to 2 you are telling VBA to paste an entire row but to start the paste in Column 2, which, for obvious reasons, won't work.

When doing the Copy (and checking the Visible property) it really doesn't matter what the ColumnIndex is set to because the use of .EntireRow overides it.

However, when pasting an EntireRow, you must start in Column 1.

Starting your loop with myRow = 2 is fine, but leave the ColumnIndex values at 1.

If you need to start the Paste on Sheet 4 in row 2, then set newRow = 1 before the loop, so that the first time it increments it increments to 2.



Report •


Ask Question