VBA - Copy data to another sheet, next available cell

[JLS01]

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,

 

[WarPigl3t]

Range of where it is "filled out" plz.

 

[vcoolio]

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]

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

Private Sub CommandButton1_Click()
    Call subMoveIt
End Sub

Then in a module

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.

 

[jolivanes]

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

 

[JLS01]

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]

Hello JLS01,

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

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:-

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

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