Copying 2nd Worksheet Below Existing Data

[YellowLabPro]

I have a worksheet that I require to be populated from two different worksheets which the two source worksheets are of identical structure.
The source workbook is MasterImport.xls and the first source worksheet to be copied is PCCombined_FF and the second worksheet to follow is PCCombined_VB to the target workbook of Upload_Fileworksheet and the worksheet is EC Products.

Along w/ that, I need a control value to be placed in column A of the target workbook from each sheet to indicate where each record's origin was derived, in the case of PCCombined_FF, the term "Fairfax" will be used starting in A4 to the last record from this worksheet and for PCCombined_VB, Va Beach will be used immediately following the last record to its last record commencing immediately after the last record from PCCombined_FF.

This code is as follows:
[vba]
Option Explicit
Sub CopyColumn()
Dim LastRow As Long
Dim RowNum As Integer
Dim FairfaxRow As Integer
Dim Va_BeachRow As Integer

Application.ScreenUpdating = True
RowNum = Range("B65536").End(xlUp).Row
'Range("A4:A" & RowNum).Value = "Fairfax"
Range("Z4:Z" & RowNum).Value = "Active"
Range("AA4:AA" & RowNum).Value = "EOREOR"


'FairfaxRow = Range("A65536").End(xlUp).Row
'Range("A4:A" & FairfaxRow).Value = "Fairfax"

With Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
LastRow = .Range("B65536").End(xlUp).Row + 1
.Range("B4:B" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("B4") 'Item Record#
.Range("D4" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("C4") 'Item Description
'.Range("J4:J" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("D4") '.PasteSpecial(xlPasteValues)
.Range("J4:J" & LastRow).Copy
ActiveSheet.Range("D4").PasteSpecial Paste:=xlValues
.Range("H4:H" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("H4") 'Price
.Range("Q4:Q" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'PARENT COLOR
.Range("T4:T" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'CHILD COLOR
.Range("V4:V" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("K4") 'SIZE
'.Range("AE4:AE" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("O4") '.PasteSpecial(xlPasteValues) 'S/H Weight
.Range("AE4:AE" & LastRow).Copy
ActiveSheet.Range("O4").PasteSpecial Paste:=xlValues
'.Range("AD4:AD" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("P4") 'Actual Weight
.Range("AD4:AD" & LastRow).Copy
ActiveSheet.Range("P4").PasteSpecial Paste:=xlValues
.Range("E4:E" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("S4") 'Quantity
.Range("Y4:Y" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("T4") 'Manufacturer
'.Range("AA4:AA" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("U4") 'Deptartment
.Range("AA4:AA" & LastRow).Copy
ActiveSheet.Range("U4").PasteSpecial Paste:=xlValues
'.Range("AB4:AB" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("V4") 'Category
.Range("AB4:AB" & LastRow).Copy
ActiveSheet.Range("V4").PasteSpecial Paste:=xlValues
'.Range("AC4:AC" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("W4") 'SubCategory
.Range("AC4:AC" & LastRow).Copy
ActiveSheet.Range("W4").PasteSpecial Paste:=xlValues

End With

'------------------------------------------VB------------------------------------------------------------------------
'With Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_VB")
'
''Va_BeachRow = Range("A65536").End(xlUp).Row
''Range("A" & Va_BeachRow, "A" & Va_BeachRow).Value = "Va Beach"
'
' LastRow = .Range("B65536").End(xlUp).Row + 1
' .Range("B4:B" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("B4") 'Item Record#
' .Range("D4" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("C4") 'Item Description
' '.Range("J4:J" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("D4") '.PasteSpecial(xlPasteValues)
' .Range("J4:J" & LastRow).Copy
' ActiveSheet.Range("D4").PasteSpecial Paste:=xlValues
' .Range("H4:H" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("H4") 'Price
' .Range("Q4:Q" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'PARENT COLOR
' .Range("T4:T" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'CHILD COLOR
' .Range("V4:V" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("K4") 'SIZE
' '.Range("AE4:AE" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("O4") '.PasteSpecial(xlPasteValues) 'S/H Weight
' .Range("AE4:AE" & LastRow).Copy
' ActiveSheet.Range("O4").PasteSpecial Paste:=xlValues
' '.Range("AD4:AD" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("P4") 'Actual Weight
' .Range("AD4:AD" & LastRow).Copy
' ActiveSheet.Range("P4").PasteSpecial Paste:=xlValues
' .Range("E4:E" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("S4") 'Quantity
' .Range("Y4:Y" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("T4") 'Manufacturer
' '.Range("AA4:AA" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("U4") 'Deptartment
' .Range("AA4:AA" & LastRow).Copy
' ActiveSheet.Range("U4").PasteSpecial Paste:=xlValues
' '.Range("AB4:AB" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("V4") 'Category
' .Range("AB4:AB" & LastRow).Copy
' ActiveSheet.Range("V4").PasteSpecial Paste:=xlValues
' '.Range("AC4:AC" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("W4") 'SubCategory
' .Range("AC4:AC" & LastRow).Copy
' ActiveSheet.Range("W4").PasteSpecial Paste:=xlValues
'End With
Cells.Columns.AutoFit

Range("A4:BB" & RowNum).Interior.ColorIndex = xlNone
Rows("4:" & RowNum).Interior.ColorIndex = xlNone
[A4].Activate
End Sub
[/vba]


Previously another boardmember provided the code that performed almost the identical procedure, but slightly different than my current scenario. The following code works great. However, it is beyond my scope and I am unable to adapt it to my current project.
The code for this procedure immediately follows:
[vba]
Sub A_DataEdited()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws As Worksheet
Dim arrWS
Dim Cell
Application.ScreenUpdating = True

arrWS = Array("TGFF", "Fairfax", "TGVB", "Va Beach")
LastRowDst = 2

For i = LBound(arrWS) To UBound(arrWS) Step 2

Set ws = Worksheets(arrWS(i))


LastRowSrc = ws.Range("A65536").End(xlUp).Row

ws.Range("A4:A" & LastRowSrc).Copy Sheets("DataEdited").Range("B" & LastRowDst)

ws.Range("D4" & LastRowSrc).Copy Sheets("DataEdited").Range("C" & LastRowDst & ":E" & LastRowDst)

Sheets("DataEdited").Range("A" & LastRowDst).Resize(LastRowSrc - 3) = arrWS(i + 1)
LastRowDst = LastRowDst + LastRowSrc - 3
Next i

LastRow = Range("F" & Rows.Count).End(xlUp).Row
For Each Cell In Range("G2:I" & LastRow)
Cell.Value = Application.WorksheetFunction.Trim(Cell.Value)
Next

End Sub
[/vba]

Much thanks,

YLP

[malik641]

Hey YLP

Is there a way you could post 2 dummy workbooks (copy from & target workbook) with what you have and the outcome?

Thanks

[YellowLabPro]

malik,
I attempted to post my sheets using Colo's HTML Utility, but it did not post correct. Is there a way to post examples of the sheets?

thanks,

YLP

[ALe]

Reply with "Go advanced" and attach the files

[YellowLabPro]

Source File

[YellowLabPro]

Target File

[YellowLabPro]

Final outcome example

[lucas]

Hi ylp,
that second code looks like it has MD's tracks on it.
If your first section of code does what you want for the first sheet why not add another with statement while the wb is open?

[YellowLabPro]

Hi Lucas,
I do have another With statement and am running it while the WB is open. The problem is two-fold. 1- For the code to know where to put the second set of data, where to start w/out having to hardcode the row coordinate and 2. to offer the index value for each file.

regards,

ylp