Copying and Pasting data into a template

[Broccoli]

Hi there

I was wondering if the below is possible using VBA?

I have a spreadsheet containing many sheets with different products.

Each Product sheet is comprised as follows:
No header row
Data contained from columns A to T
The rows vary in number

I also have a sheet named Template in the same workbook as the products.
What I'm trying to do is use VBA to copy each of the product tabs and paste them one by one into the Template sheet(paste into cell a3) and then save as a separate workbook based on the contents of cell F3 in the template.

The Template worksheet is comprised as follows:
Blank from columns A to T
Header rows in rows 1 and 2
Formula contained in columns U to Z(starting from row 3)

 

[Mark F]

Sub CombineRangefromEachSheet()
'========================================================================
' THIS MERGES ALL ROWS IN COLUMNS A To T FROM ALL WORKSHEETS
' IN WORKBOOK ONTO A "TEMPLATE" SHEET COLUMNS A TO T
'========================================================================
    Dim wsTemplate As Worksheet, SourceSheet As Worksheet
    Dim LRTemplate As Long, LRSource As Long
    Set wsTemplate = Sheets("Template")
    LRTemplate = 3
    For Each SourceSheet In Worksheets
        If SourceSheet.Name <> wsTemplate.Name Then
            LRSource = SourceSheet.Range("A" & Rows.Count).End(xlUp).Row
            SourceSheet.Range("A1:T" & LRSource).Copy wsTemplate.Range("A" & LRTemplate)
            LRTemplate = wsTemplate.Range("A" & Rows.Count).End(xlUp).Row + 1
 
        End If
    Next SourceSheet
End Sub

Hi

Give this code a go. Paste into the "This Workbook" sheet in VBE.

Please let me know how you get on

Mark

 

[jim may]

Mark, Wouldn't your excellant code be better placed in a standard module versus being placed in the "ThisWorkbook" module. Aren't Event modules (Worksheets and ThisWirkbook reserved for EVENT-TYPE procedures? Again, your excellant code is a straight procedure, not involing an Event.

Jim

 

[Mark F]

Jim - Thanks for the hint

I am still working on a "Trial and Error" basis, without fully understanding Modules / Events etc, ie if it works................

Next job today though - look it up and understand the differences!

All the best

Mark

 

[jim may]

Mark,
All the best to you -- I'm 6 years "trial and error" - and have all the scars to prove it.
I've got over 16 Excel Books and my wife that I sleep with. I'm Glad to help as I have been helped so much over the years by this Board.

Good Luck