Copy Certain Columns of Data from One Sheet to Another

[aznbozo]

Hi all,

I know that this is a very common problem but I am still not getting the answer I am looking for.

I have two sheets (Sheet1 and Sheet2)

The point of Sheet1 is to be able to put in new data and Sheet2 acts as the Historical Price Sheet.

Sheet1 has 25 columns of data but Sheet2 only requires 15.

I'm looking for a macro that is able to add only those certain columns to Sheet2.

Thanks in advance.

 

[stnkynts]

Please be more specific on which columns from sheet 1 you want transfered to sheet 2 and in what order.

 

[timjs]

What if you just start recording a macro and use your mouse to select across the column letters on Sheet 1 and then select Sheet 2, and then paste the columns where you'd like them located on Sheet 2. Click back on Sheet 1 to return to Sheet 1 and then end the macro recording.

Perhaps I'm not understanding what you're trying to do if this solution doesn't work out. The other thing that I might do is to go to an individual cell on Sheet 2 and type in an equals sign, then select Sheet 1 and click on the corresponding cell that has the information that you want to appear on Sheet 2 and then hit enter. The resulting formula in the cell on Sheet 2 should look like:

='Sheet1'!A1

So whatever is in cell A1 on Sheet1 will appear in the corresponding cell on Sheet2. Once that works, then just copy and paste the formula into the remaing cells on Sheet 2.

Hope that helps.

 

[aznbozo]

Sheet1's purpose is for the user to be able to add rows of data(25columns)

Sheet2's purpose is to hold all the historical data so it already had thousands of rows of data but I am trying to make it easier for the user so all they have to do to add data is to place it in Sheet1 and then with a click of a button certain column's information will go to Sheet2.

If you are asking why do they not just add the data rows to Sheet2 directly the reason is b/c Sheet1's data of 25 columns is being used elsewhere (Sheet3).

So Sheet1 is serving as the home page.

I have tried to record the macro but it does not do what I want it to do.

 

[stnkynts]

Let me see if I can explain where you are being unclear.

Do all 25 columns of sheet 1 need to be transfered to sheet 2?
Do they need to be transfered after the last column on sheet 2?
What rows of the columns on sheet 1 need to be transfered?
Does the data need to be cleared on sheet 1 after it has been transfered?
Are their any formulas that need to be transfered or just values?
You indicated "the click of a button". Are you saying you need help setting up a command button or just a standard macro?
Etc, etc, etc.

It's questions like these that illustrate how poor your post is.

If i were to make a guess at what you want i would just create a macro to copy the used range on sheet 1 and paste all after the last column on sheet 2. Odds are something would not be the way you truely want it.

 

[aznbozo]

I'm sorry for the poor post.

1. I would like only 15 columns of Sheet1 to be copied to Sheet2. The point of this sheet is so that the user can list out rows of data and there are certain data points in certain columns that I want to move over.

2. It is not being transferred to the last column of Sheet2 but rather the last row of Sheet2. Sheet2 acts as the sheet that holds ALL previous data so the new data that comes into Sheet1 needs to be moved to the bottom of the last row. Essentially appending it.

3. I'm sorry for focusing on columns so much but I am much more concerned with the rows of data and that there are certain columns of that data that I would like to copy over.

I am looking for something like this below link. The difference is that her sheets have the same columns but for me my columns are not the same. The title of the columns are the same but not the location.
http://excel.bigresource.com/Track/excel-bwdYDtQf/

Thank you and sorry for being poor poster.

 

[stnkynts]

The difference is that her sheets have the same columns but for me my columns are not the same.

So are you saying that Column A in Sheet 1 does not need to be transfered to Column A in Sheet 2. It for example needs to be transfered to Column E? Column B Sheet 1 would go to Column H Sheet 2, etc, etc.

Or is it just a transfer respectively (15 columns on Sheet1) so Columns A thru O on Sheet 1 transfered to Columns A thru O on Sheet 2 below the last row?

 

[stnkynts]

At this point I think it might be best to give you my best guess on what you are trying to say. Try this:

Sub testmacro()
Dim ws1 As Worksheet:  Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet:  Set ws2 = Sheets("Sheet2")
Dim lastrow1 As Long, lastrow2 As Long
 
lastrow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
 
ws1.Range("A1:O" & lastrow1).Copy Destination:=ws2.Range("A" & lastrow2).Offset(1, 0)
 
End Sub

You might want to try this on a copy of your workbook as I have a sneaking suspicion that you want something entirely different. I don't want to ruin any of your good data.