Copy Cells to Separate Workbook Using VBA

ProWHP 2 Posts Saturday July 7, 2018Registration date July 8, 2018 Last seen - Jul 7, 2018 at 12:23 PM - Latest reply: ProWHP 2 Posts Saturday July 7, 2018Registration date July 8, 2018 Last seen
- Jul 8, 2018 at 12:56 AM
Hello,

I am attempting to take a range of data, in this case A1:I42, and copy it to a separate workbook. So I want to copy A1:I42 from Sheet 1 in Workbook 1 to Sheet 1 (Change the name of Sheet 1 to the date at the time it was copied) Workbook 2. What I would like to do is the following:

Assign the code to a button to run everything.

1. Copy the data
2. Open workbook 2
3. Create a sheet on the separate workbook named with the date from when the original sheet was created (Sheet 1 on Workbook 2 would be changed to 7/7/18 for example)
4. Paste the data
5. Save the separate workbook and close it

I would be doing that process about 5 times per week and would like subsequent sheets created and moved to the end of the current number of sheets. That way the sheets will progress in chronological order (7/7/18, 7/8/18, 7/9/18, et cetera)

I would like to leave the data on the original sheet until it's manually deleted so it would stop there. I've looked at a variety of ways to do this but I can't get everything I want it to do actually done. Can anyone tell me what code I can use to accomplish all of those things?

Sincerely,

ProWHP
See more 

Your reply

3 replies

xpcman 19103 Posts Wednesday October 8, 2008Registration dateModeratorStatus July 17, 2018 Last seen - Jul 7, 2018 at 01:03 PM
0
Thank you
I suggest you hire a VBA programmer to write custom code for you.
Respond to xpcman
Ambucias 53243 Posts Monday February 1, 2010Registration dateModeratorStatus July 18, 2018 Last seen - Jul 7, 2018 at 05:19 PM
0
Thank you
This CCM How-to should help you:

https://ccm.net/faq/24666-copy-data-to-another-workbook-in-excel
ProWHP 2 Posts Saturday July 7, 2018Registration date July 8, 2018 Last seen - Jul 8, 2018 at 12:56 AM
In order to make that work I will have to change the following right?

Dim wbTarget As Workbook
Dim wbThis As Workbook
Dim strName As String

Dim wbMyFirstWorkbook As Workbook

So on and so forth correct?
Respond to Ambucias