1

Copy data from Sheet3 and Sheet4, the data is in column A, and the number of cells will vary every time. I want to copy the data from column a of sheet3 and column a of sheet4 and paste it to sheet5 cell A3 and then everytime in the next empty cell in sheet5. it is pasting data of only of sheet3 and not the sheet 4.

below is the macro I tried- enter image description here I tried copyng macros from various threads but unable to do it. Please help as I am new to macros. Thanks in advance.

    Sub Macro10()
'
' Macro10 Macro
'

'
    Sheets("Sheet3").Select
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet5").Select
    Range("A3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Sheets("Sheet4").Select
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet5").Select
    Worksheets(“Sheet5”).Activate
    erow = Sheet5.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets(“Sheet5”).Rows(erow)
    Range("A1").Select
    Application.CutCopyMode = False
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Rohit Khandelwal
  • 23
  • 1
  • 4
  • Please copy the code as text to your question (post) which you can modify by clicking on the edit button below you post. You can format it as code with {}. – VBasic2008 Mar 03 '19 at 15:37
  • It is showing your code is incorrect. Do not know how to correct it. – Rohit Khandelwal Mar 03 '19 at 15:44
  • Copy paste the code and post a screenshot of the data – Ricardo Diaz Mar 03 '19 at 15:52
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Also note that you use these fancy quotes in `“Sheet5”` but VBA only accepts the simple quotes `"Sheet5"` you must change them. – Pᴇʜ Mar 06 '19 at 08:42

1 Answers1

0

From Worksheets to Master Worksheet

Constants Version

Sub CopyToMaster()

    Const cSource1 As String = "Sheet3"  ' 1st Source Worksheet Name
    Const cSource2 As String = "Sheet4"  ' 2st Source Worksheet Name
    Const cTarget As String = "Sheet5"   ' Target Worksheet
    Const cRange As String = "A3"        ' First Cell Address of Copy Range
    Const cCol As Long = 1               ' Target Column Number

    Dim ws1 As Worksheet  ' 1st Source Worksheet
    Dim ws2 As Worksheet  ' 2nd Source Worksheet
    Dim wsT As Worksheet  ' Target Worksheet

    Dim Rng As Range      ' Copy Range (in 1st and 2nd Source Worksheet)
    Dim FNR As Long       ' First Not Used Row

    ' Create references to all Worksheets
    With ThisWorkbook
        Set ws1 = .Worksheets(cSource1)
        Set ws2 = .Worksheets(cSource2)
        Set wsT = .Worksheets(cTarget)
    End With

    ' Calculate First Not Used Row in Target Column.
    FNR = wsT.Cells(wsT.Rows.Count, cCol).End(xlUp).Offset(1).Row
    ' Calculate Copy Range in 1st Source Worksheet
    Set Rng = ws1.Range(ws1.Range(cRange), ws1.Range(cRange).End(xlDown))
    ' Copy Copy Range in 1st Source Worksheet to Target Column.
    Rng.Copy wsT.Cells(FNR, cCol)
    ' Calculate First Not Used Row in Target Column.
    FNR = wsT.Cells(wsT.Rows.Count, cCol).End(xlUp).Offset(1).Row
    ' Calculate Copy Range in 2nd Source Worksheet
    Set Rng = ws2.Range(ws2.Range(cRange), ws2.Range(cRange).End(xlDown))
    ' Copy Copy Range in 2nd Source Worksheet to Target Column.
    Rng.Copy wsT.Cells(FNR, cCol)

End Sub

'I Hate Constants' Version

Sub IHateConstants()

    Dim ws1 As Worksheet  ' 1st Source Worksheet
    Dim ws2 As Worksheet  ' 2nd Source Worksheet
    Dim wsT As Worksheet  ' Target Worksheet

    Dim Rng As Range      ' Copy Range (in 1st and 2nd Source Worksheet)
    Dim FNR As Long       ' First Not Used Row

    ' Create references to all Worksheets
    With ThisWorkbook
        Set ws1 = .Worksheets("Sheet3")
        Set ws2 = .Worksheets("Sheet4")
        Set wsT = .Worksheets("Sheet5")
    End With

    ' Calculate First Not Used Row in first column of Target Worksheet.
    FNR = wsT.Cells(wsT.Rows.Count, 1).End(xlUp).Offset(1).Row
    ' Calculate Copy Range in 1st Source Worksheet
    Set Rng = ws1.Range(ws1.Range("A3"), ws1.Range("A3").End(xlDown))
    ' Copy Copy Range in 1st Source Worksheet to first column of Target
    ' Worksheet.
    Rng.Copy wsT.Cells(FNR, 1)
    ' Calculate First Not Used Row in first column of Target Worksheet.
    FNR = wsT.Cells(wsT.Rows.Count, 1).End(xlUp).Offset(1).Row
    ' Calculate Copy Range in 2nd Source Worksheet
    Set Rng = ws2.Range(ws2.Range("A3"), ws2.Range("A3").End(xlDown))
    ' Copy Copy Range in 2nd Source Worksheet to first column of Target
    ' Worksheet.
    Rng.Copy wsT.Cells(FNR, 1)

End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28