I want to insert different values in each worksheet i.e every worksheet should 've different values.I tried the below code but i m not able to set the
range for a particular worksheet
For ex-For Sheet1 iwant to set the range as "A1:A4" with values as 10,20,30,40
But don't know how to set this for different cells.
Similarly For Sheet2 iwant to set the range as "b1:b4" with values as 100,200,300,400
If a workbook contains 10 such sheets how do i set the code
Attached below is my code
Public Sub my1()
Dim WS_Count As Integer
Dim cs As String
cs = ActiveSheet.Name
Dim r As Range
Dim ws As Worksheet
Dim y As Integer
y = 1
Dim I As Integer
For Each ws In ThisWorkbook.Worksheets
ws.Activate
If cs = "Sheet1" Then
Set r = ActiveSheet.Range("A1:A4")
r.Cells.Value = "10:20:30:40"
Else
Set r = ActiveSheet.Range("c1:c4")
r.Cells.Value = "100:200:300:400"
End If
Next ws
I m not able to get the proper result with the above code...Can anyone Plz guide me...
- Public Sub my1()
-
Dim r As Range
-
Dim ws As Worksheet
-
Dim intRowCtr As Integer
-
-
For Each ws In ThisWorkbook.Worksheets
-
ws.Activate
-
If ws.Name = "Sheet1" Then
-
For intRowCtr = 1 To 4
-
Worksheets("Sheet1").Cells(intRowCtr, 1).Value = 10 * intRowCtr
-
Next
-
Else
-
For intRowCtr = 1 To 4
-
Worksheets(ws.Name).Cells(intRowCtr, 3).Value = 100 * intRowCtr
-
Next
-
End If
-
Next ws
-
End Sub
3 6399 - Public Sub my1()
-
Dim r As Range
-
Dim ws As Worksheet
-
Dim intRowCtr As Integer
-
-
For Each ws In ThisWorkbook.Worksheets
-
ws.Activate
-
If ws.Name = "Sheet1" Then
-
For intRowCtr = 1 To 4
-
Worksheets("Sheet1").Cells(intRowCtr, 1).Value = 10 * intRowCtr
-
Next
-
Else
-
For intRowCtr = 1 To 4
-
Worksheets(ws.Name).Cells(intRowCtr, 3).Value = 100 * intRowCtr
-
Next
-
End If
-
Next ws
-
End Sub
Thanks alot..Working...But just 1 more query here...
if i want to set the range differently for every worksheet ..i mean
Sheet1 range(A1:A5) with values as 12,56,34----so on
Sheet2 range(B5:B10) with values as 22,16,44----so on
i.e choosing range randomly & also the values may differ...
For value assigning can we've an array defined..
Can u Plz guide me...
I'm sure there is an easier Method, but it simply elludes me at the moment. - Dim r As Range
-
Dim ws As Worksheet
-
Dim intRowCtr As Integer
-
Dim n As Integer
-
Dim varRet As Variant
-
-
For Each ws In ThisWorkbook.Worksheets
-
ws.Activate
-
If ws.Name = "Sheet1" Then
-
Set r = ActiveSheet.Range("A1:A4")
-
For n = 1 To r.Rows.Count
-
varRet = Split("10,20,30,40", ",")
-
r.Cells(n, 1).Value = varRet(n - 1)
-
Next n
-
Else
-
Set r = ActiveSheet.Range("A1:A10")
-
For n = 1 To r.Rows.Count
-
varRet = Split("10,20,30,40,50,60,70,80,90,100", ",")
-
r.Cells(n, 1).Value = varRet(n - 1)
-
Next n
-
End If
-
Next ws
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jack |
last post by:
I have a text file of data in a file (add2db.txt) where the entries are
already entered on separate lines in the following form:
INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great...
|
by: serge |
last post by:
I have a scenario where two tables are in a One-to-Many relationship
and I need to move the data from the Many table to the One table so
that it becomes a One-to-One relationship.
I need to...
|
by: 73blazer |
last post by:
Hello,
I'm looking for a way to make some of my insert templates more readable
by placing comments in between the values. I cannot seem to find a way
to do this with DB2, is there a way? I'm...
|
by: Mamatha |
last post by:
Hi
When i clicked a button, i want to insert data from
listview in VB.NET to Excel sheet.
If you know the solution either above or below is ok for
me.
I know how to insert from a textfile,but...
|
by: OdAwG |
last post by:
Hello All,
Given the following code from several differenet sources within this
newsgroup, we were able to come up with a way to connect to a database and
retrieve data from it. The code is...
|
by: rn5a |
last post by:
During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am...
|
by: filip1150 |
last post by:
I'm trying to find if there is any performance diference between
explicitly using a sequence in the insert statement to generate values
for a column and doing this in an insert trigger.
I...
|
by: silentbuddha |
last post by:
Hi, I am currently having some difficulties with this sunroutine that I created. My 2 dimensional array is empty.
- This code is within my UserForm1
- this sub is suppose to first select the...
|
by: Henry J. |
last post by:
My app needs to insert thousand value rows into a mostly empty table
(data are read from a file). I can either use inserts, or use merge.
The advantage of using merge is that in the few cases...
|
by: premMS143 |
last post by:
Hi all,
Using VB,
How to insert the required number of rows in all worksheets/selected sheets present in a workbook at a time.
Say an example,
I'm having an Excel file containing 15...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |