Thread: Affiliate Worksheet Help

your sample workbook2 should have the same data laid out how you would want it to appear from workbook1. Show us where everything goes, fill in a few rows so it's clear.

So, each time this happens, the data on Worksheet2 is completely erased and all the worksheet1 data is added in the appropriate columns?

Right, but you didn't answer my Excel question.

Ok, simple enough then... put this macro into a standard code module in WORKSHEET 2 since that is where the data is being collected:

Option Explicit

Sub ImportNewSKUData()
Dim wb1 As Workbook, wsCPO As Worksheet
Dim LR As Long, WasOPEN As Boolean

On Error Resume Next
Set wb1 = Workbooks("Worksheet 1.xls")
If Not wb1 Is Nothing Then
    WasOPEN = True
Else
    Set wb1 = Workbooks.Open("C:\MyExcelFiles\Worksheet 1.xls")
End If

Set wsCPO = ThisWorkbook.Sheets("CPO Outlet")
wsCPO.UsedRange.Offset(1).ClearContents

With wb1.Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("H2:H" & LR).Copy wsCPO.Range("A2")      'SKU>SKU
    .Range("E2:E" & LR).Copy wsCPO.Range("B2")      'NAME>post_title
    .Range("G2:G" & LR).Copy wsCPO.Range("C2")      'DESCRIPTION>post_content
    .Range("E2:E" & LR).Copy wsCPO.Range("D2")      'NAME>post_excerpt
    .Range("B2:B" & LR).Copy wsCPO.Range("M2")      'PROGRAMURL>url
    .Range("R2:R" & LR).Copy wsCPO.Range("N2")      'BUYURL>link
    .Range("T2:T" & LR).Copy wsCPO.Range("S2:T2")   'IMAGEURL>image & images
    .Range("C2:C" & LR).Copy wsCPO.Range("U2")      'CATALOGNAME>category
    .Range("F2:F" & LR).Copy wsCPO.Range("X2")      'KEYWORDS>tags
End With

If Not WasOPEN Then wb1.Close False

End Sub

This macro will:

1. Check to see if Worksheet 1.xls is already open and open it if it is not.
   (Be sure to edit the "path" to that file so the macro can find it when necessary)
2. Clear the current CPO Outlet data
3. Copy the mapped data from Worksheet 1.xls Sheet1 to CPO Outlet
4. Close the Worksheet 1.xls file if it was originally closed

We can reorganize this so that it works with "whatever" the activesheet happens to be.

Option Explicit

Sub ImportNewSKUData()
Dim wb1 As Workbook, wsNEW As Worksheet
Dim LR As Long, WasOPEN As Boolean

If MsgBox("Process data into the activesheet?", _
    vbYesNo, "Confirm") = vbNo Then Exit Sub
Set wsNEW = ActiveSheet

On Error Resume Next
Set wb1 = Workbooks("Worksheet 1.xls")
If Not wb1 Is Nothing Then
    WasOPEN = True
Else
    Set wb1 = Workbooks.Open("C:\MyExcelFiles\Worksheet 1.xls")
End If

wsNEW.UsedRange.Offset(1).ClearContents

With wb1.Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("H2:H" & LR).Copy wsNEW.Range("A2")      'SKU>SKU
    .Range("E2:E" & LR).Copy wsNEW.Range("B2")      'NAME>post_title
    .Range("G2:G" & LR).Copy wsNEW.Range("C2")      'DESCRIPTION>post_content
    .Range("E2:E" & LR).Copy wsNEW.Range("D2")      'NAME>post_excerpt
    .Range("B2:B" & LR).Copy wsNEW.Range("M2")      'PROGRAMURL>url
    .Range("R2:R" & LR).Copy wsNEW.Range("N2")      'BUYURL>link
    .Range("T2:T" & LR).Copy wsNEW.Range("S2:T2")   'IMAGEURL>image & images
    .Range("C2:C" & LR).Copy wsNEW.Range("U2")      'CATALOGNAME>category
    .Range("F2:F" & LR).Copy wsNEW.Range("X2")      'KEYWORDS>tags
End With

If Not WasOPEN Then wb1.Close False

End Sub

If you need we can make Excel prompt you to select the file to open and import from, too, so you have variable control of that without editing the macro.

OK, this version will import the workbook you select (a popup window will let you select the source file) into the currently activesheet.

Option Explicit

Sub ImportNewSKUData()
'Imports a file you select into the activesheet
Dim wb1 As Workbook, wsNEW As Worksheet
Dim LR As Long, fNAME As String

If MsgBox("Process data into the activesheet?", _
    vbYesNo, "Confirm") = vbNo Then Exit Sub
Set wsNEW = ActiveSheet

fNAME = Application.GetOpenFilename("Microsoft Office Excel Files (.xls),.xls")
If fNAME = "False" Then Exit Sub

On Error Resume Next
Set wb1 = Workbooks.Open(fNAME)

wsNEW.UsedRange.Offset(1).ClearContents

With wb1.Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("H2:H" & LR).Copy wsNEW.Range("A2")      'SKU>SKU
    .Range("E2:E" & LR).Copy wsNEW.Range("B2")      'NAME>post_title
    .Range("G2:G" & LR).Copy wsNEW.Range("C2")      'DESCRIPTION>post_content
    .Range("E2:E" & LR).Copy wsNEW.Range("D2")      'NAME>post_excerpt
    .Range("B2:B" & LR).Copy wsNEW.Range("M2")      'PROGRAMURL>url
    .Range("R2:R" & LR).Copy wsNEW.Range("N2")      'BUYURL>link
    .Range("T2:T" & LR).Copy wsNEW.Range("S2:T2")   'IMAGEURL>image & images
    .Range("C2:C" & LR).Copy wsNEW.Range("U2")      'CATALOGNAME>category
    .Range("F2:F" & LR).Copy wsNEW.Range("X2")      'KEYWORDS>tags
End With

wb1.Close False

End Sub