Free Microsoft Excel 2013 Quick Reference

Excel VBA Copy Cells from multiple sheets to new sheet based on cell values

Good morning, I have been struggling with this project for a week now. I am well versed on Excel with some experience in VBA. What I'm trying to do is copy non-adjacent cells/rows from over 100 different sheets into a new sheet in a summary format. I was able to get this to work initially when only copying specific cells in each sheet. When I tried to look at different fields and their values to determine what data I wanted to copy the code was outside of my experience level. So basically what I want to do is copy the following cells in each sheet if there is no date in the cell range between b12:b2000 on each of the sheets. If there is no data then the only cells I would want to copy and paste across a row are cells B7, B5, H7, H8, B9, C9, D9, K7, K8, P8, Q3, Q5. If there is data in the range of b12:b2000 I would want cells B7, B5, H7, H8, B9, C9, D9, K7, K8, P8, Q3, Q5 and the value to the left of what is found in the b12:b2000 range as well as the next two values to the right of column b. This is the code I have so far.

	VB:
	
 Button1_Click() 
    Dim SiteCol As Range, Cell As Object 
    Dim ws As Worksheet, LR As Integer 
    Application.ScreenUpdating = False 
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "FMS" 
    Set SiteCol = Range("b12:b2000") 'Range containing values
    For Each ws In ThisWorkbook.Worksheets 
        For Each Cell In SiteCol 
            With ws 
                If IsEmpty(Cell) Then 
                    .Range("B7").Copy 
                    Sheets("FMS").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("B5").Copy 
                    Sheets("FMS").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("H7").Copy 
                    Sheets("FMS").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("H8").Copy 
                    Sheets("FMS").Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("B9").Copy 
                    Sheets("FMS").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("C9").Copy 
                    Sheets("FMS").Range("F" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("D9").Copy 
                    Sheets("FMS").Range("G" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("K7").Copy 
                    Sheets("FMS").Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("K8").Copy 
                    Sheets("FMS").Range("I" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("P8").Copy 
                    Sheets("FMS").Range("J" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("Q3").Copy 
                    Sheets("FMS").Range("K" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("Q5").Copy 
                    Sheets("FMS").Range("L" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                End If 
                If Cell.Value > "0" Then 
                    .Range("B7").Copy 
                    Sheets("FMS").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("B5").Copy 
                    Sheets("FMS").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("H7").Copy 
                    Sheets("FMS").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("H8").Copy 
                    Sheets("FMS").Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("B9").Copy 
                    Sheets("FMS").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("C9").Copy 
                    Sheets("FMS").Range("F" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("D9").Copy 
                    Sheets("FMS").Range("G" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("K7").Copy 
                    Sheets("FMS").Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("K8").Copy 
                    Sheets("FMS").Range("I" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("P8").Copy 
                    Sheets("FMS").Range("J" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("Q3").Copy 
                    Sheets("FMS").Range("K" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    .Range("Q5").Copy 
                    Sheets("FMS").Range("L" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                    Selection.Value = Cells(Cell.Row, 13).Value 
                End If 
            End With 
        Next 
        With Worksheets("FMS") 
            On Error Resume Next 
            .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete 
            On Error Goto 0 
            LR = .Range("A" & Rows.Count).End(xlUp).Row 
            .Range("A1:B" & LR).Sort Key1:=Range("A1"), Order1:=xlAscending, _ 
            Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
        End With 
        Application.CutCopyMode = False 
        Application.ScreenUpdating = True 
    Next ws 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help with this would be greatly appreciated.


Post your answer or comment

comments powered by Disqus

Related Results

  1. Copy data from workbook 1 to workbook 2 based on cell value in workbook 1
  2. Copy multiple columns within row from multiple workbook to one format based on column
  3. Copy data from one workbook to another based on criteria
  4. Copy Row Based On Checkboxe Value
  5. Copy defined cell from various worksheets to new sheet (Index sheet)
  6. Copy & paste data from multiple workbooks to new workbook in a folder
  7. Copy Range From Multiple Worksheets to a Single Worksheet
  8. Copy data from one worksheet to new one based on unique ID
  9. Copy workbook to new workbook based on cell value
  10. Copying cells to new workbook based on cell background....
  11. Copying cells to new workbook based on cell background....
  12. Can I automate copy of rows based on cell values
  13. Can I automate copy of rows based on cell values
  14. Create New Workbook Based On Cell Value
  15. Copy Same Range From Multiple Worksheets To Summary Sheet
  16. Copy and Paste cell value from multiple sheets to Summary Sheet
  17. Copying a set of cells from multiple worksheets to new row on summary sheet
  18. Excel VBA Code to add data to new sheet based upon value in cell
  19. Copy Cells from 2 Columns to Another Sheet Using Checkboxes
  20. Loop thru cells and move data from specific columns to other sheet
  21. Copy data from multiple sheets
  22. Copy Sheet to new Sheet and clear cells on original sheets
  23. Auto copy data from one worksheet to another worksheet based on week ending dates
  24. Transfer data from multiple cells to new worksheet based on cell value
  25. Hi all. VBA to replicate table based on cell value in....
I have a problem that I hope someone can help me with.

I have 2 workbooks that I am working with.
I wish to copy data from workbook 1 to workbook 2 based on the following criteria.
A value in cell "B1" of workbook 1 needs to equal a value of a cell in column "A" of workbook 2... So it needs to find the corresponding cell with equal value of cell "B1" in column "A". Note the value in cell "B1" is the "key" for the records.Once the "key" has been found in row? in Workbook 2, i wish to paste the value in cell "D1" of workbook 1 into the cell in column "E" in this same row as the "key".Is it possible to do this and if so how can I do this?

Can someone please help me?

Thanks

Regi

I am new to VBA macro coding and have been trying to write a VBA macro to copy data in multiple workbooks to another workbook based on the column headings but am having issues with the formatting. In addition, I only need 6-7 specific columns on a row to move, not the entire row. The columns names are the same in all workbooks, but they are in different order. The movement should just add the data to the next available row. I have written a couple of macros that take the full row based on values, but never column headings or partial rows. I have included an example to assist with the explanation. The problems I am having are mapping the columns correctly when in a different order and moving all of them as one row (certain rows may have a blank.) I appreciate any help you can give!

Workbook1: Moving ID, Name, and Zip

ID Name Zip Order
1 Jim 90210 23
2 John 90211 42

Workbook2: Moving ID, Name, Zip, Make

Name ID Zip Order Make
Steve 1 90210
Rich 2 46913 Toyota

Final Workbook Template should be: Data imported to ID, Name, Zip, Make
ID Make Model Zip Name
1 90210 Jim
2 90211 John
1 90210 Steve
2 Toyota 46913 Rich

Hi I have tried to put together some code using help forums, what I need to do is copy remarks from old workbook to new workbook based on 2 criteria, I have tried using evaluate sumproduct but I keep getting VAlue# or Ref# errors.. please see code and attached workbook.

	VB:
	
 TransferRemarks_Click() 
    Dim SelectFile As String 
    Dim FolderName As String 
    Dim FileName As String 
    Dim wbTarget As Workbook 
    Dim wbLocal As Workbook 
    Dim i As Integer 
     
    Set wbLocal = ActiveWorkbook 
     
    ChDir (ActiveWorkbook.path) 
     
    SelectFile = Application.GetOpenFilename("Microsoft Excel Book,*.xls; *.xlsx") 
     
    If SelectFile  "False" Then 
        FileName = Dir(SelectFile) 
        FolderName = Replace(SelectFile, "" + FileName, "") 
    End If 
    Set wbTarget = Application.Workbooks.Open(FileName) 
    wbLocal.Activate 
    With Sheets("AUSOA") 
        For i = 3 To 5000 
            If Cells(i, 9).Value > "" _ 
            And Cells(i, 10).Value > "" Then 
                 
                Range("L" & i).Value = Evaluate("SUMPRODUCT(--($J" & i & "=FileName!AUSOA!J1:J5000),--($I" & i &
"=FileName!AUSOA!I1:I5000), FileName!AUSOA!L1:L5000)") 
                Range("M" & i).Value = Evaluate("SUMPRODUCT(--('[wbTarget]AUSOA'!J1:J5000=$J" & i &
"),--('[wbTarget]AUSOA'!I1:I5000=$I" & i & "), '[wbTarget]AUSOA'!M1:M5000)") 
            End If 
        Next i 
         
    End With 
    wbTarget.Activate 
    ActiveWorkbook.Close True 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I would like to be able to copy data from multiple columns in a row based on the value of column "A" which is linked to a checkbox in front of it. I have attached a workbook to eplain what I need help with.

Hi everyone,

I encounter difficulties to tune up this following macro so I can copy a cell range from all my existing tab and to paste them in a summary tab called "Index" for eg.
The range I want to specify is (A2:H5) but also other range in the same sheet (A55:H58) and (A108:H111) -well at least-.

I have this following macro that selects and copies all the data from each existing sheet to a new sheet called "INDEX-Summary" :

Sub GrabData()
'
' GrabData Macro
' Copy all data from every sheet to current sheet
' Macro to change
'
Dim wsA As Worksheet
Dim wsANm As String
Dim ws As Worksheet
Dim r As Integer
'
Set wsA = Worksheets.Add(Before:=Worksheets(1))
wsANm = wsA.Name
On Error Resume Next
wsA.Name = "INDEX-Summary"
NameErr: If Err.Number = 1004 Then
Application.DisplayAlerts = False
Sheets("INDEX-Summary").Delete
Application.DisplayAlerts = True
wsA.Name = "INDEX-Summary"
End If
If wsA.Name = wsANm Then GoTo NameErr
On Error GoTo 0
'
For Each ws In Worksheets
If ws.Index <> wsA.Index And ws.Name <> "Summary" Then
r = wsA.Range("A65536").End(xlUp).Row + 1
ws.UsedRange.Copy Destination:=wsA.Cells(r, 1)
End If
Next ws
wsA.Range("A1").Select
'
End Sub

Can somebody modify it so I can copy only defined range of cells into "INDEX-Summary" sheet ? Thanks you!

Hi,

I am trying to have a vb code for copy & paste data from multiple worksheets to new work book called as "ConsolidatedDetails". All multiple workbooks has three sheets each and macros have to copy & paste data from all three sheets. If there is no data in any of the three sheets it should ignore and move to the next file. Since the data is copying from multiple workbooks, the macro should be able to identify the last row in consolidated details and have to continue from all the files.

The data varies in all workbooks and file names are not constant. Based on the selection of files, the data has to be copied. I am using excel 2003 and the folder path is c:trialfilename.xls

please let me know how I can do this.

Hello. I am trying to copy a range of cells from multiple worksheets to an existing worksheet in my workbook. The range is always the same. When the range is pasted into the single workbook it should keep the same values and formats and move to the next available row, starting with the 4th row. Can anyone please help me with the coding for a macro?

I need to know if its possible and how to do this.

I enter whats called a d# in a work sheet. I then want excel to search for any D# that matches and copy the adjacent column (name) to the d# i just entered if it matches. so:

worksheet 1 has multiple d#'s and names (column a and b)
on worksheet #3 i enter the d# . is there anyway to copy the name from worksheet 1 to worksheet 3 without me searching manually?

I'd like to copy an existing workbook (that's closed, preferably) and name
the copy based on cell values. So, for example in column A of the active
workbook I have a list of different excel workbooks that are closed (with the
file path), and in column B I have a list of names that I want to call the
copied workbooks. For all of the items in the list I'd like to copy the
respective workbook and give it the listed name. I figure I need a VBA loop,
but have no idea on the commands needed. Thanks.

Hello All,

I have been working away on this problem for several hours and can't seem to get my code to work. I thought someone here might be able to solve it pretty quickly.

I have a workbook (column A:M) which uses conditional formatting for row M. The conditional formatting is different for all of the rows (there are currently over 100, and more might be added). I need to get a macro that will copy all cell values in the row from A:L to a new workbook when the value of M is colored red or yellow.

I was also wondering if it is possible to automatically name the new workbook based on the date.

Thanks for all your help!

Stu

Hello All,

I have been working away on this problem for several hours and can't seem to get my code to work. I thought someone here might be able to solve it pretty quickly.

I have a workbook (column A:M) which uses conditional formatting for row M. The conditional formatting is different for all of the rows (there are currently over 100, and more might be added). I need to get a macro that will copy all cell values in the row from A:L to a new workbook when the value of M is colored red or yellow.

I was also wondering if it is possible to automatically name the new workbook based on the date.

Thanks for all your help!

Stu

I am trying to automate the copy process of rows from one worksheet to
another based on specific data. I am running a DB query and returning the
data to my workbook. I then want to copy certain rows to another sheet based
on specific values in the first column. The data being returned is Date and
Time formatted as follows: mm/dd/yyyy 13:00 and is returned in ten minute
increments.
I want to automate copying rows from this worksheet to another worksheet
based on specific times in the first column. These values are not always in
the same rows.
Can I write a macro to copy the specific rows based on the time entered in
column one? If yes, what is the proper syntax?

I am trying to automate the copy process of rows from one worksheet to
another based on specific data. I am running a DB query and returning the
data to my workbook. I then want to copy certain rows to another sheet based
on specific values in the first column. The data being returned is Date and
Time formatted as follows: mm/dd/yyyy 13:00 and is returned in ten minute
increments.
I want to automate copying rows from this worksheet to another worksheet
based on specific times in the first column. These values are not always in
the same rows.
Can I write a macro to copy the specific rows based on the time entered in
column one? If yes, what is the proper syntax?

i just need a simple macro that will create and save a new workbook based on the value of a cell in the current work book

Hi! Apologies for again asking about a topic that has been addressed many times, but given my absolute beginner status with VBA or any other syntax, I am having difficulty generalising answers from other threads to my situation.

I have an excel workbook containing 123 worksheets. Sheet1 I have titled "Summary" and I wish to copy data from the remaining sheets (2-123) into it. Each sheet is formatted in the same way, and I wish to take the data in cells E66:G130 from each worksheet and paste it into the Summary sheet (so, Sheet2's 3 columns would be pasted in cell A1, Sheet3's in D1, and so on).

I gave a couple of codes a go (this one is from a thread "Copy Data From Multiple Worksheets & Append To Single Worksheet", I tried to alter accordingly):


	VB:
	
 SummurizeSheets() 
    Dim ws As Worksheet 
     
    Application. Screenupdating = False 
    Sheets("Summary").Activate 
     
    For Each ws In Worksheets 
        If ws. Name  "Summary" Then 
            ws.Range("E66:G130").Copy 
            ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) 
        End If 
    Next ws 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, I don't understand what "ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)" refers to - I am told there is an error with this line ("compile error expected =").

I also tried the Consolidate function, but had problems as well.

If you could help me out, I would be extremely grateful.

Thanks,
Jo

I have zero macro knowledge and went through many similar topics in this forum, but I still can't figure out. I really need your help to create a macro for my case.

I constantly need to copy and paste one cell from multiple worksheets to a summary sheet. I created a sheet upfront called "Summary". My wants are:

1) Copy the worksheet name to the summary sheet;
2) Copy the value (not the formula) from B34 or any cell from all worksheets in the workbook next to the worksheet names just created from the above Step 1. (all worksheets are set in the same format)
3) Hopefully this macro allows me to handle different number of worksheets, i.e. some file contains 30 worksheets and some 50 or 100, etc.

Ideally, the summary sheet needs to look like (for example):

Client 1 100
Client 2 200
Client 3 500
....

Client 35 20

Thank you very much.

Helena

Hi all,

I have a workbook that tracks pollutant emissions. The workbook could end up having up to 180 worksheets, one for each pollutant and the emission total.
I have VBA to create a new sheet for each pollutant (attached) using a hidden template sheet.

The summary sheet in position 1 needs to list each pollutant worksheet on a new row, and the same 4 cells from each worksheet (id, name, value, value).
I have been typing out the cell references in the summary sheet, and it gets boring [=Sheet!Cell id], [=Sheet!Cell name], etc.

Is there a way for me to copy those cells to the summary sheet when the new sheet is created?

Any help would be appreciated.
Thanks!
Kevin

I need to copy all data from sheet to new sheet based upon value in cell.

Please see attached excel file.

I have to generate different sheets is same workbook for values in Column A.

each new sheet should contain all data from existing sheet & should have name of value in column A.

Number of similar values in Column A can be increase or decrease.

Please can you suggest how can excel macro do this ?

thanks in advance.

Hello, all.

I am having trouble with this and am currently using ctrl-click/copy/paste to accomplish it.

I have a workbook with two sheets. The first sheet looks like this:

Name Score Joe 5.5 Bob -12.4 Jim 72.8 Fred 0.0 John 6.0

My goal is to have a checkbox for each row on this sheet along with a single button that would copy only the two cells from each selected row to another sheet. Additionally, each set of two cells would be pasted into the next available row. Using the above example, for instance, copying the Joe and Jim rows would not leave a blank row between them in the second sheet.

Also, the second sheet already has formulae in each row that are ultimately based on the values in the Score column, therefore, copying the entire row would not work.

Any help would be greatly appreciated.

Hello guys, i have going through your nice website few days now exploring, nothing similar to the problem i am having currently. i am not that expert in excel, but i think this issue can be solved by excel therefore i need this help from experts here, as you see this is my first so i hope things will go fine after this from first try

basically i have excel sheet comes from automated system, but it comes kind of silly, all mixed up and nothing to be able to read it normally to process the data other than copy paste the needed data to new sheet and perform manual calculation and stupid stuff, that can be all done in one or two steps with macros after your help

I am thinking of copy-paste all the raw sheet (automated sheet) and paste it in a template sheet 1 so in sheet 2 we can get the report ready to print !? selecting exact raw and then selecting exact cells in that raw, to be all as clean data other formulas in new sheet!? what you think?

ok, given attached workbook,

- sheet 1 (input) contains the raw data comes from automated system, as you see it is not organized at all, data doesn't come in exact manner or arranged, but columns are always the same stupid arrange! so? columns D, G, I are always static (if containing prospectively Date, time, data) so will get this data to sheet 2 in clean area... even if we forced to make new 'hidden' sheet to filter the main input to -first- remove the blanks...

- in sheet 2 (output) should start to paste in raw 5 and under : while B5 cell : the data from the raw(s) in sheet 1.

I explained it in the example attached. please see what i mean, and celsl with "manual inserting" means it will added later and no need for excel intervention, and some other cells will be calucated manually after that, so it is only needed to reach only to this step using excel.

any help, idea, will be helpful?

note: i repeat: the data doesn't always come in static raw numbers, but colmnus are always correct and always coming in same format, if column D contains date, so always date comes in column D ---- iand if d column contains "date" means we will process it!

thanks a lot in advanced
Mike

I am using the following code to copy data from multiple tabs to a summary tab - it is only supposed to copy the data in the rows if the cells in Column A have data in them, its working, but for some reason it is copying the data in columns Y and Z for four extra rows even though there is no data in column A for those rows.....driving me crazy! I am attaching a sample of the workbook here as well - hoping someone can figure out what I have coded wrong I am working in Office 2007.

Sub SumData()
'
' SummarizeData from Timesheets
'
    Sheets("Jorge Montoya").Select
    Range("A9").Select
    Set r = Range("A9:A27")
    For n = 1 To r.Rows.Count
    myval = ActiveCell.Value
    
    If InStr(myval, "") > 0 Then
        ActiveCell.Range("A1:Z1").Select
        Selection.Copy
    Sheets("Total by Job_Code").Select
    NextRow = Range("A65536").End(xlUp).Row + 1
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    ActiveCell.Offset(1, 0).Select
    End If
    Sheets("Jorge Montoya").Select
    
    ActiveCell.Offset(1, 0).Select
    Next n
    
    Range("A9").Select
    
      
    Sheets("Felipe Estrada").Select
    Range("A9").Select
    Set r = Range("A9:A27")
    For n = 1 To r.Rows.Count
    myval = ActiveCell.Value
    
    If InStr(myval, "") > 0 Then
        ActiveCell.Range("A1:Z1").Select
        Selection.Copy
    Sheets("Total by Job_Code").Select
    NextRow = Range("A65536").End(xlUp).Row + 1
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    ActiveCell.Offset(1, 0).Select
    End If
    Sheets("Felipe Estrada").Select
    
    ActiveCell.Offset(1, 0).Select
    Next n
    
    Range("A9").Select
    
    
    Sheets("Kimberly Garcia").Select
    Range("A9").Select
    Set r = Range("A9:A27")
    For n = 1 To r.Rows.Count
    myval = ActiveCell.Value
    
    If InStr(myval, "") > 0 Then
        ActiveCell.Range("A1:Z1").Select
        Selection.Copy
    Sheets("Total by Job_Code").Select
    NextRow = Range("A65536").End(xlUp).Row + 1
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    ActiveCell.Offset(1, 0).Select
    End If
    Sheets("Kimberly Garcia").Select
    
    ActiveCell.Offset(1, 0).Select
    Next n
    
    Range("A9").Select
    
End Sub
There will end up being about 30 different tabs that it will need to copy from, but the attachment I am including only has 3.

Thank you in advance for any help!

I am new to using VBA so let me explain what I am trying to do...
I want to be able to copy 3 tabs(sheets) in a workbook into 3 new
tabs(sheets) into the same workbook. Also if the date is two days later then
I want to delete values on the orignal sheets. I also want to do this
everytime the sheet is opened.

My current code loops to copy each sheet three times and then deletes data
on the copied sheets but not the original sheets. See below. Thanks for all
help!

Sub Workbook_Open()
' This Macro will copy sheets 1-2-3 to new sheets A-B-C then
' if date is 2 days past clear out the UNLOCKED cells of a sheets (1-3)
'
Dim cell As Range
For Each sh In ActiveWorkbook.Sheets
If Date > sh.Range("A1").Value - 2 Then
Sheets(1).Copy After:=Sheets(Sheets.Count)
Sheets(2).Copy After:=Sheets(Sheets.Count)
Sheets(3).Copy After:=Sheets(Sheets.Count)
For Each cell In Range("A1:H10")
If Not cell.Locked Then cell.ClearContents
Next cell
End If
Next
End Sub

Hi everyone

Thanks in advance for any help I receive.

I have looked through many forums looking for inspiration but have not been able to find any relevant help, I am not a newbie to excel but my experience is limited.

Problem 1

How can I auto copy data from one work sheet to another work sheet based on week ending dates. I have included a workbook to help explain what I am struggling with.

Sheet 1 F:2 has a dropdown menu for week end dates, data is entered into the green cells and the data then is used to populate sheet 2
Sheet 2 then needs to copy the data from H7 – H19 (highlighted in red) to the corresponding week ending column on sheet 3. I would like to add the data to sheet 3 with the help of a “process” button on sheet 1 if at all possible.

Problem 2

I need to add customers to the workbook across all 3 work sheets and to keep the list in alphabetical order, I have a list of customers on the “data” worksheet but I have no idea how to get excel to add a new customer across 3 worksheets. I would like to add the customer from sheet 1 with the help of an “Add Customer” button based on the customer account code from the list on “Data” sheet.

Any advice would be greatly welcomed

Regards

Andy

Hi, I am completely new to anything else than the really basic excel functions, so would love to get some help on the following. I am working daily with a particular worksheet of which in some cases data needs to be transferred to another sheet, as I need that for a different task. I have tried to write it out what I need to have solved, so here goes.

If cell value in column E of the worksheet (Receipts PYMT 2010) equal 70% or 100%, the following needs to happen:
- copy data in cells (same row) of column I and paste it in the next available cell of column B of new worksheet (Invoice Cost Overview 2010);
- copy data in cells (same row) of column A and paste it in the next available cell of column C of new worksheet (Invoice Cost Overview 2010);
- copy data in cells (same row) of column G and paste it in the next available cell of column D of new worksheet (Invoice Cost Overview 2010).

The new worksheet (Invoice Cost Overview 2010) has its first row as header.

Hopefully the above is clear, but do let me know if further information is needed.

Any suggestions/solutions are much appreciated.

Cheers,

Erik

Morning all, hope you're all doing well. New member here

I'll dive straight to the point....

Recently started an Excel unit in an IT/Admin course and having previously sailed through the basic Excel formula and functions (stuff like :IF, SUM, Concatenate, conditional formatting, FV, Subtotals), I've got myself stuck trying to create a nifty tool using Macros in VBA.

I've produced an "Investment Table" using the FV function to allow the user to input things like interest rates, payments, number of payments, years of investment etc, and using these values the FV function throws out the resulting value (future value). Now, on a separate page I'm trying to "break down" this tool into numerous year-by-year tables showing how the value of the user's investment changes year-by-year and payment-by-payment.

For example, Mr Smith invests £100, 4 times per year, for 5 years, with an interest rate of 3.5%. Currently, all he can see is what his value will be worth at the end of the 5 year investment. But using the year-by-year tables he'd see a separate table for each year, and so he can see that in Year 2, Payment 3 his investment will be worth "X-value".

Here's where the macro comes in; in the main "Investment Table" the user inputs the "Years of Investment" (Cell D2). Using this (number) value, and on a separate sheet, a macro needs to produce/replicate the year-by-year table based on the value in cell D2. The aim of this is to save the user having to C&P the table or create their own, X-amount of times. The macro is needed because the tool must be flexible to account for different lengths of investment - and so obviously it's not practical to have to C&P if a user is investing for, say, 25 years.

Put simply, "Table X" must be copy & pasted Y amount of times, where Y = D2
I've tried to find somewhere to start in VBA, having done basic macros in it before, but I'm lost

Hope this makes sense but I will of course clarify if needed.

Thanks


No luck finding an answer? You could always try Google.