Copy data from specific location and paste as value in different tab within file.

[ankrups]

Hello,

I have a situation that I have 30 tab in a file and want to copy specific location from all tabs (excluding 5 tabs that I described) and past into specific location in "IMPORT" tab one after another row.

Note:
It should be coded such a way that any future creation of tab with the same formate, should copy as per rest.
Should exclude 5 tabs that I described including "IMPORT" tab.

Please help me out to overcome this scenario.

 

[rjwebgraphix]

A lot more information is needed to cover the whole thing, but a general concept would be like this.

Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name <> "IMPORT" Or ws.Name <> "5 other tabs that I described" Then
        'copy the data
    End If
End Sub

You're excluding the tabs you don't want to touch with your if statement. All other sheets will run the code, but you did not specify what you want where, so that code is omitted.

 

[ankrups]

Thanks mate,

What I need is as below.

Product group 1 (Tab) contains mulitple products (could be more than 10) listed on Column B. Each product contains 6 blocks of rows but the same format and data in it. I need to copy from each block. For example, N:35 to AK35 block (which is one of the block of row for individual product) and continue until they find products in that "Product group 1" tab"

and Paste into Import tab

on Column A as Product (which is from Product Group (Tab): Column B1)
on Column B as Customer (which is from Product Group (Tab): D23, (this fixed location for customers)
on Column C onwards Forecast data from current month to next 12 months..

I hope this explains what I want to achieve.

 

[rjwebgraphix]

Can you post some dummy data to put that into perspective? Along with the dummy data, also post what the desired result would be from the dummy data. I might be able to figure it out without it, but it reads a little confusing, so I'd like to see if it is how I think it is. Such as..... Is Column B a part number/product number? Too many question that would be answered quickly with some dummy data and the desired result from that data. No explanation needed, just the data and what that dummy data should look like when its done....

On top of that, You said there were 5 or 6 worksheets you did not want to copy from, but needs to be able to handle additional worksheets, so I will need all sheet names to not copy from.

 

[ankrups]

Below is Dummy data that currently written on one of the 30 tab. Same format in each 30 tab. Just products will change.

TAB 1:
CUSTOMER NAME: JOHN LTD.

PRODUCT NAME KEY LINES JAN 13 FEB 13
XYZ BASELINE 1200 1400
PROMO 5000

123 BASELINE 2200 3300
PROMO 5000

QWE BASELINE 1200 1400
PROMO 5000




TAB 2:
CUSTOMER NAME: LAWRENCE LTD.

PRODUCT NAME KEY LINES JAN 13 FEB 13
XYZ BASELINE 12000 14000
PROMO 50000

123 BASELINE 22000 33000
PROMO 50000

QWE BASELINE 12000 14000
PROMO 50000



OUTPUT DUMMY DATA

IMPORT TAB:
PRODUCT NAME CUSTOMER NAME KEY LINES JAN 13 FEB 13
XYZ JOHN LTD BASELINE 1200 1400
XYZ JOHN LTD PROMO 5000
XYZ LAWRENCE LTD BASELINE 12000 14000
XYZ LAWRENCE LTD PROMO 50000


LIST OF TAB SHOULD BE EXCLUDE FROM COPYING
1. IMPORT
2. SKU DATA
3. CUSTOMER MAPPING
4. RAW DATA
5. MISC DATA

 

[rjwebgraphix]

Doesn't look too hard to do. I'm having trouble telling exactly what is in each cell.

Might I recommend an Excel to HTML maker so it breaks it down by cell and not so confusing....

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

You can find Mr Excel's HTML maker on the 2nd post down.

 

[rjwebgraphix]

Can you verify that this looks correct..... Or is Promo and 5000 in two different cells?

Excel Workbook
	A	B	C	D
1	CUSTOMER NAME:	JOHN LTD.
2
3	PRODUCT NAME	KEY LINES	01/13/13	02/13/13
4	XYZ	BASELINE	1200	1400
5	PROMO 5000
6
7	123	BASELINE	2200	3300
8	PROMO 5000
9
10	QWE	BASELINE	1200	1400
11	PROMO 5000
Sheet

 

[ankrups]

PROMOgoes to key lines column and 5000 goes to 01/13/13 column..

 

[rjwebgraphix]

This worked for the Dummy data as laid out in the table in my previous message. I believe I have it right.. If not, let me know what went wrong. Be sure to use this on a backup to be safe.

Sub WStoImport()

Dim WsLr As Long
Dim WSRow As Long
Dim IsFirst As Boolean
Dim IsLr As Long
Dim ws As Worksheet
Dim ImportSht As Worksheet
Set ImportSht = Sheets("IMPORT")

For Each ws In Worksheets
    If ws.Name <> "MISC DATA" And _
       ws.Name <> "RAW DATA" And _
       ws.Name <> "CUSTOMER MAPPING" And _
       ws.Name <> "SKU DATA" And _
       ws.Name <> "IMPORT" Then
       
        IsLr = ImportSht.Range("A" & Rows.Count).End(xlUp).Row
        WsLr = ws.Range("A" & Rows.Count).End(xlUp).Row
        'Set headers if not already set
        If IsLr = 1 And ImportSht.Cells(1, "A") = "" Then
            ImportSht.Cells(1, "A") = ws.Cells(3, "A")
            ImportSht.Cells(1, "B") = ws.Cells(1, "A")
            ImportSht.Cells(1, "C") = ws.Cells(3, "B")
            ImportSht.Cells(1, "D") = ws.Cells(3, "C")
            ImportSht.Cells(1, "E") = ws.Cells(3, "D")
        End If
        WSRow = 4
        IsFirst = True
        For WSRow = 4 To WsLr + 1
            IsLr = ImportSht.Range("A" & Rows.Count).End(xlUp).Row
            If IsFirst And ws.Cells(WSRow, "A") <> "" Then
                ImportSht.Cells(IsLr + 1, "A") = ws.Cells(WSRow, "A")
                ImportSht.Cells(IsLr + 1, "B") = ws.Cells(1, "B")
                ImportSht.Cells(IsLr + 1, "C") = ws.Cells(WSRow, "B")
                ImportSht.Cells(IsLr + 1, "D") = ws.Cells(WSRow, "C")
                ImportSht.Cells(IsLr + 1, "E") = ws.Cells(WSRow, "D")
                IsFirst = False
            ElseIf Not IsFirst And ws.Cells(WSRow, "A") <> "" Then
                ImportSht.Cells(IsLr + 1, "A") = ws.Cells(WSRow - 1, "A")
                ImportSht.Cells(IsLr + 1, "B") = ws.Cells(1, "B")
                ImportSht.Cells(IsLr + 1, "B") = ws.Cells(WSRow, "A")
                IsFirst = True
            End If
        Next WSRow
        
    End If
Next ws

End Sub

Edit: Didn't see your post before posting this. Give me a bit to adjust for the change.

 

[rjwebgraphix]

Try this one.... Let me know how it goes....

Sub WStoImport()

Dim WsLr As Long
Dim WSRow As Long
Dim IsFirst As Boolean
Dim IsLr As Long
Dim ws As Worksheet
Dim ImportSht As Worksheet
Set ImportSht = Sheets("IMPORT")

For Each ws In Worksheets
    If ws.Name <> "MISC DATA" And _
       ws.Name <> "RAW DATA" And _
       ws.Name <> "CUSTOMER MAPPING" And _
       ws.Name <> "SKU DATA" And _
       ws.Name <> "IMPORT" Then
       
        IsLr = ImportSht.Range("A" & Rows.Count).End(xlUp).Row
        WsLr = ws.Range("A" & Rows.Count).End(xlUp).Row
        'Set headers if not already set
        If IsLr = 1 And ImportSht.Cells(1, "A") = "" Then
            ImportSht.Cells(1, "A") = ws.Cells(3, "A")
            ImportSht.Cells(1, "B") = ws.Cells(1, "A")
            ImportSht.Cells(1, "C") = ws.Cells(3, "B")
            ImportSht.Cells(1, "D") = ws.Cells(3, "C")
            ImportSht.Cells(1, "E") = ws.Cells(3, "D")
        End If
        IsFirst = True
        For WSRow = 4 To WsLr + 1
            IsLr = ImportSht.Range("A" & Rows.Count).End(xlUp).Row
            If IsFirst And (ws.Cells(WSRow, "A") <> "" Or _
                            ws.Cells(WSRow, "B") <> "" Or _
                            ws.Cells(WSRow, "C") <> "" Or _
                            ws.Cells(WSRow, "D") <> "") Then
                
                ImportSht.Cells(IsLr + 1, "A") = ws.Cells(WSRow, "A")
                ImportSht.Cells(IsLr + 1, "B") = ws.Cells(1, "B")
                ImportSht.Cells(IsLr + 1, "C") = ws.Cells(WSRow, "B")
                ImportSht.Cells(IsLr + 1, "D") = ws.Cells(WSRow, "C")
                ImportSht.Cells(IsLr + 1, "E") = ws.Cells(WSRow, "D")
                IsFirst = False
            ElseIf Not IsFirst And (ws.Cells(WSRow, "A") <> "" Or _
                                    ws.Cells(WSRow, "B") <> "" Or _
                                    ws.Cells(WSRow, "C") <> "" Or _
                                    ws.Cells(WSRow, "D") <> "") Then
                
                ImportSht.Cells(IsLr + 1, "A") = ws.Cells(WSRow - 1, "B")
                ImportSht.Cells(IsLr + 1, "B") = ws.Cells(1, "B")
                ImportSht.Cells(IsLr + 1, "C") = ws.Cells(WSRow, "B")
                ImportSht.Cells(IsLr + 1, "D") = ws.Cells(WSRow, "C")
                IsFirst = True
            End If
        Next WSRow
        
    End If
Next ws

End Sub