CherylMc
07-12-2017, 10:24 PM
I have a workbook with several sheets containing hundreds of price grids that need to be split into individual workbooks (one price grid per workbook). The level of VBA required here largely exceeds my basic knowledge of the subject and I would greatly appreciate any help you are willing to give.
Each worksheet has a number of these grids of varying sizes separated by a blank row:
Product1
600
800
1000
1200
1400
1600
1800
2000
2200
Product2
600
985
1269
1566
1779
2123
2287
2441
2599
2733
Product3
800
1441
1998
2589
2963
3678
3925
4148
4374
4548
Product4
1000
1555
2179
2844
3258
4066
4334
4574
4817
5001
1200
1667
2363
3101
3553
4456
4745
5001
5259
5454
1400
1782
2543
3357
3849
4844
5154
5427
5703
5906
1600
1896
2724
3613
4144
5233
5565
5854
6146
6359
1800
2009
2907
3868
4439
5621
5972
6280
6592
6812
2000
2123
3088
4124
4734
6008
6382
6707
7035
7265
2200
2238
3272
4381
5031
6399
6792
7134
7478
7718
2400
2350
3452
4637
5329
6786
7202
7560
7921
2600
2464
3636
4892
5624
7176
7613
7986
Product5
600
800
1000
1200
1400
1600
1800
2000
2200
Product6
600
1073
1408
1762
2004
2421
2602
2770
2940
3081
800
1667
2363
3101
3553
4456
4745
5001
5259
5454
1000
1818
2599
3436
3941
4964
5279
5559
5841
6044
1200
1966
2838
3771
4328
5469
5815
6116
6422
6638
1400
2114
3074
4104
4712
5979
6353
6674
6999
7232
1600
2264
3314
4439
5099
6487
6887
7232
7580
7823
1800
2412
3550
4774
5486
6996
7422
7790
8161
8417
2000
2560
3789
5109
5874
7504
7957
8348
8741
9007
2200
2710
4026
5443
6261
8013
8492
8905
9322
9601
2400
2858
4266
5778
6648
8522
9030
9464
9899
10191
2600
3006
4502
6113
7035
9030
9564
10021
10480
10786
I need to make one Excel file/workbook per product. The name of each workbook will be the product name from column A and the contents must be the full grid without column A, so just all the numbers. Each workbook can be saved in ActiveWorkbook.Path. The first grid in the given example would generate 4 files named Product1, Product2, Product3 and Product4. Each file would contain only the pricing grid starting in cell A1, which as shown in the example is sometimes empty.
The following code selects each price grid block on the worksheet but then I am unsure how to loop through the data to extract the product names. "Sheet1" and "A1" in this example would also need to be dynamic values, something that would loop through all sheets and find all the blocks on each sheet.
Sub DynamicRange()
Dim sht As Worksheet
Dim StartCell As Range
Set sht = Worksheets("Sheet1")
Set StartCell = Range("A1")
StartCell.CurrentRegion.Select
End Sub
Please help? :bug:
Each worksheet has a number of these grids of varying sizes separated by a blank row:
Product1
600
800
1000
1200
1400
1600
1800
2000
2200
Product2
600
985
1269
1566
1779
2123
2287
2441
2599
2733
Product3
800
1441
1998
2589
2963
3678
3925
4148
4374
4548
Product4
1000
1555
2179
2844
3258
4066
4334
4574
4817
5001
1200
1667
2363
3101
3553
4456
4745
5001
5259
5454
1400
1782
2543
3357
3849
4844
5154
5427
5703
5906
1600
1896
2724
3613
4144
5233
5565
5854
6146
6359
1800
2009
2907
3868
4439
5621
5972
6280
6592
6812
2000
2123
3088
4124
4734
6008
6382
6707
7035
7265
2200
2238
3272
4381
5031
6399
6792
7134
7478
7718
2400
2350
3452
4637
5329
6786
7202
7560
7921
2600
2464
3636
4892
5624
7176
7613
7986
Product5
600
800
1000
1200
1400
1600
1800
2000
2200
Product6
600
1073
1408
1762
2004
2421
2602
2770
2940
3081
800
1667
2363
3101
3553
4456
4745
5001
5259
5454
1000
1818
2599
3436
3941
4964
5279
5559
5841
6044
1200
1966
2838
3771
4328
5469
5815
6116
6422
6638
1400
2114
3074
4104
4712
5979
6353
6674
6999
7232
1600
2264
3314
4439
5099
6487
6887
7232
7580
7823
1800
2412
3550
4774
5486
6996
7422
7790
8161
8417
2000
2560
3789
5109
5874
7504
7957
8348
8741
9007
2200
2710
4026
5443
6261
8013
8492
8905
9322
9601
2400
2858
4266
5778
6648
8522
9030
9464
9899
10191
2600
3006
4502
6113
7035
9030
9564
10021
10480
10786
I need to make one Excel file/workbook per product. The name of each workbook will be the product name from column A and the contents must be the full grid without column A, so just all the numbers. Each workbook can be saved in ActiveWorkbook.Path. The first grid in the given example would generate 4 files named Product1, Product2, Product3 and Product4. Each file would contain only the pricing grid starting in cell A1, which as shown in the example is sometimes empty.
The following code selects each price grid block on the worksheet but then I am unsure how to loop through the data to extract the product names. "Sheet1" and "A1" in this example would also need to be dynamic values, something that would loop through all sheets and find all the blocks on each sheet.
Sub DynamicRange()
Dim sht As Worksheet
Dim StartCell As Range
Set sht = Worksheets("Sheet1")
Set StartCell = Range("A1")
StartCell.CurrentRegion.Select
End Sub
Please help? :bug: