I'm still very new to writing VBA code and appreciate the forum and its
responders that assist in educating newbies like myself. Thank you in advance.

I have data in 2 worksheets ("a" & "b") that I need to copy and paste into a
new row in worksheet "c". The data from worksheet "a" needs to populate
columns (a:af) and the data from worksheet "b" needs to populate columns
(ag:as) in the same row. I've gotten the data to copy properly from worksheet
"a" to worksheet "c" but the data from worksheet "b" is overwriting the data
in worksheet "c" in columns (a:m). I haven't figured out how to find the last
column in the row in order for worksheet "b" data to paste in the correct
location.

--
dhunter43
The Performance Advantage, LLC

Coupe different ways. Here's one:
Range("IV1").End(xlToLeft).Select
The above code will find the select the last used cell in row 1. You
could then use Offset(0,1) to move to the next column to the right.

However, I'd like to see your code to see exactly how you are handling
this. The Copy command in VBA has an optional argument of
Destination. It can be usd to specify where to place the copied data.
Sub copySample()
Range("A1:E1").Copy Sheets("Sheet3").Range("A1")
End Sub

The above code can be tweaked to reference a particular cell for the
destination. In your case, the first available cell on a particular
row.

dhunter43 wrote:
I'm still very new to writing VBA code and appreciate the forum and its
responders that assist in educating newbies like myself. Thank you in advance.

I have data in 2 worksheets ("a" & "b") that I need to copy and paste into a
new row in worksheet "c". The data from worksheet "a" needs to populate
columns (a:af) and the data from worksheet "b" needs to populate columns
(ag:as) in the same row. I've gotten the data to copy properly from worksheet
"a" to worksheet "c" but the data from worksheet "b" is overwriting the data
in worksheet "c" in columns (a:m). I haven't figured out how to find the last
column in the row in order for worksheet "b" data to paste in the correct
location.

--
dhunter43
The Performance Advantage, LLC

I'm a bit embarrassed by all the lines of code but I'm using the record macro
and edit method to augment my self paced tutorial on VBA coding.

I inserted lines 68 & 69 thinking I could command to count columns then paste.


Sheets("InputForm").Activate
Range("B14:b25").Select
Selection.Sort Key1:=Range("B14"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Sheets("Formulas").Activate
Sheets("Formulas").Range("A2:Ag2").Select
Selection.Copy
Sheets("OperationalRates").Activate
LastRow = Sheets("OperationalRates"). _
Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("OperationalRates").Rows(LastRow & ":" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("InputForm").Activate
Range("A1:E25").Select
Selection.Copy
Windows("ProductionOrders.xls").Activate
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Columns("A:C").Select
Selection.ColumnWidth = 15#
ActiveSheet.Range("C14:C25").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
ActiveSheet.Range("C6:C7").Select
Selection.NumberFormat = "m/d/yy;@"

ActiveSheet.Range("B14:B25").Select
Selection.Copy
Windows("EGISched-ddh.xls").Activate
Sheets("OperationalRates").Select

ActiveWindow.SmallScroll ToRight:=36
Cell.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Windows("ProductionOrders.xls").Activate
ActiveWorkbook.Save

Workbooks("EGISched-ddh").Activate
Sheets("InputForm").Range("c2:c12").ClearContents
Sheets("InputForm").Range("b14:b25").ClearContents
Sheets("InputForm").Activate
Sheets("InputForm").Range("c2:c2").Select

End With

End Sub

--
dhunter43
The Performance Advantage, LLC


"dhunter43" wrote:

I'm still very new to writing VBA code and appreciate the forum and its
responders that assist in educating newbies like myself. Thank you in advance.

I have data in 2 worksheets ("a" & "b") that I need to copy and paste into a
new row in worksheet "c". The data from worksheet "a" needs to populate
columns (a:af) and the data from worksheet "b" needs to populate columns
(ag:as) in the same row. I've gotten the data to copy properly from worksheet
"a" to worksheet "c" but the data from worksheet "b" is overwriting the data
in worksheet "c" in columns (a:m). I haven't figured out how to find the last
column in the row in order for worksheet "b" data to paste in the correct
location.

--
dhunter43
The Performance Advantage, LLC

This is totally untested, but I cleaned up the code quite a bit.
Selections are a no-no if at all possible. Also, I wasn't sure which
copy you wanted pasted where. There was one line in your code where
you were doing a scroll right. Not sure what was happening there. If
you can tell me, for each copy, where you want it pasted, I can tailor
the code a little more.
Sub dhunter()
Dim shInputForm As Worksheet
Dim shFormulas As Worksheet
Dim shOperationalRates As Worksheet
Set shInputForm = Workbooks("EGISched-ddh") _
.Sheets("InputForm")
Set shFormulas = Workbooks("EGISched-ddh") _
.Sheets("Formulas")
Set shOperationalRates = Workbooks("EGISched-ddh") _
.Sheets("OperationalRates")
shInputForm.Range("B14:b25").Sort _
Key1:=shInputForm.Range("B14"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
shFormulas.Range("A2:Ag2").Copy
shOperationalRates.Range(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues
shInputForm.Range("A1:E25").Copy
With Workbooks("ProductionOrders.xls").Sheets.Add
.Range("A1").PasteSpecial Paste:=xlPasteValues
.Columns("A:C").ColumnWidth = 15#
With .Range("C14:C25")
.NumberFormat = "0.00"
.HorizontalAlignment = xlCenter
End With
.Range("C6:C7").NumberFormat = "m/d/yy;@"
.Range("B14:B25").Copy Workbooks("EGISched-ddh.xls") _
.Sheets("OperationalRates").Range("A1")
End With
Workbooks("ProductionOrders.xls").Save
shInputForm.Range("c2:c12,b14:b25").ClearContents
Set shInputForm = Nothing
Set shFormulas = Nothing
Set shOperationalRates = Nothing
End Sub
dhunter43 wrote:
I'm a bit embarrassed by all the lines of code but I'm using the record macro
and edit method to augment my self paced tutorial on VBA coding.

I inserted lines 68 & 69 thinking I could command to count columns then paste.


Sheets("InputForm").Activate
Range("B14:b25").Select
Selection.Sort Key1:=Range("B14"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Sheets("Formulas").Activate
Sheets("Formulas").Range("A2:Ag2").Select
Selection.Copy
Sheets("OperationalRates").Activate
LastRow = Sheets("OperationalRates"). _
Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("OperationalRates").Rows(LastRow & ":" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("InputForm").Activate
Range("A1:E25").Select
Selection.Copy
Windows("ProductionOrders.xls").Activate
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Columns("A:C").Select
Selection.ColumnWidth = 15#
ActiveSheet.Range("C14:C25").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
ActiveSheet.Range("C6:C7").Select
Selection.NumberFormat = "m/d/yy;@"

ActiveSheet.Range("B14:B25").Select
Selection.Copy
Windows("EGISched-ddh.xls").Activate
Sheets("OperationalRates").Select

ActiveWindow.SmallScroll ToRight:=36
Cell.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Windows("ProductionOrders.xls").Activate
ActiveWorkbook.Save

Workbooks("EGISched-ddh").Activate
Sheets("InputForm").Range("c2:c12").ClearContents
Sheets("InputForm").Range("b14:b25").ClearContents
Sheets("InputForm").Activate
Sheets("InputForm").Range("c2:c2").Select

End With

End Sub

--
dhunter43
The Performance Advantage, LLC


"dhunter43" wrote:

I'm still very new to writing VBA code and appreciate the forum and its
responders that assist in educating newbies like myself. Thank you in advance.

I have data in 2 worksheets ("a" & "b") that I need to copy and paste into a
new row in worksheet "c". The data from worksheet "a" needs to populate
columns (a:af) and the data from worksheet "b" needs to populate columns
(ag:as) in the same row. I've gotten the data to copy properly from worksheet
"a" to worksheet "c" but the data from worksheet "b" is overwriting the data
in worksheet "c" in columns (a:m). I haven't figured out how to find the last
column in the row in order for worksheet "b" data to paste in the correct
location.

--
dhunter43
The Performance Advantage, LLC

Your scripting is far more elegant and I've learned quite a bit from
comparing the 2 versions.

Issue1 needs to paste the data into the first blank row in shOperationalRates.
Issue1 (line22 thru 24)
shOperationalRates.Range(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues

Issue2 needs to paste the data at the end of the row identified in Issue1.
The column would be ("AH").
Issue2 (line33 - 35)
End With
..Range("C6:C7").NumberFormat = "m/d/yy;@"
..Range("B14:B25").Copy Workbooks("EGISched-ddh2.xls") _
..Sheets("OperationalRates").Range("A1")

Thank you,
--
dhunter43
The Performance Advantage, LLC


"dhunter43" wrote:

I'm still very new to writing VBA code and appreciate the forum and its
responders that assist in educating newbies like myself. Thank you in advance.

I have data in 2 worksheets ("a" & "b") that I need to copy and paste into a
new row in worksheet "c". The data from worksheet "a" needs to populate
columns (a:af) and the data from worksheet "b" needs to populate columns
(ag:as) in the same row. I've gotten the data to copy properly from worksheet
"a" to worksheet "c" but the data from worksheet "b" is overwriting the data
in worksheet "c" in columns (a:m). I haven't figured out how to find the last
column in the row in order for worksheet "b" data to paste in the correct
location.

--
dhunter43
The Performance Advantage, LLC