Hi S,
Thanks for your reply and comments.
Having thought more about the process, work flow and your comments, I would like to accomplish the following:
- Data is imported on a weekly basis into the data work tab (see attached fro example of setup)
- Different data is populated into sheets 1 and 3
- As the data is populated simultaneously, I would like a sinlge button to paste the row from sheets 1 and 3 into 2 and 4.
The data will be seperated into gross and net, making the numbers different in sheets 2 and 4
- I have had a go myself in creating a macro based on your initial one (see code below), so at present I have to run a macro to copy and paste the data from sheet 1 into sheet 3 and run another macro to copy and paste data from sheet 3 into sheet 4.
- Can one macro do both copy and paste data from sheet 1 and 3 into 2 and 4?
Regards
N
Apologies if I have not wrapped this correctly. It is the first time I have done so.
- Code: Select all
Option Explicit
Enum Tws ' Worksheet navigation
TwsStart = 4 ' Sh3 - column 4 = D
TwsEnd = 13 ' Sh3 - column 13 = M
TwsTarget = 3 ' Sh4 - column 3 = C
End Enum
Sub CopyData()
Const Sh3 As String = "Net Revenue Split"
Const Sh4 As String = "Net Revenue"
Dim Ws3 As Worksheet
Dim Ws4 As Worksheet
Dim Rng As Range
Dim Rs As Long ' Source row
Dim Rt As Long ' Target row
Set Ws3 = Sheets(Sh3)
Set Ws4 = Sheets(Sh4)
Rs = 7
Rt = LastRow(TwsTarget, Ws4)
With Ws3
Set Rng = Range(.Cells(Rs, TwsStart), .Cells(Rs, TwsEnd))
End With
Rng.Copy
Ws4.Cells(Rt + 1, TwsTarget).PasteSpecial Paste:=xlValues
End Sub
Private Function LastRow(Optional ByVal Col As Variant, _
Optional Ws As Worksheet) As Long
' 0059 V 3.2 Apr 2, 2012
' Return the number of the last non-blank row in column Col.
' Specify the column as string or number
' If no column is specified,
' return the last row from column A.
' If no worksheet is specified
' return the result from the currently active sheet.
Dim R As Long
If Ws Is Nothing Then Set Ws = ActiveSheet
If VarType(Col) = vbError Then Col = 1
With Ws
R = .Cells(.Rows.Count, Col).End(xlUp).Row
With .Cells(R, Col)
' in a blank column the last used row is 0 (= none)
If R = 1 And .Value = vbNullString Then R = 0
' include all rows of a merged range
LastRow = R + .MergeArea.Rows.Count - 1
End With
End With
End Function
You do not have the required permissions to view the files attached to this post.