Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

copy and paste (visual basic)

i need to copy and paste addresses from on sheet to another depending on the
cell value
EXAMPLE:

IF cell a2 on sheet 1 says "1" then copy the address (cells a1 to a5 on sheet
1) to cells d1 to d5 on sheet 2

if cell A2 on sheet 1 says "54" then copy THAT address (cells a1 to a5 on
sheet 1) to cells d10 to d15 on sheet 2
ANYONES HELP WOULD BE VERY APPRECIATED thanks


Post your answer or comment

comments powered by Disqus
i need to copy and paste addresses from on sheet to another depending on the
cell value
EXAMPLE:

IF cell a2 on sheet 1 says "1" then copy the address (cells a1 to a5 on sheet
1) to cells d1 to d5 on sheet 2

if cell A2 on sheet 1 says "54" then copy THAT address (cells a1 to a5 on
sheet 1) to cells d10 to d15 on sheet 2
ANYONES HELP WOULD BE VERY APPRECIATED thanks

Guys,

Im sure Im doing something basic wrong here but I just cant hack it since last afternoon. Any advise will be most appreciated.

 
'Distribution of the IDs to the relevant Account Manager's 'BD' worksheets - PS
Worksheets("BD-PS").Activate
Worksheets("BD-PS").Range("A56:AZ5000").Select
Selection.Delete
iRowBDdist = 3
iRowBDsheet = 56
Set rLookforBDdist = Worksheets("Aggregate").Cells(iRowBDdist, 27)

Do Until rLookforBDdist = ""
On Error Resume Next
Set rLookforBDdist = Worksheets("Aggregate").Cells(iRowBDdist, 27)
If rLookforBDdist = "PS" Then
Worksheets("Aggregate").Range(Cells(iRowBDdist, 1), Cells(iRowBDdist, 2)).Copy
Worksheets("BD-PS").Range(Cells(iRowBDsheet, 1), Cells(iRowBDsheet, 2)).PasteSpecial Paste:=xlPasteValues
iRowBDsheet = iRowBDsheet + 1
End If
iRowBDdist = iRowBDdist + 1
Loop
The problem with the code is that its not copying and pasting the data as per the lines of code above for copying if a condition is true.

Hi everyone. I need some help on a copy and paste macro. Basically I need the macro to perform a few actions:

1. In whatever cell is selected when the macro is run, enter a new row.

2. Copy the information from the row directly above the new row and paste (values, formulas, formats, etc) into the new row.

3. Return to column P in the new row, i.e if the new row is row 11, then return to P11, for row 12 return to P12, etc.

I have tried recording the macro but because it is hard coded to specific rows, its not working.

I have attached a sample copy of the sheet (had to zip due to the size of the file). Any help will be greatly appreciated. Thanks in advance.

Hi,

I have an excel sheet which has a column (say column H) which has a list of about 20 number in it. This column updates when I refresh a query from a database connection.

So, here's what I would like to do. The refreshed data in H represents the "Current Position" so each time I refresh this I first need to copy the values for the relevant date (i.e the date I last refreshed the query) in to another column which is headed with the relevant date.

Then I can take the difference between H and my historic position and I have the movement over the time period.

Basically, I want to say, in crude terms, Select, Copy and Paste Values of Columns H in to the Column which is headed with the date that the data was last refreshed. If that makes sense...

I hope someone can help me!!

Many thanks

Rob

Sorry, its my 1st time working with Visual Basic i've been looking
though a lot of websites but cant find anything. i have some knowledge
with C++ and its kinda annoying not being able to write such an easy
macro.
what im trying to do is:

1. Find keyword "yyyyy" in column A
2. Find keyword "xxxx" in column A.
3. Highlight every row in between including columns A-F
4. Copy and paste in a new Sheet.
5. Start from where 2. ended and loop 1.-5. again.

thank you for reading this and hopefully help me understand how VB
works in excel. i would greatly appreciate it

ARader1216
Problem: Excel Macros in Visual Basic, Copy and Paste using a worksheet change

I am brand new to VBA and macros.The whole goal of the workbook is simple. I am attempting to create a database for my father's jobs that is very simple for him to enter the information and it will auto-fill the rest of the workbook. I have created a simple user form where he enters the data for the job. The data is copied to a sheet entitled "entry" which is sheet #25. (This will allow me to easily see every entry and fix any problems that he may create.) There are several different columns on the entry sheet. Columns E, G, I, K, and M are for sub names (stands for sub-contractor) . I have a sheet for each "sub" and I would like to have the whole row pasted to the corresponding sub sheet if one of those columns contain the code for that sub. This will be done each time an entry is made and automatically without pushing a command button, etc. (So, essentially if one of those columns contain "Mills" I would like the whole row to be copied and pasted to the next available row in the sheet for the sub mills (entitled 6Mills). I would like to do this for each of the subs. I would like the "trigger" cell contents to be the last name of the sub (as listed on the sheet title, without the number) The subs are all listed right before the entry sheet in the normal gray color. There are 13 total subs and they are the only ones with a number in their sheet title. This will get me started. I already have a very basic code started (listed below) but I can't even get that to work for some reason, so I didn't want to even try to do everything at once. If I can I would like to ask about other codes too once we get this problem fixed. THANKS SOOO MUCH!!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = ("Column5") Then
If Target.Value = "Mills" Then
    Worksheets("Sheet25").TargetRow.Copy
    Worksheets("Sheet24").Rows(1).Select
    Worksheets("Sheet24").Paste
End Sub


Basically, I want all the textboxes data in the userform to be copied and pasted to the selected sheet (except for worksheet MENU and worksheet Lookup list) based on combo box category.


	VB:
	
 UserForm_Activate() 
    txtinvoicedate.Text = Format(Now(), "DD/MM/YYYY") 
     
    Dim ws As Worksheet, wb As Workbook 
    Set wb = ActiveWorkbook 
    For Each ws In wb.Worksheets 
        cboxCategory.AddItem ws.Name 
    Next 
End Sub 
Private Sub cboxCategory_Change() 
    ActiveWorkbook.Sheets(cboxCategory.Value).Activate 
End Sub 
 
Private Sub cmdok_Click() 
    Dim irow As Long 
    Dim ws As Worksheet, wb As Workbook 
    Set wb = ActiveWorkbook 
    For Each ws In wb.Worksheets 
         
         'FIND FIRST EMPTY ROW IN DATABASE
        irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 
         'CHECK FOR AN ASSET CATEGORY
        If Trim(Me.cboxCategory.Value) = "" Then 
            Me.cboxCategory.SetFocus 
            MsgBox "Please enter an asset Category" 
            Exit Sub 
        End If 
         'COPY THE DATA TO THE DATABASE
        ws.Cells(irow, 1).Value = Me.txtAssetNumber 
        ws.Cells(irow, 2).Value = Me.cboxCategory 
        ws.Cells(irow, 3).Value = Me.cboxEstate 
        ws.Cells(irow, 4).Value = Me.txtsupplier 
        ws.Cells(irow, 5).Value = Me.txtDetail 
        ws.Cells(irow, 6).Value = Me.txtinvoice 
        ws.Cells(irow, 7).Value = Me.txtinvoicedate 
        ws.Cells(irow, 8).Value = Me.txtcost 
         'CLEAR THE DATA
        Me.txtAssetNumber = "" 
        Me.cboxCategory = "" 
        Me.cboxEstate = "" 
        Me.txtsupplier = "" 
        Me.txtDetail = "" 
        Me.txtinvoice = "" 
        Me.txtinvoicedate = "" 
        Me.txtcost = "" 
        Me.cboxCategory.SetFocus 
    Next 
End Sub 
 
Private Sub cmdclose_Click() 
    Unload Me 
End Sub[SIZE=4][/SIZE] 

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


Unfortunately, I have a VB error message as follows:-

Run time error “9”
Subscript out of range

The following codes were highlighted

	VB:
	
 cboxCategory_Change() 
    ActiveWorkbook.Sheets(cboxCategory.Value).Activate 
End Sub 

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

My last try was to select MV in the combo box, however, all the information and data was pasted to worksheet Menu and not worksheet MV.

Which area should I correct?
I attach my file for your kind reference.

Hello,

I'm trying to enter a number into a cell and have excel automatically copy and paste a specific column into the next available column.
Basically, I want a worksheet that has x number of columns. I'm trying to do this because every column has title fields that would be common among all columns. After the columns auto generate, I would use it for data entry.

Thanks in advance for any help. Much Appreciated !

Hi

I am having some trouble with a piece of code that is part of a larger macro and was hoping that someone could help or point me in the right direction.

I have a list of data and I need to copy and paste the rows of data in which the values in column B are the same and then delete these rows from the original sheet.

The code that i have come up with so far is as follows:


	VB:
	
 Retreive_Particular_Rep_Invoices() 
     
    Dim RepInvoices As Long 
    Sheets("All Invoices").Activate 
    Worksheets("Current AM Invoices").Range("A2:T65536").ClearContents 
    With ActiveSheet 
        For RepInvoices = 1 To .Range("B1").End(xlDown).Row 
            If .Cells(RepInvoices, 2).Value = .Cells(RepInvoices + 1, 2).Value Then 
                .Cells(RepInvoices, 2).EntireRow.Copy Destination:=Worksheets("Current AM
Invoices").Range("A65536").End(xlUp).Offset(1, 0) 
                 
                 
            End If 
        Next RepInvoices 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I know that this is not correct, but it's the best i have been able to come up with so far and thought that i would try and speed it up with some help that would be greatly appreciated.

I have also attached a spreadsheet with some basic data that i have been playing with as well.

Any help will be grately appreciated.

Kind regards.

Hi all,
Basically I have a consolidation workbook and source files.I would like to convert the data from the source files into a list format in the consolidation workbook.

I have attached a sample of the sheet format of the source files in the attached file, called 'Page 5'. The other 2 sheets are 'Template' and 'Instructions'. The 'Template' sheet is what I imagined would be the list format of the data copied from the 'Page 5' sheet. Instructions is where the lookup table for currency is.

So basically starting from row 8 in 'Template' sheet, I would like to copy and paste from 'Page 5' sheet to 'Template' sheet:
- H2 to A8 & B8
- B2 to C8
- According to the list of currency in 'Instructions' sheet, lookup the currency according to operating unit in C8 and paste to D8
- D5-I5 to E8
- row A8-A23 to column F-N

Hope I'm making sense here. Sheet 'Sample' is an example of the results.

I actually have had a similar problem before, which Derk has helped me here - link: http://www.ozgrid.com/forum/showthread.php?t=50465 (I've decided to start a new thread because this is a more relevant forum).

I have tried to modify the code but I am rather lost as to which part I am supposed to modify.. Note that in this code, the source data is in separate file instead.

I'm hoping someone can help me to modify this code or perhaps some other help. Thank you!


	VB:
	
 add() 
     
    Dim wb As Workbook, f As Worksheet, t As Worksheet, j As Integer, k As Integer, n As Integer 
    Dim mty As String, yr As Integer, d As Date, bu As String, cur As String, sTodo As Variant 
     
    sTodo = Array("Page 5") 'finish adding the names
    Application.ScreenUpdating = False 
    Set t = Workbooks("Example1.xls").Worksheets("Template") 
    i = t.Cells(65536, 2).End(xlUp).Row 
     
    Set wb = Workbooks("Source.xls") 
    For n = LBound(sTodo) To UBound(sTodo) 
        Set f = wb.Worksheets(sTodo(n)) 
         
        mty = wb.Sheets("Page 5").[mth] 
        yr = wb.Sheets("Page 5").[yr] 
         'above gets month and year, but probably better to store the d as date for more versatility
        bu = Trim(f.Range("B2")) 
         
        cur = wb.Sheets("Page 5").[cur] 
        For j = 4 To 10 Step 3 
            If j = 10 Then j = 11 
            For k = 0 To 2 Step 2 
                i = i + 1 
                 
                t.Cells(i, 1) = mty 
                t.Cells(i, 2) = yr 
                t.Cells(i, 3) = bu 
                t.Cells(i, 4) = cur 
                t.Cells(i, 5) = f.Cells(5, j) 
                t.Cells(i, 6) = f.Cells(8, j + k) 
                 
                 
                t.Cells(i, 7) = f.Cells(10, j + k) 
                t.Cells(i, 8) = f.Cells(11, j + k) 
                t.Cells(i, 9) = f.Cells(12, j + k) 
                t.Cells(i, 10) = f.Cells(14, j + k) 
                t.Cells(i, 11) = f.Cells(15, j + k) 
                t.Cells(i, 12) = f.Cells(20, j + k) 
                t.Cells(i, 13) = f.Cells(23, j + k) 
                t.Cells(i, 14) = f.Cells(21, j + k) 
                t.Cells(i, 15) = f.Cells(22, j + k) 
                t.Cells(i, 16) = f.Cells(23, j + k) 
                 
                 
            Next k 
        Next j 
         
    Next n 
    Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Value = 0 
     
     
    Range("A1").Select 
     
    Application.ScreenUpdating = True 
End Sub 

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


-

I did some search before I posted this. But they don’t seem to be what I am looking for.

What I am trying to do here is to compare 2 lists in 2 different files, and when there is a match, then copy and paste the related cells of the matching name. Sorry if this sounds messy, perhaps the sample file I have attached can explain better.

Every month I get a new file in the format of “Data Source” sheet where the list of banks in column A and the figures in column M, AA and AB might change from month to month. For the sake of convenience, I put the source data as a different sheet instead of different file here.

I have an existing report template in the format of “Final report” sheet where basically I copy and paste the relevant cells according to the name of the banks.

I don’t think I can use Vlookup because the cells that I want to extract are not right beside the search criteria. If I’m wrong please correct me.

Anyway, assuming a macro is needed for this, I am wondering if I can create a macro, where it can search the list of banks in column A in “Data Source” sheet based on the list in column A in “Final Report” sheet, then copy the correct cells from column M, AA and AB and then paste them into the correct cells in columns B, E and H in “Final Report” worksheet?

Note that not all the banks in the “Final Report” sheet are in the “Data Source”, so for this example, row 4 for ABN Bank should remain blank after the search because it is not listed in the “Data Source”. The Data Source List might also change over time.

There is also this problem of the bank names from the “Data Source” sheet not being exactly the same as the existing list in “Final Report”. For example in this file, ANZ Bank in the other sheet have all the extra stuff behind, but we know it is the same bank. Can the macro solve this issue?

Thanks a lot and hope to get some help soon

Hi all. I have done lots of searching but not really found an answer to what I am looking for. What I want to do, or know if it is possible, is copy data from a cell to another cell(that bits easy of course) the difficulty comes from the fact that the first cell will be changed but I don't want the second to change.

eg a date is entered in A1, this is then copied to F1. If the date is changed in A1 I need F1 not to change and the new date to copy into G1 and so on. I may also apply this principle to other cells. These date changes occur on a 6 or 12 month cycle. Basically I want to create a history of A1 without having to copy and paste each time and wondered if it could be automated in someway.

Thnx in advance

Keith

Hi,

Experts out there, is there any way of improving the pasting time.
Basically, I open 2 files (1 program and 1 data files), then I will copy and paste the data sheet from the data files to the data sheet of the program files. As my data files is very large, it can took up to 30 minutes this copy and paste process. However, when I do a copy and paste manually, it only take say 5 minutes.

Rgds,
Samuel


	VB:
	
Windows(equityderivdesk_file + ".xls").Activate 
Sheets(equityderivdesk_sht).Select 
Cells.Select 
Selection.Copy 
 
 
Windows(program_file + ".xls").Activate 
Sheets(eqplmatricesTminus2_sht).Select 
Cells.Select 
ActiveSheet.Paste 

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


Hi...

I am attempting some copy and pasting of cells in a large worksheet ( attached ).

Basically on the settings' page I set the year using the slider feature and
the calender is created in the calender sheet and then copied to the various months of the year.

The row height and column width have been checked and are idenitcle across all the pages.

When I run the code I get an error ( RUN TIME ERROR 1004 ) which basically says that this operation requires the cells be identically sized. This line of the VB code is highlited.

Worksheets(strPageName).Range("A5").PasteSpecial Paste:=xlPasteValues

I have checked all the cells and cannot see the problem.

Can anyone help ?

Hi all,

I need help with a macro for copying and pasting of cells. I believe this should not be a problem for the Excel VBA experts, but for someone who can only record macro, I'm really at a loss.

Attached is a sample file, where sheet 'Source' is an example of the sheet from which data are to be copied. The other sheet, sheet 'Final' is an example of the final format that I need. The reason I'm doing this is I'm planning to upload my data into Access and so I need to convert them into a list format.

List of target columns in sheet 'Final' and source cells in sheet 'Source':

Column A: Biz ID - not sure if I really need this, by right it should be listed automatically once I paste the data
Column B: B2 of 'Source'
Column C: B2 of 'Source'
Column D: B1 of 'Source'
Column E: row 6, relevant column
column F: column K
column G: row 5, relevant column
column H: the specific amount

So basically I'm creating an entry for every amount in the table.

Hope to hear from someone.. and thanks for your help!

I'm trying to create a copy and paste loop that will be part of a larger macro. I setup a shell in excel to explain what I'm trying to do. Basically, the "Data" sheet has a stock ticker and date. All I want to do is copy the ticker symbol from column A in the "Data" sheet to cell "D6" in the "fetch" sheet. I want it to then copy the corresponding date from column B in the "data" sheet and paste it into cell "D7" in the "fetch" sheet. I then have a macro that will grab the stock closing price on the date specified and return it to cell "D9" on the "fetch" sheet. I want to copy the price from D9 and paste it into the corresponding cell on the "Data" sheet.

What I cannot quite figure out is how to create a loop that will continue down the "data" sheet until it comes to a blank row. So when the macro finishes, I will have a list of closing prices for each ticker on the specified date. I have a few thousand tickers/dates that I need to return prices for. Thanks for your time.

StockShell.xlsxStockShell.xlsx

Hi all,

I've been battling to work out the correct VBA to do this, but am basically banging my head against a brick wall! So thought it was time to ask for some help...I used to work with VBA in Excel a few years back and would have had no problem, but my current job is in SQL and I just can't switch back to VBA - I'm getting far too confused as I just want to write a case statement!

I've made my example as simple as possible, so it may not make any sense why I would want to do what I'm trying to do, but it would just take far too long to explain!

Using my table below, what I want to do is each time it says "yes" in column B to replace the value in column A in the same row as the "yes" by copying and pasting the formula in A1. So for the below table, I would want it to find the "yes" in B4 and B7, and then copy and paste the formula from A1 to A4 and A7.

a b 1 =average(a2:a4) 2 values change? 3 73 4 54 yes 5 68 6 72 7 67 yes 8 79

Hopefully that makes sense, and I'm sure very simple for an expert let me know if further explanation is required...

Many thanks in advance, any help is greatly appreciated.

**KTree**

I have obtained help and finally come out with this codes, which basically copy and paste from the active cell down basing on a range to define the range to copy. It work as long as the active cell is on row 2 but encounter an error if the active cell is in row 1. I would appreciate your help to amend the code to overcome this problem. The code is displayed below:

Code:
Public Sub copy_formula3()
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim NumRows As Long
    Dim MatchRange  As Range
    Dim CopyFrom As Range
    Dim DataRange As Range
    Dim BlankRange As Range
     
    Set MatchRange = Application.InputBox("Select ALL of match range with the mouse", Type:=8)
    If Not MatchRange Is Nothing Then
         
        With ActiveCell
             
            FirstRow = MatchRange.Row
            LastRow = MatchRange(MatchRange.Count).Row
            NumRows = LastRow - FirstRow
            Set DataRange = .Offset(FirstRow - 1, 0).Resize(NumRows)
            DataRange.ClearContents
            Set BlankRange = Cells(FirstRow - 1, MatchRange.Column).Resize(NumRows).SpecialCells(xlCellTypeBlanks).Offset(0,
.Column - MatchRange.Column)
            .Copy DataRange
            BlankRange.ClearContents
        End With
    End If
     
End Sub


How would I generate a macro which would copy columns A, B, C and U (or a named range) and paste to a new worksheet. Is it possible to schedule it to do it on a particular date?

Have only basic macro knowledge but think I need to go this route as I cannot achieve the above with formulae only. I want a snapshot of the data on a particular date. I could copy and paste manually but would be so efficient if I could do this across the many sheets I have.
Many thanks in advance
jc132568

Hi everyone. I am back again to pick the brains of the great and the good.

I have constructed a roster for my partner who has justed started a new job.

It is made up in the form of a "master" worksheet, Sheet 1, containing the basic roster and Sheet 2 containing the individual formatted details of 15 shifts.

If Shift 101 is in Sheet 2!A1:B5 I have to individually copy and paste that 5 by 2 group of cells to wherever Shift 101 happens to be in Sheet 1, say C2:D6, K7:L11 and G27:H31 for example.

What I would like to do, using the above example, is fill cells C2, K7 and G27 with "101" then have Shift 101's details "automatically" copied and pasted to C2:D6, K7:L11 and G27:H31.

I think I can best describe it as:

IF C2="101" COPY Sheet2!A1:B5 AND PASTE TO Sheet1!C2:D6
IF C2="102" COPY Sheet2!D1:E5 AND PASTE TO Sheet1!C2:D6
IF C2="103" COPY Sheet2!G1:H5 AND PASTE TO Sheet1!C2:D6
IF C2="104" COPY Sheet2!J1:B5 AND PASTE TO Sheet1!C2:D6
IF C2="105" COPY Sheet2!A7:B11 AND PASTE TO Sheet1!C2:D6
IF C2="106" COPY Sheet2!D7:E11 AND PASTE TO Sheet1!C2:D6
IF C2="107" COPY Sheet2!G7:H11 AND PASTE TO Sheet1!C2:D6
IF C2="108" COPY Sheet2!J7:K11 AND PASTE TO Sheet1!C2:D6
IF C2="109" COPY Sheet2!A13:B17 AND PASTE TO Sheet1!C2:D6
IF C2="110" COPY Sheet2!D13:E17 AND PASTE TO Sheet1!C2:D6
IF C2="111" COPY Sheet2!G13:H17 AND PASTE TO Sheet1!C2:D6
IF C2="112" COPY Sheet2!J13:K17 AND PASTE TO Sheet1!C2:D6
IF C2="113" COPY Sheet2!A20:B24 AND PASTE TO Sheet1!C2:D6
IF C2="114" COPY Sheet2!D20:E24 AND PASTE TO Sheet1!C2:D6
IF C2="115" COPY Sheet2!G20:H24 AND PASTE TO Sheet1!C2:D6

IF E2="101" COPY Sheet2!A1:B5 AND PASTE TO Sheet1!E2:F6
IF E2="102" COPY Sheet2!D1:E5 AND PASTE TO Sheet1!E2:F6
IF E2="103" COPY Sheet2!G1:H5 AND PASTE TO Sheet1!E2:F6
IF E2="104" COPY Sheet2!J1:B5 AND PASTE TO Sheet1!E2:F6
IF E2="105" COPY Sheet2!A7:B11 AND PASTE TO Sheet1!E2:F6
IF E2="106" COPY Sheet2!D7:E11 AND PASTE TO Sheet1!E2:F6
IF E2="107" COPY Sheet2!G7:H11 AND PASTE TO Sheet1!E2:F6
IF E2="108" COPY Sheet2!J7:K11 AND PASTE TO Sheet1!E2:F6
IF E2="109" COPY Sheet2!A13:B17 AND PASTE TO Sheet1!E2:F6
IF E2="110" COPY Sheet2!D13:E17 AND PASTE TO Sheet1!E2:F6
IF E2="111" COPY Sheet2!G13:H17 AND PASTE TO Sheet1!E2:F6
IF E2="112" COPY Sheet2!J13:K17 AND PASTE TO Sheet1!E2:F6
IF E2="113" COPY Sheet2!A20:B24 AND PASTE TO Sheet1!E2:F6
IF E2="114" COPY Sheet2!D20:E24 AND PASTE TO Sheet1!E2:F6
IF E2="115" COPY Sheet2!G20:H24 AND PASTE TO Sheet1!E2:F6

etc down to

IF O27="115" COPY Sheet2!G20:H24 AND PASTE TO Sheet1!O27:P31

I am guessing VBA would be involved, but not sure how to approach it, or even if it needs to be as long winded as above.

If that needs any clarification, please ask & I'll try my best.

I know what I want to achieve - just not sure if I have explained it clearly enough.

Thanks in advance!

Phil

By the way... I am using Excel 2007 if that makes any difference.

hi,
i have a for loop that does a routine for 100 times...
basically, at each step, it copies A1:B5 and pastes it in columns C and D

at step 1, A1:B5 would be copied and pasted to C1:D5
at step 2, A1:B5 will be copies and has to be pasted into C6:D10

how can i make the code know where to start the next paste? (ie. ctrl. end + 1 more cell)

thanks

Good day. I have an interesting problem here that I don't seem to understand. I know how to fix the problem, but not why. I am using Excel 2002 and Win XP PRO.

I have a workbook that contains 33 worksheets. One worksheet for each day of the month, one for monthly totals and one for tracking employee over/under values as a result of daily cash register resolution.

The problem does not relate to the over/under resolution, rather to simply moving the employee names. On the worksheet titled EOU (Employee Over/Under), the columns in question are as follows;

A - contains the name of each employee working during that month. This column is filled in at the start of each month.

B - contains the sum of all over/under differences as calculated elsewhere on this worksheet.

C - contains the Shift Count showing how many shifts the employee worked during the month.

D - contains the Average of all over/under amounts for the employee for the month.

E - contains the number of shifts that the employee was Over for the month.

F - contains the number of shifts that the employee was Under for the month.

G - empty for visual display separation.

H through AL5 - contains the actual over/under value for each employee for each day of the month.

All of the above columns have formulas that relate to each Row exclusively with no reference to any other Row.

The interaction between the columns above is;

Cell A3 contains the first employee and cell A22 contains the last possible employee to be tracked. In the past I was able to continue the list of employees from the previous month by simply copying the entire list. Due to space constraints on the form, I find it necessary now to delete employees who are no longer working.

Cell B5 contains the following formula and does not appear to be part of the problem.
=IF(C5="","",IF(C5=0,0,SUM(H5:AL5)))

Cell C5 contains the following formula and is the problem.
=IF(A5="","",COUNT(H5:AL5))

Cell D5 contains the following formula and does not appear to be part of the problem.
=IF(A5="","",IF(C5=0,0,AVERAGE(H5:AL5)))

Cell E5 contains the following formula and does not appear to be part of the problem.
=IF(A5="","",COUNTIF(H5:AL5,">0"))

Cell F5 contains the following formula and does not appear to be part of the problem.
=IF(A5="","",COUNTIF(H5:AL5,"

Hi,

Bear with me, first time poster, not very Excel-minded!

I have a macro (which I didn't write) that extracts information from
several Excel worksheets and complies it into one large summary
worksheet. (Basically the same thing as copying and pasting all the
data by hand.)

The problem is that the macro should just copy and paste the data
directly into the summary spreadsheet. Instead it seems to be pasting
links or references rather that just data.

So, instead of generating a summary worksheet with numbers/raw data, it
generates the worksheet filled with REF! errors. Is there a way to
change preferences to tell Excel to always paste values instead of
links?

Also, this only seems to be a problem with Excel 2000. It seems to run
fine with Excel 98.
Thanks!

Meg

---
Message posted from http://www.ExcelForum.com/

Good Afternoon

I have a graph, that I would like to be able to copy from one sheet and paste into another, is there away of doing this without dropping the data i.e. every time I copy and paste the graph clears, I don't want to copy the spreadsheet data as well as the graph is purely for a visual

Thank you for your help

Thank you for your help MDBCT, that worked great


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