This is a guest post by Vijay, our in-house VBA Expert.
Suresh sent an email with interesting problem.
There is one data entry sheet where all the data needs will be entered, however once done we want the data to be stored separately in multiple sheets designated by the Employee code.
In this article we will learn how to use VBA to help in resolving the problem Suresh was facing at work.
We will do this using VBA and the below procedure illustrates how this was done.
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("C2").Select
Do While ActiveCell.Value <> ""
strDestinationSheet = ActiveCell.Value
ActiveCell.Offset(0, -2).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
Selection.Copy
Sheets(strDestinationSheet).Visible = True
Sheets(strDestinationSheet).Select
lastRow = LastRowInOneColumn("A")
Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets(strSourceSheet).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Let’s understand the code
We start by defining 2 variables which will hold the Source and destinations sheet names, source will be one sheet only however there will be multiple destinations sheets.
A simple Do While loop is utilized to complete this data transfer from one sheet to the others.
However the most interesting line in the code is :
ActiveCell.Offset(0, -2).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
Since we start our work on Column C in the loop, we need to ensure the data from Column A and B is also selected to be copied and pasted on the relevant sheets, this is accomplished using the above line.
Once we have selected the entire data set on the current row, we copy the same and select the relevant sheet and then paste the data as values on the same.
At the end of the code once we switch back to the Source sheet, we need to ensure that we select Column C again. The reason for that is, when we select the entire data set we activate the first column and we need to come back to column C for our loop and the rest of the code to work.
Download Excel File
Click here to download the file & save it on your system and use it to understand this technique.
More on VBA & Macros
If you want to learn more about using VBA to automate reporting & email tasks, read these:
- Automatically Generate Report Variations using Excel
- What is VBA & Macros? Introduction
- Excel VBA Example Macros
- VBA tutorial videos
Join our VBA Classes
If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.
74 Responses to “VBA Move data from one sheet to multiple sheets”
Hi there,
An excellent tip, as always.
Interestingly, this is one of the features of ASAP Utilities for Excel.
A google will soon find it.
I wonder if you have ever tried this out? It's an amazing suite of Excel Time-Savers.
I assure you that I am in no way affiliated with the authors - just wanted you to know that it was out there...
Regards and thanks for a great website and emails!
Gary Skelton
Dear Chandoo !
after entry we change in data entry sheet its effect not representative in similar sheet,
In this worksheet
when we entry in cell C2 employee code 1 its data transfer in sheet 1
entry in cell C3 by employee code 2 then its data transfer in sheet 2
Problem is there after enter value in C3 then sheet 1's row should not increase ,
Dear sir,
i am karthikeyan,r.karthikeyan78@yahoo.com i request u to please clarify my doubt in excel sheet move data from one to many sheet.
if i put one entry in data entry sheet one name and various dates also various names in same date it saves and and goes to sheet1 if i run macro
several times per day it shows multiple duplicate entries. It automatically
creates like new entry when i running macro.
I need solution for that please send your valuable suggestions.
Here i attach my problem
s.r no
Date
employee code
name
Debit
Credit
Balance
1
5/10/2012
muthu
A
100
50
50
1
5/10/2012
muthu
A
100
50
50
1
5/10/2012
muthu
A
100
50
50
Dont download this guys! Like a mug i did it, but its not free, once its installed on your Excel ribbon they want an upgrade fee!
I now cant get rid of it so everytime i open an eExcel file it goes looking for this darn programme which ive deleted!
anyone know how to stop Excel looking for this programme?
Thanks
Hi all,
@Andrew - My apologies - I use this at a non-profit organisation, therefore I can use the free version - therefore I never had this problem. Have you tried a) uninstalling it or b) unchecking the Add-in in options?
@Suresh - Not quite following you, but the VBA is designed to copy your data and pasting it to another sheet, without any links to the original data. This is a one-off process for splitting your data into multiple sheets by change of a value, consolidating data onto extra sheets and enabling you to easily report (or pring) a distinct set of values.
Regards
Gary
Hello Andrew,
I'm sorry to read this. Next time, please contact us so we can help.
Usually uninstalling ASAP Utilities will completely remove it.
Do you get a message similar to "C:\Program Files\ASAP Utilities\ASAP Utilities.xla" could not be found." when you start Excel?
You can get rid of thIS message by following the following steps:
http://www.asap-utilities.com/asap-utilities-uninstall-remove.php#temporary_disable
More information on this error:
http://www.asap-utilities.com/faq-questions-answers-detail.php?m=126
I'm also sorry to read you feel mislead. We tried our best on the download page to show you when it is free:
http://www.asap-utilities.com/download-asap-utilities.php
The ASAP Utilities 'Home and student' edition is free and is (only) for non commercial use, such as at home, for students or for non-profit organizations such as Amnesty International. Commercial organizations however should buy a license. (They can try it for 90 days). The reason behind this is that if your organization makes a profit ASAP Utilities will help you reduce your costs and we ask to buy a license in return. The time saved with ASAP Utilities will recover the license costs within a few weeks.
By offering a free edition of ASAP Utilities we want to contribute to society.
Regarding this excellent article from Chandoo, below is the tool that Gary referred to:
ASAP Utilities » Sheets » Split the selected range into multiple worksheets...
http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=267&utilities=20&lang=en_us
If you need additional help then please let me know,
Kind regards,
Bastien
I realy like this macro. I was just wondering how do you get the formulas to auto copy when information is added.
Thanks,
NN
@Andrew... As Bastien pointed out, there must be something wrong with the way you have un-installed it. I have heard many people say nice things about ASAP utilities.
PS: I have no association with them. Just want to make sure you & other readers get correct information.
Andrew,
I didn' try that out ( I don't use any of add-ins)
but I'm sure you can easily switch it off from Add-Ins menu ( Excel options)
Hi,
I'm sorry, I'm normally a fan of this blog but this time:
- There really is no need to select, it is slow and cumbersome, it is much better practice to use:
SourceRangeObject.copy destination:= DestinationRangeObject
Or even better, if you have pre-formatted the areas:
DestinationRange.Value = SourceRange.value
It is even less useful to select and activate the individual sheets.
Lastly, why are you encoraging the seperation of data like this? It is much better to store the data in 1 place & then offer a report by Employee code, other wise future consolidation can be an issue if such reports aer required.
Thanks,
J
Largely I'll have to agree with J here.
Regarding separation of data, I'm not sure I follow you J. The data is not separated, only copied. E.g. are the separated sheets used as some form of reports which then could be e-mailed seperately to each employee? In order to talk of best practice it's necessary to know the purpose of the sheet.
Oh, and where does the input come from? Is it necessary to actually enter it manually to a spreadsheet?
I have a workbook I created for a customer some months ago that does the same thing but with the following additions
The user is able to select what I called the key column, in your case the Employee code, selected by name. The macro then determines the column number for the key column. Next, the unique values in the key column are determined and worksheets for each unique name are created and automatically given that name as the worksheet name. Next the data are parsed with each newly created worksheet filled only the records for that name.
Obviously, my VBA code is a little longer than yours, but is a real time saver if you have 100 employee codes, for example. Also the ability to use any heading is convenient.
What you have just described sounds exactly what I am looking for....
I have a massive database for a entire school section, and I need to generate lists of subjects into their own tab, from one master sheet of options....
If you can send the dummy report etc - I would be eternally grateful!!
I am a real novice - only starting looking at this a couple of days ago - so keep it 'simple'...
cheers again
Confusedcelt
Hi Ron,
Sounds very interesting, cany you share your code or workbook.
I've used Advanced Filter in the past to burst my data into seperate sheets
It could still be mre dynamic to make it better
[1] Does the sheet already exist?
....If not, add the sheet, if yes, do we need to append the data or delete
....and add new?
[2]The code assumes...
...[A] First Row 1
...[B] First Col A
...[C] Last Col K
Can we make all of these dynamic instead?
[code]
Sub ar_MoveServiceCharges()
Dim wsData As Worksheet
Dim wsServiceCharge As Worksheet
Dim rngData As Range
Dim rngCrit As Range
Dim rngDest As Range
Dim arrCrit
Dim lngRows As Long
Set wsData = Worksheets("Data")
Set wsServiceCharge = Worksheets("ServiceCharge")
wsServiceCharge.UsedRange.ClearContents
lngRows = wsData.Range("A" & Rows.Count).End(xlUp).Row
Set rngData = wsData.Range("A1:K" & lngRows)
Set rngDest = wsServiceCharge.Range("A1")
arrCrit = Array("Source Name", "Late Payment Invoice")
Set rngCrit = wsData.Range("IV1:IV2")
rngCrit = WorksheetFunction.Transpose(arrCrit)
rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=rngDest, Unique:=False
rngCrit.ClearContents
'Delete Service Charge Items
ar_DeleteServiceCharges
Set wsData = Nothing
Set wsServiceCharge = Nothing
Set rngData = Nothing
Set rngCrit = Nothing
Set rngDest = Nothing
Set arrCrit = Nothing
End Sub
[/code]
I would need to share the workbook since the VBA code is easier to understand with the worksheets it refers to.
I can't send my client's data, but I could create a version with dummy data. The method is completely dynamic with regard to number of columns and rows. The only restriction is the row number that contains the header. With a little more coding that also could be made dynamic. There is a user guide worksheet than allows the user to enter the key column name (column heading). After that press the "GO" button and the process is completed.
What is the method to upload workbooks to this site? I have just started reading it.
@Ron
Chandoo.org does not have an upload facility.
Have a read here: http://chandoo.org/forums/topic/posting-a-sample-workbook
Help me in adding few conditions in the macro,
Like, sheets should be automatically created and named on some condition which would be a better idea
Well, this is nice and handy and just comes at the right time.
Regarding copying of data I find the comments above not appropriate because you don't know the purpose of the sheets. I am dealing with situations like this all the time and although I have ideas how this could be done better I have to accept the fact that some people simply want it this way and there is no need arguing with them. Therefore I see that this code of course has its right of existence, although this may not be the best way to handle the data.
@Christian,
If all we are doing is copying the data & the original is still there, can yuo please explain to me what happens to the copied data when the original is changed or updated, or added to?
If we use the original as a data store and have reports avaiable then the reports are always current. If the reports need to be distributed then we can generate a copy not linked to the data.
In the past I have also extracted data like this (to multiple sheets / workbooks) using VBA.
Someone suggested using AdvancedFilter, which would do the job faster.
I'd also sort the data on the key column first, and then use the advanced filter option.
This makes it even faster while executing, especially when it involves a large number of records.
Also, another easy (but often forgotten) thing is to add a couple of lines of code to suppress screen updates while the code is executing.
This results in much faster execution of the code.
Application.Screenupdating = False at the beginning, and =True at the end of the code, will do the trick.
Finally, if you're using the AdvancedFilter option, then you don't need to use complex cell / row selection code.
Simply use : ActiveCell.CurrentRegion.SpecialCells(xlCellTypeVisible).Select -or- .Copy and paste the data on the other sheet.
@Mawdo81
I am really interested in learning cleaner techniques than I currently employ. Could you provide a short example of the technique to which you refer?
I am teaching myself how to do things like this, and would love pointers on best practices. For example, this is code I wrote for our admins to use. They are frequently asked to slice data into separate workbooks, based on various entities (locations, areas, divisions, etc.). We are a healthcare company, so this assists us with keeping to HIPAA requirements when sending out worklists. Many of our users in the field still have Excel 2003, and the data is never >65,000, so the format used is Excel8.
Oh, and I'm not asking you fix my code; it works well, and I am happy with it - and so are my admins! I'm just curious if I'm on the right path.
The template has 3 worksheets:
Instructions
Data
Criteria
The Instructions sheet includes two user-entered fields: Pathname and Filename.
The Data worksheet is just that - with the criteria being in column A.
The Criteria worksheet is a unique listing of the values from the criteria column.
The code is as follows:
Public Sub SliceAndDice()
On Error GoTo BAD_SLICE
Dim rngCriteria As Range
Dim strPath As String
Dim strName As String
Dim strPathName As String
strPath = Sheets("Instructions").Range("E3").Value
'Make sure something has been entered into the Pathname
If IsEmpty(strPath) Then
MsgBox "A path is required; please enter and rerun macro"
GoTo GOOD_SLICE
End If
'Clean up the named ranges - just in case they've gotten squirley
'(sometimes, copying/pasting/deleting things causes the named range formulas to adjust - we can't have that)
'The macro will error if this name doesn't exist
On Error Resume Next
ActiveWorkbook.Names("Extract").Delete
'Return to normal error checking
On Error GoTo BAD_SLICE
'Create the named ranges for the data worksheet and the criteria worksheet
ActiveWorkbook.Names("Data_Range").RefersTo = "=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))"
ActiveWorkbook.Names("Criteria_Range").RefersTo = "=OFFSET(Criteria!$A$1,0,0,COUNTA(Criteria!$A:$A),1)"
'Turn off the screen updating and dialog boxes - macro runs faster and doesn't freak out users with crazy questions
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'The user is allowed to include a specialized name to go along with the auto naming in the workbook
'Example: If the user typed "March Follow Ups", then the file names would be yyyy-mm-dd March Follow Ups 'Slicing Entity'
strName = Sheets("Instructions").Range("E6").Value
'Loop through the criteria
For Each rngCriteria In Sheets("Criteria").Range("A1").CurrentRegion.Cells
'Filter the data
Sheets("Data").Range("Data_Range").AutoFilter Field:=1, Criteria1:=rngCriteria
'Select the Data worksheet
Sheets("Data").Select
'Just to make sure we're in the right place
Range("A1").Select
'Copy the filtered data
Selection.CurrentRegion.Select
Selection.Copy
'Paste the data into a new workbook
Workbooks.Add
ActiveSheet.Paste
'Autofit the columns
With Cells
.Select
.EntireColumn.AutoFit
End With
'Rename the worksheet
Sheets("Sheet1").Name = rngCriteria
'Freeze the top row
Range("A2").Select
ActiveWindow.FreezePanes = True
'Turn off Cut/Copy Mode (removes the 'marching ants')
Application.CutCopyMode = False
'Create the full Path Name
strPathName = strPath & "\" & Format(Date, "yyyy-mm-dd") & " " & strName & " " & rngCriteria
'Save the workbook in Excel 97/2003 format
ActiveWorkbook.SaveAs Filename:=strPathName, FileFormat:=xlExcel8
'Close the workbook
ActiveWindow.Close
'Remove the filter
Sheets("Data").ShowAllData
Next
MsgBox "Extraction Complete", vbOKOnly, "Party on, Wayne!!!"
GOOD_SLICE:
'Turn screen output back on and allow dialog boxes again
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Sheets("Instructions").Select
Exit Sub
BAD_SLICE:
If Err.Number = 1004 Then
MsgBox "No criteria found. Please correct and reun the macro.", vbCritical + vbOKOnly
Else
MsgBox Err.Number & vbCrLf & Err.Description
End If
Resume GOOD_SLICE
End Sub
Just wondering why not use Faseeh's formula that Chandoo did the forensic on back on March 1, 2012. That way you don't have to use VBA. Can't you just use array formulas on the individual sheets that are something like this:
{=IFERROR(OFFSET($C$2,SMALL(IF((C2:C11)=2,ROW(C2:C11),FALSE),ROW(A1))-2,1),"…")}
And then just copy it down for how many ever rows you need; and change the "=2" to be "=1" or "=3" for the respective sheet.
Anybody have any comments about using this approach instead?
Hi,
Thanks for this macro.
It's exactly what i was looking for.
However, I'm having a bit of an issue when I use it.
When copying from the original sheet to the others, the macro copies from column B and on.. it doesn't copy column A.
Any help would be very appreciated.
Regards
Run Time error = Employee code increased to 4, no extra sheet has been created.
Can someone help me to use this superb file correctly for my needs.
Thanks
@Oliver,
You will need to create a new sheet and rename it to number 4.
~VijaySharma
Thanks, no macro available to do this automatically ?
I will try to update this and post the file soon.
~VijaySharma
Can i create a new sheet and rename it automatically by using your
vba code ? pls help
sir,
we need to create a file (WB) based on cell value and transfer the data based on cell value or for every change in cell value OR to new sheet named after the cell value ( to create a sheet if not existing)
such code would be comprehensive.
t.v.rao
cm, ro, ubi, mehsana
Hi Chandoo,
Am just learning VBA.
Which part of the code that says that if there's a change in employee code, corresponding data shall be copied to such employee (code) sheet?
Thank you.
I have one excel file wherein i have alphanumeric data in cells A1:A85 & B1:B85. (In Sheet 1)
I have another 85 sheets in the file. I want to pull data of A1 into D20 & data of B1 into E20 (In Sheet 2)
I want to do these in all the 85 sheets in sequential order. i.e. Sheet 2 should pull data of A1 & B1(Sheet 1), whereas Sheet 3 should pull data of A2 & B2(From sheet 1), and Sheet 4 should should pull data of A3 & B3(from Sheet 1) and so on.
Can you please help me with this?
Thanks in advance.
@Rahul
How are the 85 worksheets named?
Hi Hui,
Thanks for replying.
I have successfully completed my task using a VB code.
Hi Rahul
I am also looking for something similar. Is it possible for you to provide me with the VB Code?
Your help is much appreciated.
Thanks
Please send the VBA code Break the sheet into multiple workbooks by State, District, Region & zone wise at a time same data.
@Braj
Have a look at this post:
http://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/
This is simple transaction processing. Vijay's solution works ok albeit somewhat slow (select,copy,paste) and difficult to follow.
A more straightforward approach is to use Tables aka ListObjects. Just rename the 4 Tables DataEntry, Table1, Table2 and Table3 and ...
Sub DataEntryToEmployee()
Dim oSrcTable, oDestinationTable As ListObject, oListRow, oNewRow As ListRow, sEmployeeCode As String
Set oSrcTable = Sheets("Data entry").ListObjects("DataEntry")
For Each oListRow In oSrcTable.ListRows
sEmployeeCode = oListRow.Range.Cells(3)
Set oDestinationTable = Sheets(sEmployeeCode).ListObjects("Table" & sEmployeeCode)
Set oNewRow = oDestinationTable.ListRows.Add
oListRow.Range.Copy Destination:=oNewRow.Range
Next oListRow
End Sub
So I have tried to manipulate this code for the same puprpose, but with column E as my designator to sheets. the code looks like this:
Sub copyPasteData()
Dim strSourceSheet As String
Dim strDestinationSheet As String
Dim lastRow As Long
strSourceSheet = "Sheet1"
Sheets(strSourceSheet).Visible = True
Sheets(strSourceSheet).Select
Range("E2").Select
Do While ActiveCell.Value ""
strDestinationSheet = ActiveCell.Value
ActiveCell.Offset(0, -4).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
Selection.Copy
Sheets(strDestinationSheet).Visible = True
Sheets(strDestinationSheet).Select
lastRow = LastRowInOneColumn(A)
Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets(strSourceSheet).Select
ActiveCell.Offset(0, 4).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub
(basically the same but modified source sheet and the cell offset)However, It gives me a compile error on LastRowInOneColumn. If i remove (A) from the line it will give me the last entry for each variant in column E in the various worksheets. I am unable to figure this part out. Please help how I should correct it. Thanks so much in advance!
Hi,
I think that you need an integer for that.
Column A would be 1.
Regards
Gary
Well I tried that, and still no luck. So far the only thing that has gotten ANY results was removing the whole "(A)", but then it gave an incomplete data set. Any further help would be much appreciated!
Hi I an using the similar code but I am unable to run it as its giving a compilation error would request if you explain how did you rectify the following command line:
lastRow = LastRowInOneColumn (A)
@Kaushal
Can you please ask the question at the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file to simplify the analysis
HELLO EVERYONE
can anyone help me with this problem
can you look first at the pic http://i.imgur.com/frIGU7o.png
I need the values of cells in column O and P take their values according to the numbers in the column L
for example
cell L3 have the value 16 then in cell o3 the value will be .56 and in cell P3 the value will be 0
another example cell L4 have the value 11 then in cell O3 the value will be .37 and in cell P3 the value will be 0
is there any way to display a message box if there is not a tab for an employee code. For instance you add employee code 4, but forgot to creat ethe tab for it?
Hi, It works great but I want this to copy 19 cells from left not -2 unlike your sheet
ActiveCell.Offset(0, -19).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
But it copies only first two rows than gives me error
Runtime Error: 1004
Application-Defined or object defined error
Alam,
Change the code from
ActiveCell.Offset(0, 2).Select
ActiveCell.Offset(1, 0).Select
to
ActiveCell.Offset(0, 19).Select
ActiveCell.Offset(1, 0).Select
How it is checking the unique values ??? and seperating ??
Dear all, i have code vb for export to excel :
With xlWorkBook
.sheets("Sheet1").Name = "test1"
.sheets("Sheet2").Name = "test2"
End with
How is that possible to customize column in each sheets?
For example,
- in sheet test1 i want column will be appear : name, class, course
-in sheet test2 i want column will be appear like : score, absent, teacher.
Should i use if else? Can you teach me where should I put if else, and how the code? Thank you in advance.
I have a report that is created everyday that I copy and paste into an excel spreadsheet and then copy and paste by criteria ( in my case a number used to represent a county name i.e. 1 = Abbeville ). So I highlight and copy all data in column A that also has value "1" in column B. I think it would be much easier if I could copy and paste this list into the first tab in my spreadsheet, and then have all the data filtered into the correct tabs by county code. I am very new to this, but I am confident I can follow simple instructions. I have pre-existing data in these tabs that I don't want to be overwritten, but instead to be added to the next available cell to continue the list. I don't understand how to do this on a scale of 46 different tabs. (I have 7 digit numbers in column A and 1-91 only odd numbers for column B). Any suggestions? It is so time consuming to have to copy and paste 46 different times every day.
@Jay
Can you please ask the question at the Chandoo.org Forums
http://chandoo.org/forum/
I'd also suggest posting a sample file
This works for what i need to do but would like to take what is copied and have it paste the data into another location on the other spreadsheet.
Example
copying data from A1 through E1
would like it to paste in another spreadsheet into A8 through E8
Right now it puts the date in A2 through E2
I figured my question out
Dear all, I have a database. I copy the respected information about an employee and paste in the respected sheet on the basis of his/her field of education, like if field is Finance the data of that employee will go to the Finance sheet and so on.., I have to do it for many times due to bulk data. I need a VBA Code that may do it automatically.
One more thing the data of employee is on 10 number of rows.
I need a code that look for value (Finance ) in the education field. (One by one for each employee) if the value is there then copy the 10 rows of the data and paste in the Finance sheet. and there are number of fields of education Finance, HRM, Engineering, Mass Communication, ect.
Thanks all.
Hello,
pls help in VBA code ..
sheet2 (A2:F23) tabel data come in sheet1(A2:F4) , 3 by 3 step in vba code for Spin button ..
@Subhajit
Can you ask the question in the Chandoo.org Forums
http://chandoo.org/forum/
Please attach a sample file to aid the process as it is unclear what you require here
Sir,
Can you give me a macro code to move Data from One workbook to another workbook using user form. i.e. I want to copy some cells from Book1.xlsx to rows of Book2.xlsx. Source cells of Book1.xlsx are constant but Destinations cells in Book2.xlsx are different, i.e. data must be copy in empty row of Book2.xlsx.
Please give me a code. It very important for me.
I have diffrent types of data in 1 sheet i.e. row no 1 to 20 have diffrent data and 21 to 40 hve diffrent data & i want to copy row no 1 to 20 and 21 to 40 in to seprate worksheets how can i do this
I have diffrent types of data in 1 sheet i.e. row no 1 to 20 have diffrent data and 21 to 40 hve diffrent data & i want to copy row no 1 to 20 and 21 to 40 in two seprate worksheets how can i do this
Hi Guys
I want to set up a VBA formula that populates a summary from 2 worksheets:
1 - Income worksheet shows 3 columns, B "Source", C "Product" and D " Income".
2 - Cost worksheet shows 3 columns, B "Source", C "Product" and D " Cost".
3 - Summary worksheet should summarize the amount of total income "column C" and cost "Column D" per product listed on Column B based on match of Source selected B2 with Column B under worksheets " income" or "cost".
It works with excel formula:
=SUMPRODUCT((B$2=Income!$B$3:$F$50000)*(Income!$D$3:$D$50000)) for incomes and
=SUMPRODUCT((B$2=Cost!$B$3:$F$50000)*(Cost!$D$3:$D$50000)) for costs. But i would like to have it in VBA.
I will appreciate any help
First add counting from 1 to 10 till last row in column B Every time it should create New Workbook Then create 1 to 10 tabs in that book copy the respected data on respected tabs
i m urgent in program
Dear Sir,
I want to copy data from A1, E3 & L7 of Sheet1 to H4, N9N & P11 of Sheet2. But formula should not be copied .....Sir, I have toiled hard to search such a formula but I could not be succeeded....So would you frame such vba code in excel and send me to my mail id i.e. bhaiswarpravin@gmail.com....Thanks and kindest regards sir.
Following Suresh's problem, do you have a VBA code if there are 2 differences:
1. i have 2 criteria (columns E and F)
2. my sheets are not named exactly like the criteria
Hi so I am trying to utilize this script for my organization on an excel based Employee phonebook in one Workbook that was manually created. I have one Master List setup on the 1st Worksheet and then I have 3 separate worksheets that are based on Geographic Region (County) that towns are in. on the Master List I have all sorts of Different Towns, Some Counties have Multiple Towns in them I want to be able to have the Masterlist automatically filter to the Sublists based on what county the town is in and do so everytime it is updated with new information also I would like it if the Master list has names removed from it it will also do the same in the corresponding Sublist
With regards-some specified data whether all along or with some break/s
could also exist there into each personal separate ledger account excel files or multiple? as an self Auto reflection from continuing main master excel sheet say cash book.This is not a fixed nature (just see manual working at cash book we have to maintain ledger each accounts).So only by working at main excel sheet the data should also exist automatically at each personal ledger files.Thus by doing this saving extra efforts of copy paste posting.How, please
Imtiyaz Khan
Hi, i want to ask. How to split data from one master worksheet to multiple sheets on another new workbook? For split data on same workbook, i already done just i dont know for another workbook.
And is there any simple macro for put inputbox for this kind of code.
Set ws = sheets("sheet1")
Im sorry im really a beginner of this macro n vba thing.
Hi,
i want to sort by column B instead of column C as per your example, After changing in the code but it gives error.
Please reply
Hello,
Can you please help me with a code for following actions:
There is an excel file with existing sheets and information in the first three columns in every sheet. The name of the sheets are codes of offices.
I have another excel where I have information in columns D,E,F. In column D are the codes of the offices and in the other two columns is the additional info that I have to copy in the first file again in column D,E and F. The criteria is the code of office.
I will be very thankful if you can help me.
Hi,
I am using a code to split tabs into new WBs. The issue is the formulas stop working on the new WBs as the refrence sheet is still in the original sheet. Is there a solution for it
the Code is:
Sub CreateNewWBS()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim strFilename As String
Set wbThis = ThisWorkbook
For Each ws In wbThis.Worksheets
strFilename = wbThis.Path & "/" & ws.Name
ws.Copy
Set wbNew = ActiveWorkbook
wbNew.SaveAs strFilename
wbNew.Close
Next ws
End Sub
I am trying to write a script where I copy data from 2 workbooks, paste into my master file and update my pivot tables based on the new data to create an invoice.
VB code:
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+e
' ***** From file called May 2018 - Vendor Energy - 6.15.18 - Invoice.xlsx Tab Sheet1 to current file Tab called Sheet22 *****
'
'
Workbooks.Open Filename:= _
"C:\Users\rzepeckir\Documents\Vendor Current Month\May 2018 - Vendor Energy - 6.15.18 - Invoice.xlsx"
Selection.AutoFilter
ActiveCell.Offset(-17, -6).Range("A1:P224").Select
Selection.Copy
Windows("VBA Extractor r57with code.xlsm").Activate
'ActiveCell.Offset(-1, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+f
'
'***** From file called Copy of Vendor Master Pole set File 06-12-18.xlsx Tab Sheet1 to current file Tab called Sheet20 *****
'
'
Workbooks.Open Filename:= _
"C:\Users\rzepeckir\Documents\Vendor Current Month\Copy of Vendor Master Pole set File 06-12-18.xlsx"
Selection.AutoFilter
ActiveCell.Offset(0, 0).Range("A2:AQ40000").Select
Selection.Copy
Windows("VBA Extractor r57with code.xlsm").Activate
'ActiveCell.Offset(-1, -1).Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub
' Refresh all applicable pivot tables to setup month's data
Sub refresh()
'
' refresh Macro
' refresh data
'
' Keyboard Shortcut: Ctrl+r
MsgBox ("Update may take several minutes, Click Ok to begin")
' replace with Refresh All (had to remove the check for Enable Background refresh to make it wait)
ActiveWorkbook.RefreshAll
' Refresh all Pivot tables in all worksheets
Dim shtTemp As Worksheet
Dim pvtTable As PivotTable
For Each shtTemp In ActiveWorkbook.Worksheets
For Each pvtTable In shtTemp.PivotTables
'pvtTable.RefreshTable
Next
Next
MsgBox ("Update Complete,All data is Up-to date")
End Sub
Public Function save()
End Function
//////End VB Code
I am trying to make this run through once and then save the file with the updates.
Any help would be greatly appreciated.
Thank you,
Ray
I am beginner in VBA for excel (Using Excel 365) I just copy and run the above vba code but have a compile error: sub or function not defined in
lastRow = LastRowInOneColumn("A")
any suggestion regarding that error? thank you.
BR
Asnawi
Dear Chandoo,
No Doubt, Code is perfect. but how to skip if duplicate raw found in those sheets where data are moving?
Thanks Prabhat... I suggest using "Remove duplicates" option for dealing with duplicate rows. You can apply it on a range object.