Solved: Find last used cell, then insert new row & paste data from other worksheet

[lostin_space]

Hi, my first post here - so bear with me :bow:

I'm pulling across data from another worksheet in my workbook & pasting it into a blank worksheet. In order to ensure my copied data is placed in a blank / fresh area of my new worksheet, i'm looping through my newly pasted data to find the last cell.

When i've found my last cell, i need to insert a couple of blank lines before i paste my next lot of data. Can anyone tell me how to do this in VBa? (i seem to be going wrong with 'selection.insert shift :=xldown'

Here's my current code


Sub testing2()

Dim dumbo As String
Dim i As Integer

'the value to be auto-implanted into the next empty cell
dumbo = "this cell's been populated with the value of a variable"

'loop the formula
For i = 0 To 1

'keep going on errors
On Error Resume Next

'use the range within B column to check against & find the last sell, then select it
Range("rangeb").Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select '= "put any text in here"

'if we encounter an error, display a message
If Err.Number > 0 Then
MsgBox "all cells are used"
Err.Clear
End If

On Error GoTo 0

'activate the selected blank cell
Selection.Activate

'populate the selection with the valie of the dumbo variable
Selection.Value = dumbo
'do the next loop
Next i
Selection.insert Shift:=xlDown
End Sub

Thanks & Regards

Russ

[lucas]

without testing...

Selection.EntireRow.Insert Shift:=xlDown


is rangeb a named range?

[lostin_space]

Yes, it actually refers to column B.....

[lucas]

You must have a workbook you are testing. could you upload it to keep us from having to reproduce the file from scratch just to look at your problem please?

[lostin_space]

I can't i'm afraid - Corporate security lockdown! I had to request access to this site (and justify it to the Nth degree!)

Can you assist without? (sorry!)

[lucas]

is the sheet you wish to copy to the one that contains the range "rangeb"?
Sorry, I may be dense today but I don't see where you are pulling data from one sheet to another. This just puts the dummy info in whatever active cell you have at the moment on any sheet....

[lucas]

Hey Russ,
I'm not really following you on this but to address this part:


I'm pulling across data from another worksheet in my workbook & pasting it into a blank worksheet. In order to ensure my copied data is placed in a blank / fresh area of my new worksheet, i'm looping through my newly pasted data to find the last cell.


Try this sub and function. You will have to change it to suit your needs. Once it is copying correctly we can get the extra lines added. Hope this is what you need but I'm not sure from what you have posted so far:

'Search cells in column A of value 'HI' in uppercase only. Copy their entire row at the end
'of the current data in sheet 2.
Sub CopyCellsContainingString()
Dim RgToSearch As Range
Dim CharToFind As String
Dim RgDestination As Range
Dim rg As Range

'------ change here ------
Set RgToSearch = ActiveSheet.Range("A:A") 'search in A:A
CharToFind = "HI" 'Search for HI
Set RgDestination = ActiveWorkbook.Worksheets(2).Range("A65536") _
.End(xlUp).Offset(1, 0).EntireRow
'-------------------------

Set rg = FindAll(CharToFind, RgToSearch, xlValues, xlWhole, True, False)
If Not rg Is Nothing Then
rg.EntireRow.Copy RgDestination 'copy resulting rows in RgDestination
End If
End Sub

Public Function FindAll( _
What As Variant, _
Where As Range, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlPart, _
Optional MatchCase As Boolean = False, _
Optional MatchByte As Boolean = False) As Range
' 2002 - Sebastien Mistouflet
Dim ResultRg As Range
Dim rg As Range
Dim firstAddress As String

With Where
Set rg = .Find(What, LookIn:=LookIn, LookAt:=LookAt, _
MatchCase:=MatchCase, MatchByte:=MatchByte)
If Not rg Is Nothing Then
Set ResultRg = rg
firstAddress = rg.Address
Do
Set ResultRg = Application.Union(ResultRg, rg)
Set rg = .FindNext(rg)
Loop While Not rg Is Nothing And rg.Address <> firstAddress
End If
End With

Set FindAll = ResultRg 'Range to return
End Function

[lostin_space]

Yes, rangeb (column b) is the where i am pasting all my data to. rangeb is actually B1:b400

I paste a table's worth of data in there. i'm then trying to identify the last row / a blank row at the bottom of the table, the slip down the sheet a couple of rows, and then paste into there, the next table's worth of data.

E.g.

col b col c col d
Row1 alpha 1234 56789
row 2 beta 0123 45689
row 3 gamma 0123 45678
>find this blank row

>drop down a row or two
>paste the next block of data / table in like so.

col b col c col d
Row10 alpha 1234 56789
row 11 beta 0123 45689
row 12 gamma 0123 45678

[lucas]

Do you presently have it copying your data. from what sheet to what sheet. What triggers the copy....button? What determines what gets copied? What does the data look like? I'm flying blind here...

[lostin_space]

a button triggers the first macro.

what's copied, is the result of a filter on my data in my original sheet.

Once my first lot of data is in my new worksheet, i then need to find the bottom of this newly copied data.

Once that's been found, i need to insert a couple of blank rows / drop down a couple of rows into a clear section of the sheet, then trigger my second macro, which again, copies another section of data..

[lucas]

try this to find the last row:

Dim ws2 As Worksheet, lastA As Long
Set ws2 = Sheets("Sheet2")
lastA = ws2.Range("a65536").End(xlUp).Row
With Target
If .Column <> 2 Then Exit Sub
Rows(.Row).Copy Destination:= _
ws2.Cells(lastB + 1, 1)
End With
Set ws2 = Nothing

[lostin_space]

the code falls over after the "if.column <> 2", saying 'an object is required'

With Target
If .Column <> 2 Then Exit Sub
Rows(.Row).Copy Destination:= _
ws2.Cells(lastB + 1, 1)
End With
Set ws2 = Nothing

what am i missing?

[lucas]

Sorry Russ, try this:

Option Explicit
Sub x()
Dim RgDestination As Range
Dim rg
Set RgDestination = ActiveWorkbook.Worksheets(2).Range("A65536") _
.End(xlUp).Offset(1, 0).EntireRow
Set rg = Selection
If Not rg Is Nothing Then
rg.EntireRow.Copy RgDestination
End If
End Sub

you must make a selection first

[lostin_space]

Sorry, i'm obviously having a 'blonde' moment here... when you say 'make a selection'... do you mean 'activecell.select' ?? or similar?
:banghead:

[lucas]

I'm sorry Russ for not being clear.
If you have 3 sheets then this will work from sheet 1 or 3 but you must select a cell in the row that you wish to copy to sheet 2...hope that helps.

Altertative would be to make the selection in the code by adding something like this after the dim rg line:

Sheets("Sheet1").Select
Range("A1").Select


so your code might look something like this:

Option Explicit
Sub x()
Dim RgDestination As Range
Dim rg
Sheets("Sheet1").Select
Range("A1").Select
Set RgDestination = ActiveWorkbook.Worksheets(2).Range("A65536") _
.End(xlUp).Offset(1, 0).EntireRow
Set rg = Selection
If Not rg Is Nothing Then
rg.EntireRow.Copy RgDestination
End If
End Sub


If you need more help, don't hesitate to holler. I might be slow getting back to it but there is lots of help here.

[lostin_space]

It works!! "by jove, i think he's 'got' it!", many thanks Lucas, very much appreciated.

Bst Rgds

Russ

[lucas]

Your welcome Russ. Be sure to mark your thread solved using thread tools at the top of the page.