Copy Paste VBA Code for exporting data to another worksheet

Jan Kalop

Active Member
Joined
Aug 3, 2012
Messages
367
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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
973
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Aug 3, 2012
Messages
367
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.
 
Last edited:

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
973
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Aug 3, 2012
Messages
367
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

Well-known Member
Joined
Nov 26, 2020
Messages
973
Office Version
  1. 2016
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

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