0

I am currently trying to finish a Macro that will pull together a list of all the people into 1 list and show all of the different info for people across all of the lists.

I have gotten to the point where I have all the lists together, and all I need to do now is select the proper data and compile it.

My current code:

Sheets("Sheet2").Select
Columns("E:AI").Select
Selection.Copy
Sheets("Sheet1").Select
Range("E1").Select
ActiveSheet.Paste

I am trying to replace the columns line so that it can select a variable length.

Columns("E").Select
Range(Selection, Selection.End(x1toRight)).Select
Range(Selection, Selection.End(x1Down)).Select
Selection.Copy

When the Macro gets to the first 2nd line in my updated code, I get a run time error '1004': Application-defined or object defined error.

When I go through it line by line, there seems to be an issue with the Columns("E") selecting the same data as it previously had when it was Columns ("E:AI").

Any help would be greatly appreciated.

Community
  • 1
  • 1
CZ31
  • 5
  • 3
  • 1
    `x1toRight` should be `xLtoRight` and `x1Down` sould be `xLDown`. –  May 31 '17 at 13:58
  • Why are you trying to use `xlDown` when you start with full columns anyways? Record yourself performing these actions and examine the code you produce. –  May 31 '17 at 13:59
  • You should also avoid using `Select`, see [this answer](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for some more info – Jordan May 31 '17 at 14:09

1 Answers1

0

Not exactly sure what you are trying to do but this code till select E1 -> last column and last row filled.

You need to adapt it to suit your needs. I assumed your table is square, if not change the code.

I counted the rows used in column E, if that is not what you want change that.
I also looked for the last column in row 1. change that if that is incorrect. 

LastCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column ' here you need to change '1' to what row to look at
LastRow = Cells(Rows.Count, "E").End(xlUp).Row ' change "E" to fit your need
Range("E1:" & Split(Cells(1, LastCol).Address(True, False), "$")(0) & LastRow).Select
Andreas
  • 23,610
  • 6
  • 30
  • 62
  • I am trying to select all of the columns after E until the end of my data. The lists that I'm pulling my info from vary in the number of columns that I need to copy. Will this work for that? – CZ31 May 31 '17 at 15:48
  • @user8092309 well, if you tell me what your sheet looks like I can answer your question. Or you can just try and see. Either way. I'm sure it works or I can make it work – Andreas May 31 '17 at 16:04
  • @user8092309 You say "all of the columns". Yet the code you gave us above indivates it's a range of cells and not columns you select. First line you select E column, then "ctrl right arrow" menas select till end. Fine. But then you do "ctrl down arrow", do you see the confusion? Do you want columns or range of cells? – Andreas May 31 '17 at 16:07
  • I can see why that would be confusing. Either will work. I need to capture all of the information in column E until the end of the spreadsheet. – CZ31 Jun 01 '17 at 12:53
  • I did and it worked. I'm not sure why it did, but I really do appreciate your help. – CZ31 Jun 01 '17 at 13:25