VBA - Copy data to another sheet, next available cell

JLS01

New Member
Joined
Feb 27, 2016
Messages
11
Hi,

New to VBA so here goes, I have a spreadsheet where sheet one (DATA) is for all captured data to be added. Once it is filled out I want to have a command button that populates another sheet (TIMELINE), however each time the command button is pressed I would like the data to be entered into the next cell down.

Any help would be greatly appreciated.



Thanks,
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,036
Hello JLS01,

Just a few questions first:-

1) You'll be entering all your data into sheet1 (DATA). I assume from row 2 on with row 1 having your headings. Correct?
2) How many columns will have data?
3) Is the whole data set to be transferred to sheet2 (TIMELINE) or just selected rows of data?
4) If it is just certain rows of data to be transferred, do you have a criterion to search or filter on?
5) By "next cell down", I assume that you mean the next available row in sheet2. Correct?

It would be ideal if you could upload a sample of your work book showing inputs (DATA sheet) and expected outputs (TIMELINE sheet). Be careful with any sensitive data.
You can upload a sample by using a free file sharing site such as DropBox and then posting back here with the link to your file.

Cheerio,
vcoolio.
 

billbrunt

Board Regular
Joined
Jul 17, 2009
Messages
178
Hi -

I'll try and help you with this.

One question, will the DATA sheet be cleared in between the adds before it gets appended to TIMELINE?

Asking because if it isn't cleared, the code would need track what was previously present. Let's assume it will be cleared.

For your command button
Code:
Private Sub CommandButton1_Click()
    Call subMoveIt
End Sub

Then in a module
Code:
Sub subMoveIt()
    Dim lsRngSelect As Range
    MsgBox (ActiveWorkbook.Worksheets("DATA").Range("$A$1").CurrentRegion.Address)
    Sheets("DATA").Range("$A$1").CurrentRegion.Cut
    Sheets("TIMELINE").Select
    Set lsRngSelect = Range("$A$1").End(xlDown).Offset(1)
    lsRngSelect.Select
    ActiveSheet.Paste
    Sheets("DATA").Select
End Sub


The code for the button is attached to the worksheet and while there may be a way to refer to ranges outside of the worksheet's button code, i found this code needs to separate the code in a module otherwise got the error "select method of range class failed"

- Bill Brunt

Let me know if that helped.
 
Last edited:

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,689
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Code:
Sub Maybe()
    Sheets("DATA").UsedRange.Copy Sheets("TIMELINE").Cells(Rows.Count, 1).End(xlUp).Offset(1)
End Sub
 

JLS01

New Member
Joined
Feb 27, 2016
Messages
11
Hi,

So I should have given a bit more info sorry.

Data is being entered into - Sheet1 (DATA) F21:F27. Once done I wish to have a 'Save' command button where it will populate this into Sheet2 (TIMELINE) F21 into B8, F22 into C8, F23 into D8 etc. After this has been done I have a 'Clear' command button that clears the cells in Sheet1 (DATA) F21:F27.

Next time data is entered into these cells and the 'Save' button is used the info in Sheet1 (DATA) F21:F27 will be copied into Sheet2 (TIMELINE), however this time F21 into B9, F22 into C9, F23 into D9 etc.

The idea is that the data captured in Sheet2 (TIMELINE) will eventually be graphed once enough data is present.


Thanks,
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,036
Hello JLS01,

Perhaps the following code will do the task for you:-


Code:
Sub CopyPaste()

Application.ScreenUpdating = False

          Dim ws, ws1 As Worksheet
          Dim ar As Variant
          Set ws = Sheet1
          Set ws1 = Sheet2

ws.Range("F21", Range("F" & Rows.Count).End(xlUp)).Copy
ws1.Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues, Transpose:=True
ws.Range("F21", Range("F" & Rows.Count).End(xlUp)).ClearContents

Application.ScreenUpdating = False
Application.CutCopyMode = False
Sheet2.Select

End Sub

Following is the link to my test work book for you to peruse. Click on the button to see it work.

https://www.dropbox.com/s/hj7vvjbz1rmvpcr/JLS01.xlsm?dl=0

The code assumes that the row headings in sheet1 are in row 20 and row 7 in sheet2.
The code clears the data from sheet1 F21:F27 once the transfer to sheet2 is completed.
Add new data to F21:F27 to see the new data added to the next row in sheet2.

I hope that this helps.

Cheerio,
vcoolio.

P.S.: If the range F21:F27 is fixed as the sole range into which data is entered, then change this part of the code:-


Code:
ws.Range("F21", Range("F" & Rows.Count).End(xlUp)).Copy
ws1.Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues, Transpose:=True
ws.Range("F21", Range("F" & Rows.Count).End(xlUp)).ClearContents

to

Code:
ws.Range("F21:F27").Copy
ws1.Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues, Transpose:=True
ws.Range("F21:F27").ClearContents
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top