Copy Data from one Worksheet to Another in Excel VBA
Home » Excel VBA » Copy Data from one Worksheet to Another in Excel VBA
Description:
When we are dealing with many worksheet, it is a routine thing to copy data from one worksheet to another in Excel VBA. For example, we may automate a task which required to get the data from differen worksheets [some times different workbooks]. In this situation, we need to copy the some part the worksheet and paste it in a target worksheet.
Copy Data from one Worksheet to Another in Excel VBA – Solution[s]:
The following example will show you copying the data from one sheet to another using Excel VBA.
Code:'In this example I am Copying the Data from Sheet1 [Source] to Sheet2 [Destination] Sub sbCopyRangeToAnotherSheet[] 'Method 1 Sheets["Sheet1"].Range["A1:B10"].Copy Destination:=Sheets["Sheet2"].Range["E1"] 'Method 2 'Copy the data Sheets["Sheet1"].Range["A1:B10"].Copy 'Activate the destination worksheet Sheets["Sheet2"].Activate 'Select the target range Range["E1"].Select 'Paste in the target destination ActiveSheet.Paste Application.CutCopyMode = False End SubInstructions:
- Open an excel workbook
- Enter some data in Sheet1 at A1:B10
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to run it
Now you should see the required data [from sheet1] is copied to the target sheet [sheet2].
Explanation:We
can use two methods to copy the data:
Method 1: In this method, we do not required to activate worksheet. We have to mention the source and target range. This is the simple method to copy the data.
Method 2: In this method, we have to activate the worksheet and paste in a range of active worksheet.
The main difference between two methods is, we should know the destination worksheet name in the first method, in second method we can just activate any sheet and paste it.
Download the Example Macro Workbook:
Download the Example VBA Macro File and Explore the code example to copy the data from one sheet to another worksheet:
Copy Data Form One Sheet To Another Sheet
More Macros on Copying Data Using VBA:
- Copy Method Explained
- Copy Data from one Worksheet to Another in Excel VBA
- Copy Data from One Range to Another in Excel VBA
- Copying Moving and Pasting Data in Excel
- Data Entry Userform
- VBA to Append Data from multiple Excel Worksheets into a Single Sheet – By Column
- VBA to Consolidate data from multiple Excel Worksheets into a Single Sheet – By Row
- CopyFromRecordset
- VBA Sort Data with No headers Excel Example Macro Code
- VBA Sort Data with headers Excel Example Macro Code
120+ Professional Project Management Templates!
Save Up to 85% LIMITED TIME OFFER
A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.
All-in-One Pack
120+ Project Management
Premium Templates
Essential Pack
50+ Project Management
Premium Templates
50+
Excel
Project Management
Templates Pack
50+ PowerPoint
Project Management
Templates Pack
25+ MS Word
Project Management
Templates Pack
Ultimate Project Management Template
Ultimate Resource Management Template
Project Portfolio Management Templates
Share This Story, Choose Your Platform!
100 Comments
I am trying to copy specific ranges from 12 worksheets on from the “Source” workbook to the same specific ranges on 12 worksheets to the “Target” workbook. How can I set up a “loop” to accomplish this in a VBA code? Thanks!
– Ron –
‘If your input files are always same then store them in an Array
mySource=Array[“File1.xlsx”,”File1.xlsx”,…,”File12.xlsx”]‘If they are not same always then read your workbook names and store it in an Array [mySource]
'open each workbookFor iCntr=1 to 12
set sourceWB=Workbooks.Open[mySource[iCntr-1]] ' since array starts from 0
sourceWB.Sheets["YourSheetName"].Range["YourRange"].copy ThisWorkbook.Sheets["YourTargetSheet"].Range["TargetRange"]
'Example:sourceWB.Sheets["Sheet1"].Range["A1:A10"].copy Destination:=ThisWorkbook.Sheets["YourTargetSheet"].Range["A" &iCntr*10+1]
nextSearch with a keyword ‘downloads’ in our site, you will get the working file to see the code.
Hope this helps-PNRao!
How can we select user choice range and paste it in user defined range? Can we give ability to transpose the data if user wants? Do u gave any vba script that can help me with that?
Hi,
We give the user to select a range to copy and range to paste in two different ways.
1. You can use two input boxes: one is to accept the ranges from users and other one is to choose the range to paste.
Please check this link to for advanced input box examples: //analysistabs.com/excel-vba/inputbox-accept-values/2.The other method is, creating userform and place two RefEdit controls. one is for to select the range to copy and the other one is the for accepting the range to paste.
So, now you know the range to copy and the range to paste the data. Then you can use, pastespecial method to transpose the data
Example: Let’s say you accepted two ranges from user and stored as rngToCopy and rngToPaste
Range["rngToCopy"].Copy
Range[rngToPaste].Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True
Hope this helps.
Thanks-PNRao!Hey! I work for a transportation company and have a workbook in which I keep track of trailers parked at a lot daily. Every day I copy the previous day’s sheet and rename to today’s date.
So I have sheet “05-05” and I copy it and rename the new sheet to “05-06”. I have been Googling this for hours now to no avail: I am trying to create a button that runs a macro that creates a copy of the active sheet, and renames it to today’s date. If I have to include the year in my sheet names that is fine. Can anyone help?
Hi there,
Could you please help me with the following:
I have two worksheets named Data and Levels. In Data worksheet I have thousands of rows and three columns [ region, ID, and sum]. In Levels worksheet I have only hundreds of rows and two columns [ID and Level]. What I need is to put for each row in the first sheet its corresponding level from the second sheet.
I’m only a beginner, so detailed explanation would be much appreciated,
Thank you
I am working on a macro to transfer data from one spreadsheet to another. I would like ot transfer the data to the next open row within the second spreadsheet. Basically I want to take electronic batch sheets, with data entered in them, and transfer that data to a central source for record keeping. Is this doable?
Thanks for any help you can offer.
Hi Josh,
We can do this, every time you need to find the last row in the common file and update the data from that particular row. You can search for the examples provided to find the last row in the excel sheet. Or, you can send me the sample file.Thanks-PNRao!
i have one folder named checking master and it contains 4macro enabled excel2010 files and each file has 8 worksheet and i want to copy data from these four macro enabled files specific sheet called”register sheet ” ranging a2to k2 without opening these files into a new file master file in the same folder.further if files becomes more tan 4 macro enabled files then also all files “register sheet’s range a2 to k2 automatically transferred in the master file.please help through vba code and intimate through email and oblige
And, as always, if you have questions about this or any other
financial topic, seek the services of a licensed,
responsible financial advisor or other professional.
You might be able to obtain your score at no cost after you have applied for a
loan. For this purpose you will have to limit the credit account as well as the
credit card, make timely repayments of the credit facility availed and enhance your credit transactions.Hi I am pretty rubbish when it comes to VBA, im trying to put together a stock book where everything in column A, B and C on sheet1 automatically copies on to column A, B and C in sheet2. I want them to appear in exactly the same cell for each column. I dont want to have to click run or a button. Can you help?
In the VB Macro Can you also apply filter conditions from the source workbook to copy [based on filter] and paste it in a destination workbook. please note its a different workbook and NOT sheets.
please give me copy and paste jobs
Hi
for every month, i am using one spreadsheet for each day. so for example for 30 days in a month, 30 spread sheets. the spread sheets contains lot of matter.
what i need is the data of total month details has to come in one spreadsheet with date wise.example: 01.12.2014 02.12.2014…………………………………………………………………..31.12.2014
1 3 7
2 4 7
2 6 2Hi Syam,
You can do this using formula or VBA, please send an example file, we are happy to help you.Thanks
PNRao!Hello
The code works great but is there away of molding it slightly so instead of “Activatesheet.paste” you can paste only the values?
Thank you
Hello
I want to import data form one workbook to another workbook using VBA form,
the main problem is that import dxata cantain duplicate values I do not import them I want only append new values in column.
Please help.
Thank youHi K.S.Gill,
You can check the duplicated records while updating it. You can do this in different ways:
1.if your input data having minimal rows and columns then you can use for loop to check the duplicates and update record by record.
2. If you have lot of records in your input file, then import all the records to your target file and then delete the duplicate records in one sheet using Delete Duplicate function available in the Excel.
3. If may not want to disturb your target sheet -then you can duplicate the sheet and import the latest data into this temporary sheet. And run the macro to delete duplicates and then merge the unique records to your actual worksheet.
Hope this helps.
Thanks-PNRao!Hello all,
I am beginner and I am looking import multiple text files [2200] to excel and make a single excel sheet using VBA
Please help.. I am trying this using this code…
[CODE]
Sub CombineTextFiles[]
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String
‘===================================
On Error GoTo ErrHandler
Application.ScreenUpdating = False‘======================================
sDelimiter = “:”FilesToOpen = Application.GetOpenFilename _
[FileFilter:=”Text Files [*.txt], *.txt”, _
MultiSelect:=True, Title:=”Text Files to Open”]If TypeName[FilesToOpen] = “Boolean” Then
MsgBox “No Files were selected”
GoTo ExitHandler
End Ifx = 1
Set wkbTemp = Workbooks.Open[Filename:=FilesToOpen[x]]
wkbTemp.Sheets[1].Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close [False]
wkbAll.Worksheets[x].Columns[“A:A”].TextToColumns _
Destination:=Range[“A1″], DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=”|”
x = x + 1While x Date
Date Sta1Load.Pass Sta1Load.
CodeSta1Load.
VisionProgramSta2Armature.Pass
Sta2Armature.Code
Sta2Armature.
VisionProgram
Sta3Weld.Pass: False
2015-1-19 9:19:55 True 411 0 False 0 0 Flase
Text file2 data
Text fiel 3 dataHi PNRao, please could you help me with a VBA formula about this matter? I want to copy the data from sheet1 to sheet2 automaticly. In sheet1 there are formulas in the cells, but I want only the text to be copied to sheet2, everytime something changes in sheet1. Is this possible? I hope for a answer, would be very appreciated! Thanks a lot in advance for any help!
Hi Tom,
You can write something like below in Worksheet change event:
Range["rngToCopy"].Copy Range[rngToPaste].Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True
Hi,
I have some data in sheet 1 and want to summarize it in sheet 2. Column A has the country names and Column B&C has some data. In most of the cases column B&C are not blank and in some cases either of one is blank or both are blank. I want to copy only the countries which has data in either one of the columns in sheet 2, by avoiding the blank rows. Please assistHi JM,
Please refer the below macro:
Sub sbCopyCounryDataIfAvialble_OR_NotBlank[] Dim iCntr, jCntr, lRow As Integer 'iCntr is to loop through the Sheet1 'jCntr is for sheet2 last row 'lRow is the last row with data in sheet1 lRow = 226 'if it is dynamic, please refer our most useful macros to find the last row jCntr = 1 ' For iCntr = 1 To lRow If Cells[i, 2] " Or Cells[i, 3] " Then 'Either of the cell should have data Sheet[jCntr, 1] = Cells[i, 1] ' country name Sheet[jCntr, 2] = Cells[i, 2] ' Column B Sheet[jCntr, 3] = Cells[i, 3] ' Column C j = j + 1 End If Next End SubThanks-PNRao!
Hi Sir,
I am in starting stage of using macros for good effect, here we have a workbook, in which we will have lots of data in sheet1[Data_Source], we have copy data from sheet 1 to sheet2[“Data_Extracted”] with specific headers matching in sheet1 to sheet2
we have to copy only those data which have the specific headings.the data in the sheet1 is dynamic and keeps on changing at the End of each day, and we need only values into sheet2.
in sheet1 we will have formulas in almost all columns,so we require only values to be pasted in sheet2in sheet1 we will be having like for ex 32 columns[headings], we need only 9or 10 columns out of those 32.
please send me the code for this to my mail, your help is highly required.
Many Thanks in advance.
please help ASAP.
I putting together and inventory sheet to track my restaurant inventory. I have figured out how to get the data from my add sheet and my remove sheet to the calculation sheet. The only problem is every time I run the macros it overwrites the data already on they calculation sheet. I have been trying to figure out what code line I need to search for the next available row and then paste my data. Can anyone help me with this? Keep in mind I am a beginner when it comes to this stuff.
Hi,
could you please help me with a VBA formula about this matter? I have approx 60 files [.xls or .xlsx] saved in a drive c:test. However the number of files varies each time. [maybe less or maybe more]… I want to open the files from the directory and paste to master.xls assigned tabs. [i.e. abc.xls must go to master.xls TAB ABC, efg.xls must go to master.xls TAB EFG tab, and so on]. I see a lot of vba that loop through directory if filename xls THEN copy to just master sheet. But since my number of file varies each time, and each file must be paste to assigned TABS. Please assist and thanks in advance.Hi,
i have got a requirement to copy data from One Excel Workbook to another Workbook[Located locally on the same machine]. This will help me consolidate the data am receiving from my peers to a master file. Ideally there should be a VBA button on the source workbook if i click on it, that should trigger copying to the Master Workbook.
Appreciate if someone can help on this. Thanks
Hi
I’m working on excel document and I have to find a solution for situation like this. In one of the cells I need to have one word permanently and when I’m putting one click on it to write next to it a name which I will use on other sheet.
For example: cell -> |Patient: [next to him impute name]|
Is exist an solution to do this using VBA or formulas?
Thank you for any help!Hi,
I have been told in some forums that this isn’t possible, but have been working on figuring this out for weeks! The company I work for is a Gov’t Subcontractor. The owner has a master spreadsheet “Pipeline Data”. There are headers in row 1, data from columns A-S and currently 348 entries. Columns M & N contain years for the solicitation date and award date [these can be the same year or 5 years apart]. Is it possible to have a code transfer the data in the rows that have 2015 as the year in either M or N to a worksheet “2015”? The owner wants to be able to click on the worksheet 2015, 2016, 2017 etc. and see all the solicitations and awards that will occur in that year.
The other tricky part is he wants it to automatically update when I enter new data in a row….so for example, if the new data has solicitation year 2015, but awarded in 2018 then it will automatically copy to both sheets 2015 & 2018.
I did find a code in a forum that worked checking column M for the dates, but having no experience with VBA, I have no idea how to rewrite it to check both columns M & N. As I stated, others have told me Excel was not capable of doing this, but I want someone else’s opinion. I know the owner is tired of hearing “I’m still working on it”, but I have yet to find a way for this to work and I am a determined lady. Thank you in advance for any help you can offer me!!Hi Dima,
You can write VBA code for this in Selection_Change[] event of the worksheet:
The below example will write current date and time on the B1 when you click on the A1:
Please paste the below code in the required worksheet code module:
Private Sub Worksheet_SelectionChange[ByVal Target As Range] If Target.Address = "$A$1" Then Target.Offset[0, 1] = Now[] Target.Offset[0, 1].Select 'Or Range["B1"]= Now[] End If End SubThanks-PNRao!
Hi there if its possible to help me with a very complicate issue in vba.
Well the situation is this: i have a set of around 50 files [more less] and there are all in the same format [i have created them]. There are answers from a research that i did [with names and values 0,1 or 2]. Like the following:
B5 = Question 1
B6 = Question 2
B7 = Question 3
etc
to Question 30from D4 to N4 horizontaly there are names like [Thomson, James, Howard…etc ] where are corresponded values like 0 or 1 or 2 per name and question. The only difference between these 50 files is that not always each cell is fullfiled from [d4 to N4 with names] . Sometimes are less than 11 names and left it blank the coressponded cell but the format is the same for all 50 files.
What i want to do is little complicated. I want to take each response from FILE1,FILE2…to FILE50 and copy the values into another file or sheet. In the new file or sheet i want to organized the answers in lists per answer and per question.For example if someone responded
D5=0 [Thomson responded 0],
E5=0 [James responded 0]
F5=2 [Howard responded 2] in File1,and in File2
D5=0 [Brandley responded 0],
E5=1 [Clarck responded 1]
F5=2 [Nickolson responded 2]I would like to create a list like the following anywhere in a the new sheet or file:
Question1
[answered 0 ]
Thomson
James
BrandleyQuestion1
[Answered 1]
ClarckQuestion1
[Answered 2 ]
Howard
NickolsonThese lists should be created for every question from Question1 to Question30.
I hope i explain well what i would like to do and i hope even more and i would appreciate any suggestion and advice from anyone for this complicated work.DEAR Matt,
pls explain ur need in detail. i will help you. I am also in Logistic service. Its a favour for me to help anotherf logistics man. Thank u. VasuHi,
Now I want to copy 1 column of data to another workbook, but the data need to be separate in different columns which is not continuous column.
Hi Zhao,
you can record a macro and use ‘Text to Columns’ command in the data tab. And change the auto generated code accordingly.
Thanks-PNRao!
Hi,
I want to know what i am trying to do is possible or not in excel and if how?
I want to write data in one sheet [e.g.. sheet1 A1 to G1] and that data get copied to sheet two[e.g.. Sheet2 A1 to G1] then again when i write in the same row of sheet1 [A1 to G1] this time the data get copied to sheet2-A2 to G2 and this process goes on.
Basicalyy i want to fix my user input cell in sheet1 and the resultant will be transferred to sheet 2.
Is their any way to do this.
Thanks in advance for your reply.Hi Krishna,
Here is the code to copy the sheet1 data into sheet2. You can place a button in sheet1 and assign this macro. You can ask your users to fill the data in sheet1 and hit the button to update the sheet2.Sub CopyRangeFromSheet1toSheet2[] Dim lastRow As Long lastRow = Sheets["Sheet2"].Range["A100000"].End[xlUp].Row + 1 ' then next free row in sheet2 Sheets["Sheet1"].Range["A1:G1"].Copy Destination:=Sheets["Sheet2"].Range["A" & lastRow] End SubThanks-PNRao!
Hello,
we have 100 number of files in a folder which has name like C1.. C2.. C3 to file C100. we have defined name range in all files.
we need output in a different worksheet named “WORKBOOK”, Sheet name “OUTPUT” Continuously starting from cell A1.
we want to import a name range [values with same format] which we defined in file C1 to C100 sheet:Sheet1 Cell:A1 [want to loop till it finds a false named range in any file in sheet1 cell A1]
[Name ranges are either 7 columns or 14 columns, 24 rows as defined.]
we also have a “OUTPUT” sheet in all files where we get in range A1:N24
we can also use that as targetcan anyone please help regarding this..
Hi,
I have 4 columns of data in Sheet 2. I need to search a value in column A and a value in column B. Once both column match at row X, I want to copy column C, row X : row X+1 and column D, row X : row X+1 to show in Sheet 1.
Please show me how to do this. Thank you!
I have a simply request, I am constructing a call log for myself, I have a drop box that has started, Pending, Follow up and Completed,
I would like to be able to copy the whole row of data to another worksheet in the same workbook Pending for Pending, Follow up for Follow up and Completed, and also have them removed from the main worksheet when drop box is used to go from started to the other choices, Is the a way to do this
Any and all help is always appreciated
Thanks in advancedHi Woody Ely,
Hope this example helps to solve your requirement:
Example file to download: //analysistabs.com/download/copy-data-one-sheet-another/
Sub CopyDataBasedOnStatusCondtion[] Dim lRow, cRow As Long lRow = Sheets["YourMain"].Range["A50000"].End[xlUp].Row 'Last row in your main sheet 'change the sheet name as per your needs 'Let's find these items in the Main sheet and send to the respective sheet For j = lRow To 1 Step -1 'Assuming you have the drop-down in the first Column [= Column A] 'looping throu your main sheet and copying the data into respective sheet If Sheets["YourMain"].Range["A" & j] = "Pending" Then cRow = Sheets["Pending"].Range["A50000"].End[xlUp].Row Sheets["YourMain"].Rows[j].Copy Destination:=Sheets["Pending"].Range["A" & cRow + 1] Sheets["YourMain"].Rows[j].Delete ElseIf Sheets["YourMain"].Range["A" & j] = "Follow up" Then cRow = Sheets["Follow up"].Range["A50000"].End[xlUp].Row Sheets["YourMain"].Rows[j].Copy Destination:=Sheets["Follow up"].Range["A" & cRow + 1] Sheets["YourMain"].Rows[j].Delete ElseIf Sheets["YourMain"].Range["A" & j] = "Completed" Then cRow = Sheets["Completed"].Range["A50000"].End[xlUp].Row Sheets["YourMain"].Rows[j].Copy Destination:=Sheets["Completed"].Range["A" & cRow + 1] Sheets["YourMain"].Rows[j].Delete End If Next '*NOTE:if this solution is for your clients- 'You may have to write some validation 'steps to check if all required sheets are available in the workbook 'to avoid future isssue End SubThanks-PNRao!
Sir,
I appreciate all your hard work, I had my popup in column K but no worries I just started over and used column A and all is working out. I hope I can call on you again.
Thanks
WoodyHi there, I have a ~maybe~ simple request:
I am constructing a sales history. Where I have all the sales of the day recorded on a sheet.
After we close, I copy [using a Macro] all the info to another Sheet.
This sheet have info on Column A to N. [beginning on row 7, rows 1 – 6 are for the header]
Also, I’m trying to filter and copy some rows from that sheet to another.
The “Search” I’ve made using a UserForm, but I’m having some trouble with the “copy & paste” part.
Could you suggest a way to do it ?
If you need, I can also paste the code..
Thanks in advanced!Your most welcome Woody!
Thanks-PNRao!Hi,
Kindly help me on the following problem I have a sheet with some names of the products with their company names in two columns respectively & in corresponding the quarter sales is updated in a workbook which need to be copied again to the other workbook which is having same product name in one column & company name in next column in next column & quarter sales [needed to be copied along with values+ cell comments] in other column. The column keeps on changing for each quarter i.e if its A column for this quarter then its B for next quarter.
Kindly help me with a macro for the same ASAP.
Thanks in advance.Hi,
I’m looking for a VBA code to export tables from a workbook to another .
Here’s what I have. Workbook1 [“Validation”] has tables exported from access queries. Tables are exported into sheets [F01,F02,F03……F10].
This tables needs to be exported to another workbook [“Validation Update”] to next blank row. So, table from Workbook1/Sheet F01 to be exported to next blank row, Workbook2/Sheet F01.
And repeat the process for sheet F02, F03 to F10. Some of the tables may be empty sometimes. Is there any way to use N/A in that case?Any help would be greatly appreciated !
Hi,
In my first excel sheet there are around 20 columns but want to copy around 5 of them to the second worksheet using a VBA macro.
Please helpHi Manny,
Please find the below macro code.
Sub Move_Data_From[] Sheets["Sheet2"].Columns["A:E"].Copy Destination:=Sheets["Sheet3"].Range["A1"] End Sub
Where
Sheet2 – Source Sheet Name
Sheet3 – Destination Sheet Name
Columns [“A:E”] – Source Data [Column A to E]
Range[“A1”] – Destnation Cell or rangeThanks-Valli
Hi There, I want to copy a specific column from one workbook to another workbook using VBA macros, and both workbooks have merged cells in between the rows , please I need your urgent response
Hi Hella,
The below code will copy Column D of Sheet1 from Book1 to Book2:
'Clear the target Column to avoid Merge Cells Warning Workbooks["Book2"].Sheets["Sheet1"].Range["D:D"].Cells.Clear 'Now copy the Column D to target Workbook Workbooks["Book1"].Sheets["Sheet1"].Range["D:D"].Copy _ Destination:=Workbooks["Book2"].Sheets["Sheet1"].Range["D1"]
Hope this helps!
Thanks-PNRao!Please request your assistance in Private sub, this is how i want macro to work
if i enter Email id in sheet 1 any cells of the worksheet it should automatically get updated in “Sheet2 – coloumn 1” and also email id’s should be unique in sheet 2 [i.e no duplication should happen] Please assist me…Hi, Want to copy data from various sheets in one, is that possible using Macro?
Hi,
I am new to VBA code and don’t really know anything about coding. I am desperately trying to get my worksheets to:
When yes is entered in a column the macro copies the row the yes is in and transfers it from the waiting list spreadsheet it is in, to the allocated spreadsheet in the next available row, of the same workbook. It would be great if once transfered the data could be deleted from the waiting list spreadsheet and the data below it be moved up.This is what i have put so far [like I said I am not a natural at this]:
Sub MoveText[]
‘
‘ MoveText Macro
‘ Move text from Waiting List tab to Allocated tab
‘‘
Range[“L8”].Select
Do Until Selection.Value = “yes”
If LCase[Selection.Value] = “yes” Then
Selection.EntireRow.Copy
Loop
End IfSheets[“Allocated”].Select
Range[“A6”].SelectIf IsEmpty[ActiveCell.Offset[1]] Then
ActiveCell.Offset[1].Select
Else
ActiveCell.End[xlDown].Offset[1].Select
End IfSelection.Paste
Sheets[“Waiting list”].Select
Range[“L8”].Select
Do Until Selection.Value = “xxx”
If LCase[Selection.Value] = “yes” Then
Selection.EntireRow
Selection.Delete
Else
Selection.Offset[1, 0].Select
End IfI keep getting error messages and have no idea what I am doing wrong. Can anyone kindly help me?
Stacey
Hi All
Not sure whether this is acheiveable but here goes…
I have a workbook with 3 work sheets – Order Form / Stock Level / Order History
When completing the Order Form, a check is carried out agasint the inputted cell to see if it is in stock, if it is then the Stock Level work sheet is updated accordingly. If it is not availble [