Dear S,
Thanks for the quick reply.
You mention replace my code with the below.
Do I also have to stop using the initial code you emailed me? i.e. delete bot codes and replace with the below?
Regards
N
This forum has been moved to TeachExcel.com
Ask all future questions in the New Excel Forum.
ExcelKey
= Range(.Cells(Row, Column), .Cells(Row, Column))
Set Rng = Range(.Cells(NwsStart, Cs), .Cells(NwsEnd, Cs))
' or
Set Rng = Range(.Cells(NwsStart, NwsSource), .Cells(NwsEnd, NwsSource))
Ws2.Cells(Rt + 1, NwsTarget).PasteSpecial Paste:=xlValues, Transpose:=True
Option Explicit
Enum Nws ' Worksheet Source (Sh1)
NwsStart = 5 ' Sh1 - row
NwsEnd = 9 ' Sh1 - row
NwsSource = 4 ' Sh1 - 4 = Column D
End Enum
Enum Nwt ' Worksheet Target (Sh2)
NwtFirstRow = 4 ' Sh2 - target row
NwtFirstCol = 4 ' Sh1 - 4 = Column D
End Enum
Sub TransferData()
Const Sh1 As String = "Sheet 1"
Const Sh2 As String = "Sheet 2"
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Rng As Range
Dim Ct As Long ' Target column
Set Ws1 = Sheets(Sh1)
Set Ws2 = Sheets(Sh2)
Ct = LastColumn(NwtFirstRow, Ws2) + 1
If Ct < NwtFirstCol Then Ct = NwtFirstCol
With Ws1
Set Rng = Range(.Cells(NwsStart, NwsSource), .Cells(NwsEnd, NwsSource))
End With
Rng.Copy
Ws2.Cells(NwtFirstRow, Ct).PasteSpecial Paste:=xlValues
End Sub
Private Function LastColumn(Optional ByVal Rw As Long, _
Optional Ws As Worksheet) As Long
' 0056 V 2.1 Apr 2, 2012
' Return the number of the last non-blank column in row Rw.
' If no row is specified,
' return the last column from row 1.
' If no worksheet is specified,
' return the result from the currently active sheet.
Dim C As Long
If Ws Is Nothing Then Set Ws = ActiveSheet
Rw = IIf(Rw, Rw, 1)
With Ws
C = .Cells(Rw, .Columns.Count).End(xlToLeft).Column
With .Cells(Rw, C)
' in a blank row the last used column is 0 (= none)
If C = 1 And .Value = vbNullString Then C = 0
' include all columns of a merged range
LastColumn = C + .MergeArea.Columns.Count - 1
End With
End With
End Function
Return to Macros and VBA Questions
Users browsing this forum: No registered users and 47 guests