Transfer Data from one sheet to many with VBA

Hi
I am new to VBA for excel and I am trying to transfer data from master sheet(Data Entry) which contains expenses for different dates and for every date I have a separate sheet where I want to transfer expenses from master sheet named "Data Entry". the problem with my code is that when I transfer Column B & C amount and description from master sheet to destination sheet it transfer data to column A & B but when I want to change code for destination as column B and C in destination sheet it run for only one entry and then left the all others, I have lot of destination sheets and lot of entries for every sheet please help me in solving keeping my format for sheets in consideration. I am attaching the VBA code as well as Snaps of my sheet.



My VBA code is as follow



Sub copyPasteData()

Dim strSourceSheet As String
Dim strDestinationSheet As String
Dim lastRow As Long

strSourceSheet = "Data entry"

Sheets(strSourceSheet).Visible = True
Sheets(strSourceSheet).Select

Range("B2").Select
Do While ActiveCell.Value <> ""
strDestinationSheet = ActiveCell.Value
ActiveCell.Offset(0, 1).Resize(1, 2).Select
Selection.Copy
Sheets(strDestinationSheet).Visible = True
Sheets(strDestinationSheet).Select
lastRow = LastRowInOneColumn("A")
Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteAll
Application.CutCopyMode = False
Sheets(strSourceSheet).Select
ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Public Function LastRowInOneColumn(col)
'Find the last used row in a Column: column A in this example

Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
LastRowInOneColumn = lastRow
End Function

This is the Workbook I am working with

Hello Atif,

The attached file doesn't look like the pictures posted. Can you make sure you attach the correct file

Sorry Dear Nankw83,
you are correct I have attached the wrong file this is the actual files which is attached herewith
thanks in anticipation

This is a revised code, try it out

Please Login or Register  to view this content.

Dear nankw83
Thank You Very Much That's what I was looking for
many thanks

Glad to help … Please mark this thread as solved by selecting Thread Tools from the menu link above & add to the reputation(s) of those who helped