Copy Paste VBA Code for exporting data to another worksheet

[Jan Kalop]

In worksheet "Collect" cells $A$10:$K$10 I have data which is collected with specific requirements from other worksheets, by a few macro buttons. Every time, when new result is produce I am switching to worksheet "DATA" and with VBA Code macro button
"Worksheets ("DATA"). Range ("A1: K1"). Value = Worksheets ("Collect"). Range ("A10: K10"). Value" ....import that data in first no occupied row.

In worksheet "DATA" I have 20 macro buttons like above with VBA Code coresponding to every row from A1:K1 to A20:K20.
By switchings to "DATA" worksheet every time when new data in worksheet "Collect" is formed and pressing next macro button coresponding to next not occupied available row, can be a bit tedious and sluggish.

I am curious if it is possible to build a VBA Code with only one macro button in worksheet "Collect" to perform that tasks and export data every time the values change, from "Collect" to next empty A?:A? i "DATA" worksheet.

 

[Zot]

I t is possible to run one main macro that call other macros to perform their tasks.

My understanding was that the macro buttons in Collect are to import to collect data from other worksheets and macro buttons in Data are to import data from Collect.

You can run one macro to do all the data collection and then export data to Data sheet in one go. It is possible if my understanding is right.

If possible, probably you can upload your workbook so that others can help I suppose.

 

[Jan Kalop]

I t is possible to run one main macro that call other macros to perform their tasks.

My understanding was that the macro buttons in Collect are to import to collect data from other worksheets and macro buttons in Data are to import data from Collect.

You can run one macro to do all the data collection and then export data to Data sheet in one go. It is possible if my understanding is right.

If possible, probably you can upload your workbook so that others can help I suppose.

The macros whitch collect data from other worksheets are OK and I want leave them as they are.
My intention is to remove all macro button in "DATA" and create a code that will send each time the new data is created, but in such a way, that using one and the same macro button to export data to the "DATA" worksheet with the ability to read which next line is free and perform task.
I use Excel 2010.

 

[Zot]

How do you handle the last empty row with macro in Data? It is no difference if you run it from Collect.

You can just run macro on Collect and let it copy macro in Collect and paste it in Data. This way there is no need to have any macro in Data. Maybe you can paste one of the macro in Data as example.

 

[Jan Kalop]

How do you handle the last empty row with macro in Data? It is no difference if you run it from Collect.

You can just run macro on Collect and let it copy macro in Collect and paste it in Data. This way there is no need to have any macro in Data. Maybe you can paste one of the macro in Data as example.

Sub FROM_Collect_copyPAST_2_DATA()
Application.ScreenUpdating = False
Worksheets("DATA").Range("A1:K1").Value = Worksheets("COLLECT").Range("A10:K10").Value
Application.ScreenUpdating = True
End Sub

Now the problem is that I have to have 20 of them to be able to paste in correct row, but if the same VBA would run from "Collect" then that code will overwrite all the time previous record data, every time the new data is created.
I would like to add to that code function that would distinguish which free row in "DATA" is available, so next new data from "COLLECT" is paste to a new line

to make it maybe easier, with the other macro buttons I perform task to create new data in A10:K10 in "Collect", then I would like to have extra macro button in "Collect" to send data to "DATA" to free space in range A1:K1 to A20:K20. For sample I create 1st data and press button to send it to A1:K1 then I create 2nd data and by pressing the same button send to A2:K2 and so on.
The code "Worksheets (" DATA "). Range (" A1: K1 "). Value = Worksheets (" Collect "). Range (" A10: K10 "). Value" beside simple copy paste function would have to have the abilty to read which next row is empty in "DATA" to perform paste. ALL procces would be run from "Collect"

Sorry for may english.

 

[Zot]

Here is a simple guide. Note that I named worksheet to simplify writing code.
You can put this sub in Collect. Can put all other code as well in one subroutine

Sub FROM_Collect_copyPAST_2_DATA_Alt()

Dim wsCollect As Worksheet, wsData As Worksheet

Set wsCollect = ActiveWorkbook.Sheets("Collect")
Set wsData = ActiveWorkbook.Sheets("Data")

Application.ScreenUpdating = False
wsData.Range("A1:K1") = wsCollect.Range("A10:K10")
' ### Another method is ###
' wsCollect.Range("A10:K10").Copy wsData.Range("A1")

' ### To get last row like pressing Ctrl + ArrowDown ###
' endRow = wsData.Range("A1").End(xlDown).Row

' ### To get last row from bottom up like pressing Ctrl + ArrowUp ###
' endRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

' ### So instead of using wsData.Range("A1:K1"), you can write like this ###
' wsData.Range("A1","K" & endRow)

Application.ScreenUpdating = True

End Sub