Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Finding The Last Row Or Column Containing Data?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I have recorded a macro which copies a fixed range of cells and pastes the selection where the cursor is placed before running the macro. I always paste these cells 2 rows after the last row of my sheet . The problem with the macro is that if my cursor is placed anywhere but the desired location, it messes up my sheet

How can I modify my macro so that it always pastes the selection 2 rows after the last row containing data?

Also can I do something similar fo rcolumns? i.e how can I find the last column containing data?

Regards,

- J


Similar Excel Video Tutorials

Helpful Excel Macros

Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru

Similar Topics







I have a recorded VBA macro that, for the row that the cursor is in, copies the first 12 cells in that row and pastes them on another worksheet. HOWEVER, as written, the macro requires the user to have the cursor in column A of that row. The code is:

ActiveCell.Range("A1:K1").Select
Selection.Copy

Can you help me rewrite this so that regardless of what column the cursor is in, it copies the first 12 cells of that row.

It seems that while recording, if I switch to relative addressing, the code generated then only works for the particular column that the cursor was in at the time of the recording. This is why I resorted to requiring my users to put the cursor in column A before invoking the macro. This is not desirable and is error prone.


Hello,

Sorry I'm a bit new to this. I would like to copy a selection from a specified workbook (X) and paste it into the current workbook, where I initiate the macro from (Y). The problem is that the code indicates that the info should be pasted into workbook (Z) (the workbook where I recorded the macro).

How can I modify it so that the macro refers to the current workbook. I ask because I would like to run the same macro and apply it to many workbooks, not just (Z) and I would like to not have to go in and change the macro every time to the name of the new workbook (Y, L, T, whatever).

Here's the code:

Sub Min1()
'

'
' Keyboard Shortcut: Ctrl+Shift+M
'
Workbooks.Open Filename:= _
"H:X.xls"
Range("U1:AD5").Select
Selection.Copy
Windows("Z.xls").Activate
ActiveSheet.Paste
Range("U5:AD5").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("U5:AD19")
Range("U5:AD19").Select
End Sub

this code always references the correct sheet "X" , but pastes it into Z.xls. I would like to paste it into the workbook which is open. As an example: I'm working in workbook L, I run the macro and it copies the info from X and pastes it into L. Then I finish that and start work on workbook M, I run the macro from my personals again and it copies the info from X and pastes it into M. etc.

Thanks for any and all help


Hi,

I have a spreadsheet with 2 tabs - the row information is exactly the same, with column data different.
I have 2 simple macros to insert a formatted row - different formatted rows for each tab.
This is reliant on the user placing the cursor in the correct place on each tab.

Code:

Sub Add_Row1()

'// Inserts a new row
'// Copies the template row and pastes
'// to the new row
  
    Selection.EntireRow.Insert
    Cells(ActiveCell.Row, 1).Select
    Rows(Range("Row1").Row).Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Cells(ActiveCell.Row, 4).Select
    
End Sub


Sub Add_Row2()

'// Inserts a new row
'// Copies the template row and pastes
'// to the new row
    
    Selection.EntireRow.Insert
    Cells(ActiveCell.Row, 1).Select
    Rows(Range("Row2").Row).Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Cells(ActiveCell.Row, 4).Select
    
End Sub


The rows on each tab should be exactly the same - is there a way to link the 2 macros.
i.e if the user places the cursor on row 20 in tab 1, both tabs are updated with the formatted rows ("Row1" and "Row2") at the same time.

Many thanks for any suggestions.
Sheila


i need a macro where it selects cells b to br, copies them, then goes down a row and pastes 4 rows. for example, if the selection was on b56, it would select b56:br56, then copy, then paste b57:br61. but i need the macro to be set up where it copies and pastes according to that one function, i forget its name, it's something where you specify the cells according to the cell you're on, like if you're at a1, then b2 would be [-1,-1]. thanks in advance.


I have recorded the below macro to filter some information were column 10 has blanks, then paste info into another sheet. The problem is it pastes all the info if it cant find any blanks, how do I add in something to make it see there are no blanks and not paste anything

Sub Live123NOKCV()

'
' Live123NOKCV Macro
' Macro recorded 17/08/2009
'

'

Sheets("DATA").Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:="=", Operator:=xlAnd
Range("C16:J200").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Sheets("sheet 1").Select
Range("D3").Select
Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DATA").Select
Rows("1:1").Select
Range("C1").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Sheets("sheet 1").Select
Range("C2").Select


End Sub


Hi all, I have a simple macro that filters data then copy and pastes it into another sheet.

I have data in a range (A1:H200), with a macro I filter column G to show only rows with data in column G. A1:G200 is then copied to another sheet and column G & H is then deleted to bring in the new column. This loops until there is no column header in column G. This stacks the data how I need it

If there is data in column G then it works fine, it filters and copies only the required rows, however if there is no data under the header it copies all the rows with no data in column G and pastes them.

Is there an easy solution to this problem?

Thank you.


Hi everyone,
I am in the process of repetitively copying and pasting the entire contents of worksheets and pasting them to the previous worksheet in my workbook. I have data that is structured identically for each region, all of which have their own worksheet. I want a macro that takes the contents of the activesheet and pastes them into the previous sheet (i.e. pastes from sheets(i) to sheets(i-1)), starting in column A of the first nonempty row. I would also like to add something that stops running the macro if it encounters and error trying to paste because this would mean that there are not enough rows left. Here is what I recorded and it is not working:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/24/2008 by evalentini
'

'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWindow.SmallScroll Down:=3
Selection.Copy
WorksheetCount = Worksheets.Count()
i = 1

Workbook.Sheets
Do
i = i + 1
Loop Until WorSheets(i) = ActiveSheet
Sheets(i - 1).Select
Range("A1").Select
Selection.End(xlDown).Select
Range("A49915").Select
ActiveSheet.Paste
Sheets(i).Delete

'Application.CutCopyMode = False
'ActiveWindow.SelectedSheets.Delete
End Sub


I have a macro that copies a set of data from one sheet and pastes it into another sheet, the location where it pastes the data is based on a named cell.

I then highlight all of the rows where data was just pasted in and then I want to sort it.

The problem I have is that the sort command (at least the way I've used it in macros in the past) has to have a range defined by the start and end cells. The start and end cells will be different each time (both starting row and total number of rows).

Is there any way to write the sort command into the macro without referencing the range?


Hi I have a macro that copies data from one spreadsheet-pastes it into a set location then copies back a set of results next too orginal set of data. Basicly some client data pastes data into a audit sheet an audit result is displayed in sheet two then the data is copied back to the orginal sheet.

The set of data copied from the first sheet can be 1-10 rows big. I select the first list in the filter run the macro, then go to the next option then run the macro.

Am trying to find the best way to automaticly step through the autofilter any thoughts?

Russ


Hi,

I have recorded a Macro in excel 2000 that will find cells containing ".xls" and then copies and pastes the values into that cell. How can I get this macro to repeat until it has found all the cells containing ".xls". The Macro I have recorded is:

Cells.Find(What:=".xls", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

Thanks

BelfastHatter


Hi Everyone!

I have a macro that I am using and it works perfectly but I just want to tweak a little bit but I don't know how. This macro will take you back to another sheet from active sheet to the last record row but the cursor always ends up at column D. I would like to have the cursor location to end up at the last record row of column B instead.

Here is my macro code:

VB:

Sub CPListUpdate() 
    Worksheets("CPList").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(1, 3).Value = _ 
    Array(Cells(24, 1).Value, Cells(24, 7).Value, Cells(13, 4).Value) 
     
    Range("D1").Select 
    Sheets("CPList").Select 
     
    x = ActiveSheet.UsedRange.Rows.Count 
    ActiveCell.SpecialCells(xlLastCell).Select 
     
End Sub 


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



Again, the cursor always ends up at column D of the last record. I would like to have the cursor at column B instead.

I would appreciate everyone's help on this and thank you in advance.

RNF

User copies and pastes data (as text) to the next unused row on a worksheet. Data/Text-to-Column works fine. Later the user updates the worksheet in the same manner, and the new data occupies a new location further on down in the worksheet.

As part of a longer macro, I wish to include the code to convert text to columns at the location of the active cell, where the new data has just been pasted. The code I get when I use the macro recorder includes the destination for the active cell at the point the macro was recorded. (If the user invokes the recorded macro, useful data would be overwritten.) So, how do I work it so the text-to-column conversion is applied at the location of the current active cell?

Here is the relevant portion of the recorded macro code, problem element in Bold:

Selection.TextToColumns Destination:=Range("A10219"), DataType:= _

I am thankful for any help.


Hi All -

I am trying to write a macro (see below) that copies a 2 row range to another place. The "copy to" place is where the cursor is when I activate the macro. I want the macro to paste the row range to wherever I am when I run it. It's not working (if it was working I wouldn't be here). It seems that the cursor moves during the execution of the macro instead on staying where I am when I start the macro.

Any help would be appreciated.

Amy

Sub Macro2()
'
Rows("21:22").Select
Selection.Copy
ActiveCell.Select
Selection.Insert Shift:=xlDown
End Sub


Hello,

I need some help with this macro. I know very little about Visual Basic (or any other programming language for that matter), so I used the macro recorder to create the following. This is the process I'd like to enable with this code.

1. User pastes their spreadsheet in sheet2
2. User runs the macro.
3. Macro copies everything on sheet2 from row 8 on down in columns A,C,J and K(as long as there are entries in the row for column a)
4. Macro pastes the copied data on sheet 1. Without removing any entries already entered on sheet 1.
5. Data is sorted according to column B. (There is a header row.)
6. Next user pastes their spreadsheet in sheet2 (Overwriting what ever was there).
7. Macro is run and the pertenent information is added to sheet 1 and sorted.

I placed comments in the code where I know it doesn't do what I want.

Please let me know if you need any more information.

Quote:

Sub PopulateList()
'
' PopulateList Macro
' Macro recorded 2/23/2010 by maurice.holliday
'
' Keyboard Shortcut: Ctrl+l
'
Sheets("Sheet2").Select
Range("A8:A11,C8:C11,J8:K11").Select 'copy everything from row 8 down as long as something is in it for columns A,C,J, and K.
Range("J8").Activate
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select 'first empty cell in column A
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A2").Select
End Sub




Hi - Newbie here.. (can hear the groans already

I am trying to use a macro to copy a set of cells.

I have successfuly created a macro - the problem is I would like the column reference to be absolute but the row reference to be relative. I can have one or the other but not mixed it seems.

If I have not explained myself well, perhaps this may help - I need to copy cells $J1:$S1 and paste them to $L1:$U1

Currently the macros works if the cursor is in cell J1 when I activate the macro but if I move the cursor to any other cell it copies and pastes the wrong set of cells.

Any help you can give is much appreciated.

Thanks

Chris


i've currently got a recorded marco that inserts four rows, highlights and copies four rows and pastes it (while deleting their values) in the rows that were inserted.

the problem is that when i want to run the macro again, the four new rows that are inserted are in between the original four rows and the four rows that was pasted the first time the macro was ran.

i'd like to have a macro that will paste the rows at the bottom of the list.

the code currently look like:

Sub InsertRow()

InsertRow Macro

Rows("18:21").Select
Selection.Insert Shift:=xlDown
Rows("14:17").Select
Range("C14").Activate
Selection.Copy
Rows("18:21").Select
ActiveSheet.Paste
Range("J18").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("J21").Select
End Sub


Any help would be greatly appreciated.


Hello Everyone!

I recorded a macro that will go directly to the sheet of a selected cell with a hyperlink property. The issue with this recorded macro is, it has a fixed cell address so every time this macro run, it will execute the same cell that was recorded. I would like to have the cell address as a selected cell (where the cursor is).

Here is the recorded macro:

VB:

Sub Create_New_Ship_Code() 
     '
     ' Create_New_Ship_Code Macro
     ' Macro recorded 1/19/2011 by
     '
     
     '
    Range("B17").Select 
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True 
End Sub 


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



As you can see, it has B17 cell address as fixed. I would like to have it as a selected cell meaning where the cursor is.

I hope you can help me on this!

Thank you in advance.

RNF

Morning.

I am wondering what the line(s) of code are in a macro that will return the cursor to the page where the macro button was pressed.

I have a macro that goes from one sheet, copies a bit of data from that sheet, unhides another, pastes the data, then hides the sheet again, but because the sheets are not next to each other, the macro ends on the sheet that was next to the one I am hiding.

I can not put the sheet name i wish it to return to as this button appears on multiple sheets.

Can somene help? My searches are finding nothing (may be using wrong keywords to describe what I am after)

Tayler


I have a VB macro that sorts an area of my worksheet several ways and also copies and pastes in some formatting in case the user has "clobbered" the formatting by pasting in some data from outside. When the macro ends, the cursor has been moved to the top left of the data area.

I would like some code to return the cursor back to the cell it was in before the macro was started. I assume that I will have to set a variable = to the "ActiveCell" at the start and then select that range again at the end, but I don't know the code to set the variable.

Or is there a better way?

Thanks in advance,

Vernon


Hi

I am trying to write some code that copies 1 cell from my Data sheet (always cell B2) and pastes it in row 2 in my Graph Data sheet but each time I run the macro it pastes it in the next column on from the last time a ran it

The reason being I want my graph data to be updated on a weekly basis when I run the macro so the new weeks data needs to go in the next column each time

So far I have the following code but each time I run it it doesnt seem to keep the variable from the last time it was run

Sub Week_Finished()
'
' Week_Finished Macro
'
Dim i As Integer

If i = 2 Then i = i Else i = 2

Range("B2").Select
Selection.Copy
Sheets("Graph Data").Select
Cells(2, i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

i = i + 1
Sheets("Data").Select
MsgBox (i)

End Sub

Any help appreciated

Thanks


Hi

Hope soemone can help as I'm definitely stumped on why this is happening here

I recorded a Macro that takes unique dates from a large sample of dates (i.e in the sample the 20th Feb appears 300 times but I only want to paste 1 example of this in another part of the excel book)
The macro firstly uses advanced filter function on the data looking for unique records on the date column and then copies the unique list of dates to another sheet

However when it pastes it in the other sheet it consistently pastes it in a different location each time. This wouldnt be so bad but I aim to have formulae set up to reference the unique dates so it needs to go into the same place each time and this is exceptionally puzzling

Very very grateful for any thoughts


i am trying to record a macro that copies and pastes data from a webpage into a sheet in excel and then copies and pastes different data from a different webpage into another sheet in excel.

when i replay the macro it just pastes the same data twice instead of 2 different sets.

i am obviously making a basic error - is the way i copy and paste?

i'm not massively into VBA so would prefer advice on what is going wrong when i record the macro if possible.

thanks in advance.


The code below grabs a piece of information from a database, pastes that information into a calculater, calculates, then copies and pastes the results elsewhere in the sheet. I need to add another element. After the calculator figures out the results, there is another string of data that needs to be pasted. So, the macro needs to grab one set of results, paste it in the sheet, then grab the second set of results and paste it elsewhere in the sheet before calculating the next set of info.

The way I have it written now is it goes through and calculates/pastes the array of data like usual, but THEN copies and pastes the second set of results. Do you think you could help?

For counter2 = 0 To units
Range("Unit1_Info").Offset(counter2, 0).Select
Selection.Copy
Range("Input").Select
PV
Range("Unit1_Mo1").Offset(counter2, 0).Select
ActiveCell.Formula = "=Mo_Rents"
Selection.Copy
Range("Unit1_Rents").Offset(counter2, 0).Select
PST
Range("TurnPaste").Select
Selection.Copy
Range("AH1277").Offset(counter2, 0).Select
PST
ActiveSheet.Calculate
CPV
Next counter2

PST, PV and CPV are macros that essentially paste values (from what I can tell).


I have been trying to write a macro that selects the bottom 20 cells in a column of data, and pastes them into another place on the same worksheet. These cells are the results from formulas working along the rows, and need to be 'paste specialed' into the destination range. I have ran the macro with a normal 'paste' and just had zeros in the destination range.

The bottom 20 cells will be in column G, and the target range for the cells is J5:J24. The macro code I have at the moment is:

Sub Cpy()
Dim LR As Long
LR = Range("G" & Rows.Count).End(xlUp).Row
Range("G" & LR - 19 & ":G" & LR).Select
Selection.Copy
Range("J5").Select
Selection.PasteSpecial Paste:=x1PasteValuesAndNumberFormats, Operation:=x1None, SkipBlanks:=False, Transpose:=False
End Sub

When I run this macro, I get an error that says just '400', . The help button gives me nothing - literally. Just a blank screen. I'm at a dead end. Can anyone tidy up the code of a beginner to get this to work? Thanks!


I have a macro that opens up a workbook, copies its content then pastes it into the current workbook. The workbook that it copies from is a CSV file with only one column and the lines include the pipe character (|) which I will use as a separator for Text to Columns.

Anyway, many rows have a comma (,) in it and when the macro pastes, it pastes all values between commas in separate columns; how do I prevent this behavior? Urgent, help me out.

So the result should be pasted 1:1, that is only one column instead of pushing some values into other columns. Is there some hidden delimiter setting when copy + pasting?

Code:

Code:

    ActiveWorkbook.Sheets(1).Cells.Select

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Because I used Select all cells (select current contents and paste over old data) I couldn't use the more simple Selection.Paste function for some reason, only PasteSpecial worked.