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
Improve this question
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 {}. Commented Mar 3, 2019 at 15:37
  • It is showing your code is incorrect. Do not know how to correct it. Commented Mar 3, 2019 at 15:44
  • Copy paste the code and post a screenshot of the data Commented Mar 3, 2019 at 15:52
  • You might benefit from reading 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. Commented Mar 6, 2019 at 8:42

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@Rohit Khandelwal: I'm very sorry. The code was totally wrong. This one is tested and is working.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.