Copy cells from one sheet to the next available row on another?

[clarkie]

Hi Guys

I am trying to record a macro that will copy the information from one
row on one particular sheet (used daily) to the next available row on
another sheet.


Can anyone help. I'm fairly new at this but learning fast.


thanks

 

[Ken Johnson]

Hi clarkie,

You haven't supplied any details, so here's my way where the user makes
the decisions on the fly...

Public Sub RowToOtherSheet()
Dim rngDest As Range
Dim rngSubject As Range
Dim lngLastRow As Long
Dim lngLastColumn As Long

On Error GoTo CANCELLED 'handle Cancel press

'Row range to copy
Set rngSubject = Application.InputBox( _
prompt:="Select the leftmost cell of the row to copy.", _
Title:="Copy Row.", _
Default:=Selection.Address, _
Type:=8)
lngLastColumn = Cells(rngSubject.Row, _
Columns.Count).End(xlToLeft).Column
Set rngSubject = ActiveSheet.Range(rngSubject.Cells(1), _
Cells(rngSubject.Cells(1).Row, lngLastColumn))

'Sheet as destination
Set rngDest = Application.InputBox( _
prompt:="Click..." & vbNewLine & "1. Other sheet tab" & _
vbNewLine & "2. Any cell on that sheet" & vbNewLine & _
"3. OK", _
Title:="Destination?", _
Type:=8)

'Next available row on destination sheet relative to col A
With Worksheets(rngDest.Parent.Name)
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set rngDest = .Range(.Cells(lngLastRow, 1), _
.Cells(lngLastRow, rngSubject.Columns.Count))
End With

'transfer values
rngDest.Value = rngSubject.Value

'show that code has taken effect
'just delete next line if not necessary
Worksheets(rngDest.Parent.Name).Activate
CANCELLED:
End Sub


Ken Johnson

 

[Guest]

Ken I have a variation of this task. I converted a pdf to xls and each page
is now a tab. I have a list with 4 columns that is 54 pages long now in 54
tabs. Can I use or modify this code to combine that list back into one tab?

I started to cut and paste and it's just taking too long. I have to run
analysis on this data and create pivots and charts. I will really like it if
I can get my list back into one tab.