Copying data creating new sheets based on template

Johnson Boni

New Member
Joined
Feb 9, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi all

I have worked long on this project and got through 90 percent of data evaluation and compiling on my own. Which is my strong point. However, copying data into a new sheet is something I have never been good at because I hardly performed this task.

I need help in copying data into a template and creating new templates based on this data set. None of the codes i tried to write worked so I'm not going to try and share it here. I will describe in detail my situation as best i can.

I have multiple data sets in one workbook, lets call this data,xls, sheet 1. And I have a template in another workbook, template.xls, sheet 1. In the data file, sheet 1, there is data in A1:E10, A15:E18 also G1:L10, G15:L20, and so on. The second range in all will fluctuate. I am trying to get the template to copy into a new workbook, sheet1, with the data set (A:E) to be copied into it at C1 and the second at P1 then the template to copy to sheet 2 with data set G:L to be copied into cells C1 and P1 just like the first. But like i said the second data set will fluctuate so formatting will have to stop when the data ends.

I know this is a huge request but can anyone help? I understand its just creating a new workbook. And copying the template into a woksheet, copying data, but i cannot figure this out.

I thought i could attach an excel spreadsheet as an example but it isn't allowed. and image files are too large to attach.

Any help is appreciated.

Thank you.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
there is data in A1:E10, A15:E18 also G1:L10, G15:L20, and so on.
If I understood correctly, you have a data.xlsx book, with a sheet1 like this:
DATA.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
112345123451234512345
2ABCDEGHIJKMNOPQSTUVW
3A3B3C3D3E3G3H3I3J3K3M3N3O3P3Q3S3T3U3V3W3
4A4B4C4D4E4G4H4I4J4K4M4N4O4P4Q4S4T4U4V4W4
5A5B5C5D5E5G5H5I5J5K5M5N5O5P5Q5S5T5U5V5W5
6A6B6C6D6E6G6H6I6J6K6M6N6O6P6Q6S6T6U6V6W6
7A7B7C7D7E7G7H7I7J7K7M7N7O7P7Q7S7T7U7V7W7
8A8B8C8D8E8G8H8I8J8K8M8N8O8P8Q8S8T8U8V8W8
9A9B9C9D9E9G9H9I9J9K9M9N9O9P9Q9S9T9U9V9W9
10A10B10C10D10E10G10H10I10J10K10M10N10O10P10Q10S10T10U10V10W10
11
15A15B15C15D15E15G15H15I15J15K15M15N15O15P15Q15S15T15U15V15W15
16A16B16C16D16E16G16H16I16J16K16M16N16O16P16Q16S16T16U16V16W16
17A17B17C17D17E17G17H17I17J17K17M17N17O17P17Q17S17T17U17V17W17
18A18B18C18D18E18G18H18I18J18K18M18N18O18P18Q18S18T18U18V18W18
19
Sheet1

So you want in a new book to generate 4 sheets like these:
New Book.xlsx
ACDEFGHPQRSTU
112345A15B15C15D15E15
2ABCDEA16B16C16D16E16
3A3B3C3D3E3A17B17C17D17E17
4A4B4C4D4E4A18B18C18D18E18
5A5B5C5D5E5
6A6B6C6D6E6
7A7B7C7D7E7
8A8B8C8D8E8
9A9B9C9D9E9
10A10B10C10D10E10
11
Sheet1

New Book.xlsx
ACDEFGHPQRSTU
112345G15H15I15J15K15
2GHIJKG16H16I16J16K16
3G3H3I3J3K3G17H17I17J17K17
4G4H4I4J4K4G18H18I18J18K18
5G5H5I5J5K5
6G6H6I6J6K6
7G7H7I7J7K7
8G8H8I8J8K8
9G9H9I9J9K9
10G10H10I10J10K10
11
Sheet2

New Book.xlsx
ACDEFGHPQRSTU
112345M15N15O15P15Q15
2MNOPQM16N16O16P16Q16
3M3N3O3P3Q3M17N17O17P17Q17
4M4N4O4P4Q4M18N18O18P18Q18
5M5N5O5P5Q5
6M6N6O6P6Q6
7M7N7O7P7Q7
8M8N8O8P8Q8
9M9N9O9P9Q9
10M10N10O10P10Q10
11
Sheet3

New Book.xlsx
ACDEFGHPQRSTU
112345S15T15U15V15W15
2STUVWS16T16U16V16W16
3S3T3U3V3W3S17T17U17V17W17
4S4T4U4V4W4S18T18U18V18W18
5S5T5U5V5W5
6S6T6U6V6W6
7S7T7U7V7W7
8S8T8U8V8W8
9S9T9U9V9W9
10S10T10U10V10W10
11
Sheet4

_________________________________________________________________________________________________
If the above is correct, then put the following macro in your "data" book.
Before running the macro, you must open your workbook "template.xlsx".
Now yes, run the macro.

VBA Code:
Sub Copying_data()
  Dim wb3 As Workbook, nwSh As Boolean
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim j As Long, n As Long
  
  Application.ScreenUpdating = False
  Set sh1 = ThisWorkbook.Sheets("Sheet1")
  Set sh2 = Workbooks("Template").Sheets("Sheet1")
  sh2.Copy
  Set wb3 = ActiveWorkbook
  
  For j = 1 To sh1.Cells(1, Columns.Count).End(1).Column Step 6
    If nwSh Then sh2.Copy After:=wb3.Sheets(wb3.Sheets.Count)
    nwSh = True
    With wb3.Sheets(wb3.Sheets.Count)
      n = n + 1
      .Name = "Sheet" & n
      .Range("C1").Resize(10, 5).Value = sh1.Cells(1, j).Resize(10, 5).Value
      .Range("P1").Resize(4, 5).Value = sh1.Cells(15, j).Resize(4, 5).Value
    End With
  Next
End Sub
 
Last edited:
Upvote 0
This seems to be exactly what i need but the second data set changes. At times there are three rows of data and up to ten rows of data, it fluctuates.

I will attempt this complete example this evening when time comes available.

Thank you.
 
Upvote 0
Ok I got an example matching it as close as i can to what you provided. Have a look:

example.xls
ABCDEFGHIJKLMNOPQRSTUVWX
1Data Set in data.xls/sheet 1
2ABCDEGHIJKMNOPQSTUVW
312345123451234512345
4ABCDEGHIJKMNOPQSTUVW
5A3B3C3D3E3G3H3I3J3K3M3N3O3P3Q3S3T3U3V3W3
6A4B4C4D4E4G4H4I4J4K4M4N4O4P4Q4S4T4U4V4W4
7A5B5C5D5E5G5H5I5J5K5M5N5O5P5Q1S5T5U5V5W5
8A6B6C6D6E6G6H6I6J6K6M6N6O6P6Q2S6T6U6V6W6
9A7B7C7D7E7G7H7I7J7K7M7N7O7P7Q3S7T7U7V7W7
10A8B8C8D8E8G8H8I8J8K8M8N8O8P8Q4S8T8U8V8W8
11A9B9C9D9E9G9H9I9J9K9M9N9O9P9Q1S9T9U9V9W9
12A10B10C10D10E10G10H10I10J10K10M10N10O10P10Q2S10T10U10V10W10
13
17A15B15C15D15G15H15I15J15M15N15O15P15S15T15U15V15
18A16B16C16D16G16H16I16J16M16N16O16P16
19A17B17C17D17M17N17O17P17
20A18B18C18D18
21
22template in template.xls/sheet1
23Data Value 1
24AABBCCDDEE
25
26
27
28
29
30
31
32
33
34
35
36Data Value 2
37TTUUVVWW
38
39
40
41
42
43
44
45New Workbook
46New Sheet 1New Sheet 2New Sheet 3New Sheet 4
47Data Value 1Data Value 1Data Value 1Data Value 1
48AABBCCDDEEAABBCCDDEEAABBCCDDEEAABBCCDDEE
4912345123451234512345
50ABCDEGHIJKMNOPQSTUVW
51A3B3C3D3E3G3H3I3J3K3M3N3O3P3Q3S3T3U3V3W3
52A4B4C4D4E4G4H4I4J4K4M4N4O4P4Q4S4T4U4V4W4
53A5B5C5D5E5G5H5I5J5K5M5N5O5P5Q1S5T5U5V5W5
54A6B6C6D6E6G6H6I6J6K6M6N6O6P6Q2S6T6U6V6W6
55A7B7C7D7E7G7H7I7J7K7M7N7O7P7Q3S7T7U7V7W7
56A8B8C8D8E8G8H8I8J8K8M8N8O8P8Q4S8T8U8V8W8
57A9B9C9D9E9G9H9I9J9K9M9N9O9P9Q1S9T9U9V9W9
58A10B10C10D10E10G10H10I10J10K10M10N10O10P10Q2S10T10U10V10W10
59
60Data Value 2Data Value 2Data Value 2Data Value 2
61TTUUVVWWTTUUVVWWTTUUVVWWTTUUVVWW
62A15B15C15D15G15H15I15J15M15N15O15P15S15T15U15V15
63A16B16C16D16G16H16I16J16M16N16O16P16
64A17B17C17D17M17N17O17P17
65A18B18C18D18
66
Sheet1
 
Upvote 0
Yes the MACRO worked fine i just need to adjust it so the second data set only displays the lines of data available.

thank you.
 
Upvote 0
You can change the 5 to a bigger number, say 50 and it should work:

.Range("P1").Resize(4, 50).Value = sh1.Cells(15, j).Resize(4, 50).Value
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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
Back
Top