VBA in Excel to tranfer data from one sheet to many

[atifmehmood]

Hi
I am new to VBA for excel and I am trying to transfer a data from master sheet 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 amount and description from master sheet todestination sheet it works for column A but when I want to change code for destination as column B 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.

""""""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""""

 

[rpaulson]

look a this thread.

Create a worksheet for each uiniuqe item in my Data range?

I see a lot of request for some VBA code to create individual sheets out of my data. (Like: "I want a sheet for every part number in my list") I put together some code to automate the process for you. Enjoy! Sub Create_Worksheets() 'This macro will seperate the data in your range into...

www.mrexcel.com

 

[Fluff]

Cross posted Transfer Data from one sheet to many with VBA

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.