Sheet Names?

[sarlo00]

Are sheets numbered like rows and columns are?

I want to take the first row of the first sheet, copy it to the last row of the sheet, and then delete the first row. Then do the same for all sheets of the workbook.

So can I declare a variable for the sheet number (SheetNum) and run a loop (SheetNum+1) until the end of the workbook?

How do I make sure the loop stops at the last sheet?

Thanks.

 

[Jonmo1]

Something like this should get you started

For Each ws in Worksheets

    ws.Range("A1").Copy Destination:=ws.Range("B1")

Next ws

Hope that helps..

 

[VoG]

Try

Sub test()
Dim ws As Worksheet, LR As Long
For Each ws In ThisWorkbook.Worksheets
    With ws
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        With .Rows(1)
            .Copy Destination:=.Range("A" & LR + 1)
            .Delete
        End With
    End With
Next ws
End Sub

 

[sarlo00]

It did it for the first sheet then:

Run-time error '1004':
Select method of Range class failed

Code:

Sub Macro1()
'
' Macro1 Macro
'

'
For Each ws In Worksheets
ws.Rows("1:1").Select
Selection.Cut
ActiveCell.SpecialCells(xlLastCell).Select
Selection.End(xlToLeft).Select
ActiveSheet.Paste
ws.Rows("1:1").Select
Selection.Delete Shift:=xlUp
Next ws

End Sub

 

[VoG]

Try the code that I posted - it worked for me.

 

[sarlo00]

VoG, that worked perfectly. Would you please help me understand how?

Sub test()

'This just shows the start of the macro?

Dim ws As Worksheet, LR As Long

'Declares the variables?


For Each ws In ThisWorkbook.Worksheets

'Is "ThisWorkbook" built into VBA for Excel?
'Why do you need the "For Each"? Is that the way to stop your code at the end of the workbook without actually incrementing ws by one to the end?

With ws
LR = .Range("A" & Rows.Count).End(xlUp).Row

'"ws.Range("A"& Rows.Count)" is the first field in the last record? What does End(xlUp).Row do?

With .Rows(1)
.Copy Destination:=.Range("A" & LR + 1)
.Delete

'I don't have to select the destination to paste to it? That's nice!

End With
End With
Next ws
End Sub

I understand if you don't have time to answer these basic coding questions. Thank you for the code I will definitely apply parts of it to other codes that I have.

 

[VoG]

I'm more than happy to oblige - obviously you don't want to be spoon-fed answers which is good. See the commented code below - if you need more explanation please ask

Sub test()
'declare variables
Dim ws As Worksheet, LR As Long
'this says for each sheet in the workbook that this code resides in
For Each ws In ThisWorkbook.Worksheets
'by saying With ws every statement in the With / End With loop is referring to that object (ws in this case)
    With ws
'capture the last row
        LR = .Range("A" & Rows.Count).End(xlUp).Row
'for row 1
        With .Rows(1)
'we copy it and bung the result into the first free row
            .Copy Destination:=.Range("A" & LR + 1)
'then delete row 1
            .Delete
        End With
    End With
Next ws
End Sub

 

[sarlo00]

Do you have to put ".Worksheets" after "ThisWorkbook"?
What does ".End(xlUp).Row" do? Or, better put, how exactly does this line of code work:

LR = .Range("A" & Rows.Count).End(xlUp).Row

I understand that LR is the variable and ws.Range("A"&Rows.Count) would be the last row of data.(?) But, what does that last part do?