Free Microsoft Excel 2013 Quick Reference

Find Last Row With Text String

The following line finds last "non-blank" row in a column.
I have a column that has formulas in 10000 rows.
Most of the time only first 7000 rows will have data, other cells below will have "". I need to find the last row with real data, not "".

Is there a way to add a condition or check to the following line?

thanks,
modytrane


Post your answer or comment

comments powered by Disqus
i need a macro to Find last row with data and autofit, bold and center align all the cells in the row

I currently have 3 worksheets (CB Design, INV Design, Summary) within a template workbook. I have a macro button on the CB Design sheet that performs one function. I also have a similar macro button on the INV Design sheet. The template will always open a file that will contain the following on the Summary sheet: cell A2 = CB1 and cell A3 = INV1. When the macro button on CB Design sheet is pressed, I would like to add the following:
after the macro performs what it already does on the CB Design sheet, go to Summary sheet, find last row that contains "CB", copy that row and insert it directly below itself.
When the macro button on INV Design sheet is pressed, I would like to add the following:
after the macro performs what it already does on INV Design sheet, go to Summary sheet, find last row that contains "INV", copy that row and insert it directly below itself.
I have searched for several hours on the excel forums with no luck. The main hurdle is, I am not looking for an exact value in the last cell (e.g. CB2), but rather just "CB" of "INV". Most of the solutions have been for specific cells or not to find last row with the partial value.

An example that lacks the copy function, but that I tried to modify and run as a test before incorporating it into the existing macros, but realized it is looking for an exact value is below:

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "B").Value = "CB1" Then
Rows(i + 1).Insert
End If
Next i

End Sub
Sub Macro5()
Any help would be greatly appreciated.

Pat

This may be easy but I'm completely stumped right now.

I'm writing a macro that sets print areas automatically from a button. There's one sheet that the rows will change everytime, anywhere from 10 lines to 250 lines.

Is there code that will find the last row containing text?

Hi all,

I need to get a range VBA code to find the last row with a non-empty cell in a Multiple Column Range, specifically from columns A to AT

So far I have tried this one in different versions, but the best it does is give me the last row in Column "A", even when I use AT or any other column.

Range("A10", Range("a65536").End(xlUp)).Activate

I have tried alos some version wit R1C1 format, but no luck.

Help Please

Hi, I need a little help with CONCATENATE

I already have a form that finds the last cell with data in sheet 1 then adds the new data to the row beneath (pretty standard thing to do and I’ve got that working).

What I need help with is, once that data is entered I want my concatenate formula in sheet 2 to write a little blurb about the latest entry (so staff can highlight it and paste it into another program).

So in sheet 1 I have data like
A B C
1 Type Size Colour
2 Truck Small Blue
3 Car Big Red

In sheet 2 I have =CONCATENATE(Sheet1!A3,Sheet3!A1……….)

In sheet 3 I have strings like ‘This is a’ to concatenate with the data from sheet 1.

So the end product should be a small narrative in sheet 2 that reads something like ‘This is a Big car that is the colour red’

What I can’t do is make reference to sheet 1 in the CONCATENATE formula. In the example above I would need to reference the last row with text, rather than type A3.

So when the next entry is added to sheet one, let’s say boat – small – white
The narrative in sheet 2 will change to read ‘This is a small boat that is the colour white’

Can anyone help? What I need is the formula for CONCATENATE that reads (In Sheet 1 find the last cell with data in column A, Sheet3!A1......) Instead of (Sheet1!A3, Sheet3!A1…)

Thanking you in advance

I need help with a code to find the last column with data entered, copy that Entire Column, select the column to the immediate right and insert the copied text. However, I need help with the next step. I also need to make sure ROWS 22:75 in the newly inserted column are cleared.


	VB:
	
 FindLastColumn() 
    [COLOR=DarkGreen] 'Finds last used column, copies and selects the next[/COLOR]
    Dim LastColumn As Integer 
    Dim NextColumn As Integer 
    [COLOR=DarkGreen] 'Find last column with text[/COLOR]
    If WorksheetFunction.CountA(Cells) > 0 Then 
         'Search for any entry, by searching backwards by Columns.
        LastColumn = Cells.Find(What:="*", After:=[A1], _ 
        SearchOrder:=xlByColumns, _ 
        SearchDirection:=xlPrevious).Column 
    End If 
     
    [COLOR=DarkGreen] 'Copy that last column with text
     'and select the next column to the right to insert copied data in.[/COLOR]
    Columns(LastColumn).Select 
    Selection.Copy 
    Columns(LastColumn + 1).Select 
    Selection.Insert Shift:=xlToRight 
    Application.CutCopyMode = False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
what would I add at the end of this to select this NEW Last column with data, and select certain rows within that column?

Hi everyone. So far, I've gotten tons of help from the forum to make VB code that lets me browse to a folder, list all of its contents, and import the list into a spreadhseet. Thanks again!

Now, I'd like to do some sorting using the code, and to do that I need to know how many data entries were imported. The easiest way to do this, I'd guess, is to simply find out what the number of the last row with data is. I've seen some mentions about
 but I'm not sure if that what I need or what it does.

Can someone point me in the right direction of the command(s) that will tell me the row number of the last data entry?

Thanks,
Adam

I am trying to insert 6 consecutive rows with text values after a value found in a cell (in between the Magenta colored text).
This is a sample set of text (All of the text is one string from Column A, per row)
RECALL/D(COORD1),DISK
SNSLCT/S(S1a90b180)
GOTO/ABS,CART,0.00000,-1.53925,1.53925
D(DROTAT)=ROTATE/ZAXIS,180.0000
D(DROTAT)=ROTATE/XAXIS,-90.0000
GOTO/ABS,CART,0.00000,0.00000,0.65745
MEAS/SPHERE,F(CAL_4),5
GOARC/ABS,CART,0.46489,0.46489,0.00000,0.00000,0.00000,0.00000,I-J-K,0.70711,-0.70711,0.00000,CW
GOMEASURE/ABS,CART,0.35348,0.35348,0.00000,I-J-K,-0.70711,-0.70711,0.00000
GOARC/ABS,CART,0.46489,-0.46489,0.00000,0.00000,0.00000,0.00000,I-J-K,0.00000,0.00000,1.00000,CW
GOMEASURE/ABS,CART,0.35348,-0.35348,0.00000,I-J-K,-0.70711,0.70711,0.00000
GOARC/ABS,CART,-0.46489,-0.46489,0.00000,0.00000,0.00000,0.00000,I-J-K,0.00000,0.00000,1.00000,CW
GOMEASURE/ABS,CART,-0.35348,-0.35348,0.00000,I-J-K,0.70711,0.70711,0.00000
GOARC/ABS,CART,-0.46489,0.46489,0.00000,0.00000,0.00000,0.00000,I-J-K,0.00000,0.00000,1.00000,CW
GOMEASURE/ABS,CART,-0.35348,0.35348,0.00000,I-J-K,0.70711,-0.70711,0.00000
GOARC/ABS,CART,0.00000,0.00000,0.65745,0.00000,0.00000,0.00000,I-J-K,0.70711,0.70711,0.00000,CCW
GOMEASURE/ABS,CART,0.00000,0.00000,0.49990,I-J-K,0.00000,0.00000,-1.00000
GOTO/ABS,CART,0.00000,0.00000,1.53925
ENDMES
CALIB/SENS,S(S1a90b180),FA(CAL_4)
SAVE/P(P-2x40)
D(MACHCS)=DATSET/MCS
This is my macro (Sub bbb only! (There is a "sub aaa" in the same module)
()
For lRow = Range("A1").End(xlDown).Row To 2 Step -1
    If Left(Cells(lRow, "A").Value, 13) = "MEAS/SPHERE,F" Then
        If Cells(lRow, "A").Offset(-1, 0).Value <> "RECALL/D(COORD1),DISK" Then
            Cells(lRow, "A").Activate
            With Selection
                'MsgBox Cells(lRow, "A").Value    'for testing
                'find left "(" and then right ")" string example: "(CAL_1)"
                sName = Cells(lRow, "A").Value
                sName = Mid(sName, InStr(.Value, "("), InStr(sName, ")") - InStr(sName, "(") +
1)
                If sName <> "(QUA_1a)" Then
                    'MsgBox sName    'for testing
                End If
            End With
        End If
    End If
    'MsgBox Cells(lRow, "A").Value & " " & sName    'for testing
Next lRow

For lRow = Range("A1").End(xlDown).Row To 2 Step -1
    If Cells(lRow, "A").Value = "(QUA_1)" Then
        Exit Sub
    Else
        If Cells(lRow, "A").Value = "ENDMES" Then
            Cells(lRow, "A").Activate
            'MsgBox lRow 'for testing

            iCounter = 0
            iNewRows = 6
            Do While iNewRows > 0
                iNewRows = iNewRows - 1
                iCounter = iCounter + 1
                Cells(lRow, "A").Offset(1, 0).Activate
                With Selection
                    '.EntireRow.Select
                    '.Insert Shift:=xlDown
                    If iCounter = "1" Then
                        MsgBox iCounter 'for testing
                        .EntireRow.Select
                        .Insert Shift:=xlDown
                        Cells(lRow, "A").Value = "DECL/GLOBAL,REAL,VAR1"
                        If iCounter = "2" Then
                            MsgBox iCounter 'for testing
                            .EntireRow.Select
                            .Insert Shift:=xlDown
                            Cells(lRow, "A").Value = "VAR1=OBTAIN/FA" & sName & ",8"
                            If iCounter = "3" Then
                                MsgBox iCounter 'for testing
                                .EntireRow.Select
                                .Insert Shift:=xlDown
                                Cells(lRow, "A").Value = "IF/ (VAR1 .GE. 0.00008)"
                                If iCounter = "4" Then
                                    MsgBox iCounter 'for testing
                                    .EntireRow.Select
                                    .Insert Shift:=xlDown
                                    Cells(lRow, "A").Value = "JUMPTO/" & sName
                                    If iCounter = "5" Then
                                        MsgBox iCounter 'for testing
                                        .EntireRow.Select
                                        .Insert Shift:=xlDown
                                        Cells(lRow, "A").Value = "ELSE"
                                        If iCounter = "6" Then
                                            MsgBox iCounter 'for testing
                                            .EntireRow.Select
                                            .Insert Shift:=xlDown
                                            Cells(lRow, "A").Value = "ENDIF"
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End With
            Loop
        End If
    End If
Next lRow
End Sub
Using message boxes I can see I do not itterate thru all 6 iCounter variables. Only iCounter "1", which places proper text for "DECL/GLOBAL,REAL,VAR1".

The end result formatted as shown, my goal. (Reference my sample set of text in Magenta color)
ENDMES
DECL/GLOBAL,REAL,VAR1
VAR1=OBTAIN/FA" & sName & ",8
IF/ (VAR1 .GE. 0.00008)
"JUMPTO/" & sName
ELSE
ENDIF
CALIB/SENS,S(S1a90b180),FA(CAL_4) The 6 lines of text are:
DECL/GLOBAL,REAL,VAR1
VAR1=OBTAIN/FA" & sName & ",8
IF/ (VAR1 .GE. 0.00008)
"JUMPTO/" & sName
ELSE
ENDIF Any hints, tips or examples are appreciated.

Hi All,
I could use a hand from somebody with more expertise on this one. I'm attaching a sample sheet (trying to, anyway) to help clarify a bit. Forgive my rudimentary vba

There are two sheets. Sheet 1 contains three columns. The first is a BBID (a numeric identifier). The second, which I don't need, is a company/index name. The third is a series of keywords, comma separated, used to describe that particular BBID.

On the Sheet 2, there is one column, which contains single keywords.

What I need to do is return every BBID that contains each of the keywords on sheet 2.

My macro, though not well done, does seem to work - but ultimately there will be close to 1 million rows of data on sheet 1, and 40,000 keywords to map, not the 10,000/9 I have in my sample. Wth my macro, this will take quite a while to run.

Any suggestions are greatly appreciated!

Here's my macro code:
Sub findmatchingkeywords()
Application.ScreenUpdating = False
Dim keyw As String
Dim bbid As String
'find last row with data
Sheets(1).Select
lastrow = Range("a1000000").End(xlUp).Offset(1, 0).Row

Sheets(2).Select
Range("a2").Select

'determine keyword and locate bbid's of any rows with matching data
Do Until ActiveCell = ""
    keyw = Trim(ActiveCell)
    Sheets(1).Select
    Range("C1").Select
    Do Until ActiveCell.Row = lastrow
        If InStr(ActiveCell, keyw) > 0 Then
            bbid = ActiveCell.Offset(0, -2)
            Sheets(2).Select
            Range("xfd" & ActiveCell.Row).End(xlToLeft).Offset(0, 1) = Trim(bbid)
            Sheets(1).Select
        End If
        ActiveCell.Offset(1, 0).Select
        Application.StatusBar = "Now on row:  " & ActiveCell.Row
    Loop
    
    Sheets(2).Select
    ActiveCell.Offset(1, 0).Select
Loop
If my attachment didn't come through this time, will try again momentarily!

Hi.

I need a macro that will find the last row containing data (i.e., the last cell in column A containing data) on the sheet called "RawData", then enter that particular row number in cell A1 on the sheet named "RD2."

Thanks for your help!

Glenn

I'd like to set the print range based on the last row with text in specific columns. I found a couple of macros in this forum to adapt, but neither are working. Extra rows, which only contain conditional formatting, and other excluded rows and columns still print. Any suggestions on fixing either of these macros or another approach would be highly appreciated.


	VB:
	
) 
    Dim MaxRow As Long, i As Integer 
     
    MaxRow = 1 
    For i = 1 To 6 
        Cells(65536, i).End(xlUp).Select 
        MaxRow = Application.WorksheetFunction.Max(MaxRow, ActiveCell.Row) 
    Next i 
     
    ActiveSheet.PageSetup.PrintArea = "$A$2:$F$" & MaxRow 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
) 
    Dim LastRow As Long 
    LastRow = ActiveSheet.Columns("A:F").Find(What:="*" _ 
    , After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
    ActiveSheet.PageSetup.PrintArea = "$A$2:$F$" & LastRow 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,
Steve

What i need to do is Open a Closed workbook (which i have done in the code below)

1 . When this book opens there is a msgbox that needs to have "template" pasted in to then ok automatically clicked ( i have no clue if this can even be done)

2. Then go to Sheet("contactor info") find last row then paste certain info. Below is a peice of code i have from the open workbook but of corse it doesnt work that why i am asking for help. The red is where it hangs up. Thanks in advance for any help.


	VB:
	
 
openpath = ActiveWorkbook.Path & "" 
Set wb = Workbooks.Open(openpath & "Template.xls") 
With wb.Sheets("Contractor Info") 
    [COLOR="Red"].Cells(Sheets("Contractor info").Rows.Count, "B").End(xlUp)(2, 1).Select[/COLOR] 
    ActiveCell.Value = Msg1 
    ActiveCell.Offset(0, 4).Value = Msg2 
    ActiveCell.Offset(0, 5).Value = Msg3 
    ActiveCell.Offset(0, 3).Value = Msg5 
    ActiveCell.Offset(0, 2).Value = Msg4 
    ActiveCell.Offset(0, 1).Value = Msg6 
    ActiveCell.Offset(0, 6).Value = Msg7 
    Application.Calculation = xlCalculationManual 
    Sheets("Contractor Info").Range("B5:h200").Sort Key1:=Sheets("Contractor Info").Range("B3"), Order1:=xlAscending, _ 
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ 
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal 
    Application.Calculation = xlCalculationAutomatic 
End With 
 
wb.Close , True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am writing a macro. It's more complicated than I originally thought. What I
need to do is autofill a formula in a column to the last row with data. The
problem is the rows are dynamic. They start in a different place each time
and end in a different place. The range size will vary every time the macro
is used. Once the formula has calculated the neccessary numbers I want to
copy and paste the information back to another column. My problem is telling
excel which function I want to perform when the calculations will be made on
a moving target. I have the macro working to the first cell where the formula
is entered. Now I need to copy the formula to the last row that contains
information the formula can use. Please help. I have posted this previously
but it was days ago and still no answer. Here's a sample of my macro. This is
only a small part.

Sheets("BOMSized").Select
Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12"

--
A Waller

Is there a code that can find the last row with data on a worksheet. My worksheet has blank cells with formatting in them which causes the xlUp function to not work properly because it stops at the blank cells. I need a code that can find the last row with actual data in it. Any help would be great.

I have some VB code that selects the last row with data in an excel worksheet:

With Columns("A:I")
    LR = .Find("*", .Cells(1, 1), xlValues, xlPart, xlByRows, xlPrevious, False, False).Row
    End With
Now I want/need to tweak it so it only selects rows where the value in column "I" = 0. I'm pretty new to VB so I'm not sure the best way to do this. Any suggestions? Thanks!

I am writing a macro. It's more complicated than I originally thought. What I
need to do is autofill a formula in a column to the last row with data. The
problem is the rows are dynamic. They start in a different place each time
and end in a different place. The range size will vary every time the macro
is used. Once the formula has calculated the neccessary numbers I want to
copy and paste the information back to another column. My problem is telling
excel which function I want to perform when the calculations will be made on
a moving target. I have the macro working to the first cell where the formula
is entered. Now I need to copy the formula to the last row that contains
information the formula can use. Please help. I have posted this previously
but it was days ago and still no answer. Here's a sample of my macro. This is
only a small part.

Sheets("BOMSized").Select
Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12"

--
A Waller

This is a rephrasing of another question posted earlier in another thread, that I have requested a moderator close/delete.

I'm trying to find the last row which has a value (non-empty) in column B. I want to then copy the formatting, validation and formulas of that last row (Columns A - BA) and paste it into the next available row.

I'm not very good / quite new to VBA, so here is what I've managed to piece together so far, from reading online (here and elsewhere). The comments show what I think will be happening and where... so you can see what I've got so far and what I'm still working on.

Any code / links / tips are appreciated.

Sub NewRow()
Option Explicit

'Declare variables;
Dim ws As Worksheet
Dim lastRowColB As Long
Dim screenUpdateState, statusBarState, calcState, _
    eventsState, displayPageBreakState As Boolean


'Get current state of various Excel settings;
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks

'Turn off some Excel functionality;
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

'Unprotect the worksheet, and unhide all rows so that the
'new row can be "inserted";
ActiveSheet.Unprotect
ws.Cells.EntireRow.Hidden = False

'Find the last row (lastR) which has a value in Column B;
lastRowColB = Range("B65536").End(xlUp).Row


'Copy the formatting, formulas and validation for the row
'found in the step above;


'Paste the formatting, formulas and validation to
'the next available row;

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

'Rehide all unused rows (lastrow+1) and re-enable protection;

ActiveSheet.Protect

'Restore state;
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState

End Sub


I am trying to improve my macro.

At the moment I have data in column A. It always starts in cell A1 and I
need to find the last row with data. At the moment I have the following code:

Cells(1, 1).Select
Selection.End(xlDown).Select
EndCell = ActiveCell.Row

I am trying to get away from actually selecting cells in a sheet if I can.
Is there some code that will find the last row for me?

Thanks in advance.

I need to find the last row in a column that has data in it. I am currently using:

Is there a better, more efficient way to do this?

I have a macro but its not working. I need a Macro to put bottom border at last row with data. The last column is always difrrent as well so i need it to find the last row and column with data and place a bottom border. I was using a code with the lin below but i whink i have soomething wrong.

    With Cells
        LastRow = .Find("*", .Cells(1, 1), xlValues, xlPart, xlByRows, xlPrevious, False, False).Row
        LastCol = .Find("*", .Cells(1, 1), xlValues, xlPart, xlByColumns, xlPrevious, False, False).Column
            .Range(.Cells(LastRow, 1), .Cells(LastRow, LastCol)).Borders (xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With


Hi,

I have a worksheet and lets say that the range A1:H5 is filled with data.
What I want is to find the last row with data after I delete the values of A2:H5. I don't delete the entire rows, I just select the cells and press delete.
I've tried this in many different ways but it keeps telling me that the last row is 5, even if there's nothing in the cells.
Is there anyone that can help me out with this?

Thanks

Hi

Does anyone know how to copy and paste 12 sets of data into a a worksheet by first looking at where the last row with text in it is and then leave a blank line and paste the data.

I have this so far:

strFile6.Sheets(1).Range("A2:N14").Copy Destination:=wbNew.Sheets("MTH").Range("B")
strFile7.Sheets(1).Range("A2:N14").Copy Destination:=wbNew.Sheets("MTHPrevious").Range("B")
strFile8.Sheets(1).Range("A2:N14").Copy Destination:=wbNew.Sheets("MTH").Range("B")
strFile9.Sheets(1).Range("A2:N14").Copy Destination:=wbNew.Sheets("MTHPrevious").Range("B")

strFile10.Sheets(1).Range("A2:N14").Copy Destination:=wbNew.Sheets("YTD").Range("B")
strFile11.Sheets(1).Range("A2:N14").Copy Destination:=wbNew.Sheets("YTDPrevious").Range("B")
strFile12.Sheets(1).Range("A2:N14").Copy Destination:=wbNew.Sheets("YTD").Range("B")
strFile13.Sheets(1).Range("A2:N14").Copy Destination:=wbNew.Sheets("YTDPrevious").Range("B")

strFile14.Sheets(1).Range("A2:N14").Copy Destination:=wbNew.Sheets("R12").Range("B")
strFile15.Sheets(1).Range("A2:N14").Copy Destination:=wbNew.Sheets("R12Previous").Range("B")
strFile16.Sheets(1).Range("A2:N14").Copy Destination:=wbNew.Sheets("R12").Range("B")
strFile17.Sheets(1).Range("A2:N14").Copy
Destination:=wbNew.Sheets("R12Previous").Range("B")
But i dont know how to add code to it so it looks at where the last line of text is in the worksheet and then leaves a blank line and pastes the data in.

Can anyone help?

Thanks

Jeskit

Hi,
I am struggling with part of a macro. I have a macro that copies and pastes rows from one workbook to another.


	VB:
	
 ActiveWorkbook.ActiveSheet 
    On Error Resume Next 
    Last = .Cells(.Rows.Count, 1).End(xlUp).Row 
    Set CopyRng = Range(.Rows(StartRow), .Rows(Last)) 
End With 
 
With bk.Worksheets("Master") 
    .Unprotect Password:="d" 
    shLast = .Cells(.Rows.Count, 1).End(xlUp).Row 
End With 
CopyRng.Copy 
 
With bk.Sheets("Master").Cells(shLast + 1, "A") 
    .PasteSpecial xlPasteValues 
    .PasteSpecial xlPasteFormats 
    Application.CutCopyMode = False 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Before i lock the sheet again i want to copy cells "AY3" & "AZ3" and paste including formating down along the columns ("AY & AZ) to the last row with values in it.

I hope that made sense.

I would appreciate any help anyone can give me.

Many Thanks
D

HELP !!!!
Need to fix macro to insert selection under the last row containing data currently it selects Row ("9:9") and inserts. If this makes any sense please help!!!!!

Sub Trial()
'
' Trial Macro
' Macro recorded 2/2/2007 by Teri Calrson
'
' Keyboard Shortcut: Ctrl+r
'
Sheets("1").Visible = True
Sheets("1").Select
Rows("2:2").Select
Selection.Copy
Sheets("JSA Template").Select
Rows("9:9").Select
Selection.Insert Shift:=xlDown
Sheets("1").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("JSA Template").Select
End Sub


No luck finding an answer? You could always try Google.