Macro problem help please.

[Nevyn]

Hi all you brain boxes out there. Need some help please. I have a book with
about 10 different sheets in it. What I am after is a way to copy the
information from a worksheet row to another worksheet without going back and
forward cutting and pasting. The macro i was trying to use was this,
Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+a
'
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("POrder").Select
ActiveCell.Offset(-4, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
Sheets("Photo Stock ").Select
ActiveCell.Offset(1, 4).Range("A1").Select
End Sub


Can some kind person tell me whatI am doing wrong besides banging my head
off the pc lol.

 

[Bernie Deitrick]

Sub Macro3()
ActiveCell.EntireRow.Copy _
Sheets("POrder").Cells(Rows.Count, 1).End(xlUp)(2).EntireRow
End Sub

Will copy the entire row of the activecell to the bottom of sheet POrder....

HTH,
Bernie
MS Excel MVP

 

[FSt1]

hi
you haven't given enough info to make a good assessment.
this line....
ActiveCell.Offset(-4, 0).Rows("1:1").EntireRow.Select
how did you determine the activecell on the other sheet??

the activecell exists only on the activesheet and if you don't want to be
flopping back and forth, then you will have to find another way to tell excel
where that cell is without selecting the sheet and cell.
here is a ditty that might show how it may work but the above line is the
the big question. this code just puts the copied data in the next row of the
other sheet which may not be what you want.

Sub copyit()
Dim r As Range
Dim ro As Range
Set r = ActiveCell
Set ro = Sheets("sheet3").Range("A65000").End(xlUp).Offset(1, 0) 'change
r.EntireRow.Copy Destination:=ro
r.Offset(1, 4).Select ' this will change the activecell

End Sub

regards
FSt1

 

[JLatham]

Based on what you wrote in your other request for help about this (please,
just post questions in one place - makes everything easier for everyone
involved), and what you wrote here, I came up with this code:

Notice that I named it Macro3, so you can just copy this code, go into the
VB editor where you now have Macro3 and paste this code over all you
currently have, that'll allow it to work just like it does now, associated
with the click of a button I presume you've already got on the sheet?

Sub Macro3()
'copies the current row on a sheet to
'just below the last entry on a sheet
'named POrder
'Limitation: Only a single row may
'be selected when the macro is run,
'although you can have more than one
'cell in that row selected.
If Selection.Rows.Count > 1 Then
'don't know what to do if multiple
'rows are selected, so do nothing
Stop
Exit Sub
End If
ActiveCell.EntireRow.Copy
Worksheets("POrder").Range("A" & Rows.Count). _
End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
'if you want to jump to A1 on the
'active sheet then
Range("A1").Select
End Sub