Alter Code

[vac]

Hi all,
How do I alter the following code to make it do the following:
Copy only columns A:D instead of all row
Search through more than 1 worksheet ie sheet2, sheet3, sheet4 etc

Sub Export()
Sheets("Sheet2").Select
Columns("A").SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Hidden = True
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy Sheets("Sheet1").Range("A1")
Cells.Rows.Hidden = False
Range("A1").Select
End Sub

Many Thanks

 

[erik.van.geit]

Hi, I get runtime error 1004
with this line
.Resize(, 4).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)(2)

Thanks

what is the error decription ?

what could be wrong except for the "Sheet1" ?
I already asked you: do you really have a sheet called "Sheet1" ?

I'm sure Dan has tested his code, so I did with mine ...

Dan, you learned me something: the Resize-arguments are optional that's good news for me

 

[HalfAce]

Most welcome. Glad you got it working.

 

[erik.van.geit]

Most welcome. Glad you got it working.

javascript.emoticon "SWEAT"
yes this was a fassssssssst thread

you're welcome !

 

[vac]

Thanks Guys

 

[vac]

I need to change this slightly, how do I stop it copying the headers of sheets ?
Thanks

Sub ExportThisworks()
Dim Ws As Worksheet
For Each Ws In ThisWorkbook.Sheets
If Ws.Name <> ("Sheet1") Then
On Error Resume Next
With Ws
.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
With .Columns("A").CurrentRegion
.Resize(, 3).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)(2)
End With
.Cells.Rows.Hidden = False
End With
Err.Clear
End If
Next Ws

End Sub

 

[erik.van.geit]

The mind of an Excel-lover is full of structure. It loves reading code which is indented: analysing is quicker: so every effort can go to the problem itself...

To let the code stay indented do the following.
Click "reply"
write your message
select your code
click the "Code"-button
(or Click code button, paste your code, click "Code" button again.)
Submit

A real relief for those who try to help you !

your question:
this is the copy part

.Resize(, 3).SpecialCells(xlCellTypeVisible).Copy _ 
Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)(2)

do you mean not to copy the first row ??
take a look at Offset in the helpfiles

.Offset(1, 0).Resize(.Rows.Count - 1, 3).SpecialCells(xlCellTypeVisible).Copy _ 
Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)(2)

typed off-hand and untested, but should be close

kind regards,
Erik

 

[vac]

Many Thanks Eric, I will take a look.

 

[vac]

Thanks Eric, but it seems not to be copying all the rows that it should. Have looked at the help files but cant see why its not doing it. What is the .Rows.Count - 1, part doing?

Thank you
vac

 

[erik.van.geit]

Thanks Eric, but it seems not to be copying all the rows that it should. Have looked at the help files but cant see why its not doing it. What is the .Rows.Count - 1, part doing?

Thank you
vac

was it copying all the rows before you changed the code ?
.Offset(1, 0).Resize(.Rows.Count - 1, 3)
"select" range one row down
resize range to one row less
.Rows.Count does exactly what it says: count the rows "within the range"
this "within the range" is due to the DOT
Resize(.Rows.Count - 1, 3)

best regards,
Erik

 

[vac]

Sub ExportThisworks()
Dim Ws As Worksheet
For Each Ws In ThisWorkbook.Sheets
  If Ws.Name <> ("Sheet1") Then
    On Error Resume Next
    With Ws
      .Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
      With .Columns("B").CurrentRegion
        .Resize(, 3).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)(2)
      End With
      .Cells.Rows.Hidden = False
    End With
  Err.Clear
  End If
Next Ws

End Sub

Ok, have now finally worked out that if there is data in column A (Its looking at column B) thats how it is fowling up.
Is there a way to stop it looking in all the sheets, but only in the ones want it to, eg sheets 10-20
Any Ideas
Thanks