Code Is Selecting The Wrong Sheet

May 25, 2007

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long
Dim newRow As Long
Set ws1 = Sheets("SUMMARY")
Set ws2 = Sheets("WIP")

Application.ScreenUpdating = False
With ws2
Range("A2:C2").Select
Range(Selection, Selection.End(xlDown)).Select ' Clear
End With

x = 10
lastrow = ws1.Range("C5").End(xlUp)
Do
The code is run from ws1. I put in the .select for now because I don't want to loose my info on ws1.

I trying to clear the range A2:C2 all the way down to where info is before doing the Do Loop which will repopulate the same range from ws1.

If the rest of the Do Loop code is needed, let me know.

View 9 Replies


ADVERTISEMENT

Copy/paste-button - Wrong Reference Code In Sheet 2

Jul 8, 2009

i have a button that copies data from cell
A5:K5, and pastes it in Sheet2 of my workbook.

The data is ordered like this

------
------
------ button1

------
------
------ button2

------
------
------ buttonX

I have like 40 buttons attached to 4 rows each. And in that group of rows, i have some cells with an IF= formula, and some minor coding.
The problem is: If i press button 3,4,17,29 before i press every other button before that. I get an reference code fail at the cell.

If i press the buttons in order. Like 1,2,3,4,5,6,7 - it appears smoothly.

More info: The button copies data from for example A5:K5 and pastes it into the next blank row of sheet2. But i want to be able to press button 2 before i press button1, without getting an error in my =IF cell.

If i press the 40 buttons in a row, and then starts to mix up the button pressing. It appears smoothly.

View 9 Replies View Related

Code Is Populating A Cell With Wrong Value?

Jun 27, 2013

Code:
With wshfma
lr1 = 0
.Range("K2:N15").ClearContents 'early late staff rowsource
.Range("C2") = Format(hwmin1, "h:mmA/P") 'text!
With .Range("D2")
.Value = (.Range("C2"))
.NumberFormat = "general"
End With

Cell C2 of worksheet 'fma' is populated by a value linked from a textbox in a userform. It is a time value in text format (eg "8:30P").

This snippit of code is to populate cell D2 of worksheet 'FMA' with the value in C2 (8:30P), and apply the general number format to it. (I know redundant ... but this is just testing).

What is happening though, is cell D2 is actually being populated with the contents of another cell ... F3 to be exact.

View 3 Replies View Related

Code Changing Color Property Of Wrong Cells?

Apr 14, 2013

I have this code:

Code:
If .Range("K" & t) = "AUTO" Then
.Range("K" & t) = Format(Application.VLookup(.Range("A" & t), rcore, 54, False), "h:mmA/P")
With .Range("K" & t)[code]....

It is intended to change the font colour to white when the cell = "AUTO". It does, but it also changes the contents of the cell (like NA, and NR) to white when they aren't equal to AUTO.

View 2 Replies View Related

VB Code Bringing New Data In Onto The Wrong Blank Rows

Jan 19, 2009

Im having some problems with the following VB code

It does import all my data but if I have 4 files for it to import to the 3 different tabs the following happens:
on tab A the first 3 files all come in fine but the 4th file comes in on row 103 instead of the next blank row which would be 22
on tab B the first 2 files come in fine but the 3rd and 4th come in starting on row 94 instead of first blank row which would be 13
on tab C the first 1 file comes in fine but the 2nd 3rd and 4th files come in starting on row 88 instead of 7.

I'd be more then happy to email my spreadsheet to someone as its quite hard to explain whats going on!
I know its probabally a daft mistake I've made in the code if someone could just point out what I've done wrong so I can try to resolve it.

Private Sub ImportData_Click()

'Imports and processes the raw FILE data into the Workbook
'Define a as the current FILE file reference cell,
Dim a As Range
'Define minTime and maxTime as the min and max task periodicity should be processed.
Dim minTime As Long, maxTime As Long
'Define a store for the address of the period totals...

View 9 Replies View Related

Displays Wrong Sheet After Running A Macro?

Mar 30, 2014

[Code] ....

But it doesn't show the correct sheet after running it.

I need it to show the sheet called "Invoice with code".

View 3 Replies View Related

Macro Deleting Values On Wrong Sheet

Jun 6, 2009

The below code is supposed to delete data on the last sheet of the workbook HeadsetOutTimeMay.xls however for some reason it is deleting the data on HeadsetOutTime.xls What am I doing wrong?

View 8 Replies View Related

Print Macro Printing Wrong Sheet

Jun 22, 2006

I have this macro

For I = 6 To 18
Sheets(I).Visible = xlSheetVisible
PrintSubs Sheets(I)
Sheets(I).Visible = xlSheetVeryHidden
Next
End Sub

When it starts printing it starts off with my "JOBCOM" sheet which is sheet20. why it would start printing with that one. That sheet shouldn't even print at all. It's only supposed to print sheets6 to sheet18.

View 9 Replies View Related

Accidently Executing A Macro On The Wrong Sheet Within A Workbook

Sep 3, 2009

When running a macro is there a way you can execute code on another sheet within a workbook without being in the original sheet?

I seem to have a problem if I accidentally run a macro not on the original sheet whereby the code is executed and fills over data not in the desired sheet and the previous cells information is lost and the only way to fix the problem is to close the workbook and re-open a saved copy as you can't undo?

View 9 Replies View Related

Importing CSV File To Hidden Sheet Without Selecting Sheet

Mar 14, 2014

I have this code that first selects a sheet (data) and then imports a csv-fil to the data sheet.

I want to hide the data sheet and the the code doesn't work.

How do I change the import code so that I do not select the sheet?

[Code] ......

I believe it should be something like this: With Sheets("DATA").QueryTables. Add..... but I am not aware of the syntax.

View 1 Replies View Related

CODE For Selecting Second Term

Oct 27, 2009

I am writing a equation in if statement...Is there a procedure to select the the second and third letter from a sentence and write a condition based on that...

I mean, say if I had 1XY in cell A1 and I want to write a if condition based on the XY from 1XY term, how can i do that?

View 9 Replies View Related

Selecting A Range Using VBA Code

May 13, 2006

I have attached a spreadsheet...I'm trying to select Range A1:N16 using VBA code...

Range(activecell,activecell.end(xltoright)).select
Range(selection,selection.end(xldown)).select

When I use this code is only gets A1:N6...: is there anyway to get it to select the range all the way to the end of the data. I tried CurrentRegion and some other stuff but can't get it to work.: The range may go upto line 500 or 3 I just never know.

View 4 Replies View Related

Code For Selecting Sheets From Combo Box

Aug 25, 2008

Use a drop down menu (Combo box for example) to look at all the sheet names (these will be names of people, sheets added all the time) be able to select a name which when selected takes me to that sheet. So the amount of sheets will increase so everything will need to be dynamic.

I dont want to see all the list behind the drop down so that why i was thinking of a combo box.

View 8 Replies View Related

Code For Selecting Search Button

Jan 5, 2007

What code to use to tell VB to select the "search" button on webpage "http://66.179.23.21/DP1/Metroplex/FairfaxCounty/permit/WIZ_INSPECTION.asp"?

View 9 Replies View Related

VBA Code For Selecting Specific Cells Within Worksheet?

Aug 28, 2013

know the VBA script to select only specific cells within a worksheet. To be more precise, on sheet 1 - A2, A6 and A10 cells are filled with red color. I want only those cells which are highlighted in red to be selected, copied and pasted to sheet2.

View 3 Replies View Related

Error Code Selecting Block Of Data From Worksheet?

May 3, 2014

I am trying to select a block of data from a remote worksheet but I keep on getting the following error

Run time error '1004':

Application-defined or object-defined error.

I have attached the file and I am working on worksheet 21 "PT Monthly Report 1"

View 7 Replies View Related

Code To Prevent Editing & Selecting Of Range Worksheet

Sep 3, 2008

I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'. Loop Through Rows & Copy Each Row To New Workbook

For lLoop = 2 To 251 'first row of data to last row.
If ws1.Cells(lLoop, 4).Value = "x" Then '4 = Column D
ws1.Activate
ws1.Range("e" & lLoop & ":g" & lLoop).Copy
ws1.Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Rng1.Copy
Workbooks.Add
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("A1").Select
ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls"
ActiveWorkbook.Close

Else
End If

Next lLoop

how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.

View 5 Replies View Related

Selecting A1 On Each Sheet?

Mar 28, 2012

I have 5 sheets in a workbook. After a code has run I want each sheet to be in A1 when they click on that tab (as there could be 50,000 rows on each), but when I try recording it it doesn't do it properly (Sometimes the A1 cell is active but the view is somewhere down the bottom of the sheet).

View 2 Replies View Related

Selecting And Paste Into New Sheet?

Feb 4, 2014

Base on this thread as reference:

[URL]

I have the same issue, but my data are all in 1 cell instead of separate cells.

The result on my sample Sheet 2, I use RIGHT, MID to pull the data, so I wonder if I can run a macro to have all the data in 1 click.

The header in Sheet 2 is to show you the order of the data to pull, having them or not is not the main thing. I need the data in the same order, that is more important.

View 3 Replies View Related

Selecting Last Sheet And Copying From It?

Feb 11, 2013

I am trying to basically select the last sheet in a workbook, copy the contents, and paste into a new sheet which gets its name from an inputbox. I keep getting a select class error (1004).

Every month i end up adding a sheet and copying everything from the previous month, and then updating a few details which then updates my graphs. Trying to automate the process.

Here is what i have so far.

Code:
Sub Create_Report_Data()
Dim month As String
Dim wcount As Integer

[Code]....

View 2 Replies View Related

Selecting Data On Another Sheet

Jul 25, 2008

When I am trying to select a data range on another sheet within the same document, I get an error. My code looks like this:

activeworkbook.sheets("mySheet").Range("A1:D3").Select

View 9 Replies View Related

Selecting ALL Rectangles On A Sheet

Aug 16, 2006

how I could go about selecting all of the "rectangles" on the active sheet. I think I have around 50 on one sheet so it's a real pain in the neck some times.

View 5 Replies View Related

Code That Automatically Run After Selecting Item In Data Validation Dropdown List In Column C

Oct 12, 2011

I have a dropdown list in C24:C50 (=CategoryList) with data validation and a sub list in D24:D50 (=ItemList) with data validation. I am looking for a way to have code automatically run after selecting an item in the data validation dropdown list in column C.

Example; I click on C24 and make a selection. I what it to trigger code that would move me to D24 and open up the data validation list in D24. After the selection in D24 I would like it to move me back and down 1 row to C25. I have not found anything directly related to this but I have found that code can be run after a selection in a valadition list.

View 2 Replies View Related

Selecting Ranges On Different Sheet Using Union

Nov 7, 2013

I am writing a UDF to find the average of two ranges, which start at the StartDate and then go back a certain number of days, that are on another sheet. When I use Union to try and unite my two ranges the resulting range just equals the first range.

VB:
Public Function ModifiedAverage(StartDate As Variant, SheetTenor As String, History As Double)
Dim DateRow, NbCols As Double
DateRow = Application.WorksheetFunction.Match(StartDate, Sheets(SheetTenor).Range("A:A"), 0)
NbCols = Application.WorksheetFunction.CountA(Sheets(SheetTenor).Range("6:6"))

[Code] ....

View 1 Replies View Related

By Selecting From 2 Comboboxes, Copy From One Sheet Into Another One

Nov 9, 2009

I have 2 comboboxes in the complett worksheets. By selecting some values in this comboboxes, I need some code that will copy automatically some data from worksheets a and b, to worksheet export.

For example if I select "a" and "A26" in the comboboxes, I need range E26:B26( 4 col to the right of A26) from "a" worksheet, to be copied with Paste Special /Values/ Transpose into "export" worksheet in range A1:A50. Also I need in range A51 from worksheet "export" the value "A26" selected in the second combobox. Also in range A52 from worksheet "export" the value from range H40 from "complett" worksheet.

View 8 Replies View Related

Refresh Web Query Without Selecting Sheet

Apr 27, 2014

I am trying to avoid selecting sheets in my current project. I have a querytable in A1. I can get it to work if i selects sheet and range.

Can it be done using with as below

Code:
With Worksheets("meeting to look at")
End With

View 1 Replies View Related

Selecting Sheet Based On Cell Data In VBA?

Nov 30, 2011

I have a workbook with many tabs and I am writing a macro that works on the active sheet but needs to pick up data from another tab, which is labelled on the active sheet.

As I want to write this macro only once but different tabs have different tabs they look to, VBA code that slects the sheet named in a fixed cell on the active sheet.

This is the code that currently looks up to the tab labelled 'Cork, FA'. However, if this tab name is stored in cell AA2, I would like the code to choose the tab based on the information in AA2?

ActiveSheet.Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNTA('Cork, FA'!C)"
CountClients = Selection
Selection.ClearContents

View 1 Replies View Related

Excel 2003 :: Selecting Only Certain Items From One Sheet To Another?

Mar 26, 2012

Using Excel 2003

We have a spreadsheet set up showing a list of sales invoices, each invoice has the customer name and a customer reference assigned to it i.e B1, B2 etc

We want to set up another sheet that takes the sales invoice information but only picks up that which relates to a specific customer, i.e want a list of the invoices relating to B1

This will be done with a view to creating statements for each individual customer reference number which will just take info from these sheets B1,B2 etc

View 1 Replies View Related

From A Table Selecting One Value That Returns Another Value In Separate Sheet

Nov 4, 2013

I have a data input sheet on a spreadsheet with a pick list where we can select an option, but I need another 'flat file friendly' option to appear on a second sheet where the data will be pulled from. For example

If on the front sheet Flat Roof Insulation is selected, it needs to return FRI into the other sheet, there are 45 options in the list.

View 1 Replies View Related

Opening A Workbook And Selecting A Specific Sheet Within

Dec 19, 2006

is there any way to open a workbook and select a sheet within the book without having the entire name?
I've got a unique workbook and worksheet identifier but don't know how to use wildcards to get my macro to open the right workbook.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved