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

ankrups

Board Regular
Joined
Mar 14, 2006
Messages
120
Office Version
  1. 2021
Platform
  1. Windows
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
A lot more information is needed to cover the whole thing, but a general concept would be like this.

Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
Can you verify that this looks correct..... Or is Promo and 5000 in two different cells?
Excel Workbook
ABCD
1CUSTOMER NAME:JOHN LTD.
2
3PRODUCT NAMEKEY LINES01/13/1302/13/13
4XYZBASELINE12001400
5PROMO 5000
6
7123BASELINE22003300
8PROMO 5000
9
10QWEBASELINE12001400
11PROMO 5000
Sheet
 
Upvote 0
PROMOgoes to key lines column and 5000 goes to 01/13/13 column..
 
Upvote 0
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.

Code:
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.
 
Last edited:
Upvote 0
Try this one.... Let me know how it goes....

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,203,456
Messages
6,055,543
Members
444,794
Latest member
HSAL

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