Copy data from one sheet to another in the same workbook

[krishnak]

I am a beginner in VBA programing and I need help. I am compiling a program in which the contents of one sheet are to be copied, row by row, to other sheets depending upon specific coniditions e.g if Cells(i,2) is equal to the sheet named. I am attaching the code.
I am not able to identify my worksheets in the format
Workbooks(1).Worksheets("Master"). The program works only if the code is Workbooks(1).Worksheets(Sheet1), which is the present location of the Master sheet. If I move the sheet to another location in the workbook, the code now takes note of the new location and does not work. When I am assigning the reference to the Sheet in VBA code, I am only shown the prompt "_Default(Index)". No option to enter the given name of the sheet.


Sub CopyDataToAnotherSheet()
'
Dim mySheet As Worksheet
Dim i, cntRowsSource, cntRowsTarget As Integer

Set mySheet = Workbooks(1).Worksheets(Sheet1)
'Count the number of rows in the Master sheet i.e Sheet1
cntRowsSource = Cells.Find("*", _
SearchOrder:=xlByRows, LookIn:=xlValues, _
SearchDirection:=xlPrevious).EntireRow.Row
For i = 2 To cntRowsSource
For j = 1 To 12 ' Number of columns are 12.
If (ActiveSheet.Cells(i, 2).Value = "USA") Then

Workbooks.Worksheets(Sheet4).Cells(i, j).Value = (ActiveSheet.Cells(i, j).Value)
End If
Next

Next
MsgBox (cntRowsSource)
'
End Sub

Please assist. Thanking in advance

[lucas]

What does Workbooks(1) mean?

I think your looking for something more like this:
Set mysheet = ActiveWorkbook.Sheets("Sheet1")

[lucas]

Guess you should probably use this instead unless your using it in an addin:
Set mysheet = ThisWorkbook.Sheets("Sheet1")

[lucas]

Try it now with option explicit at the top of the module...copies the row to the same position on sheet 4 from sheet 1 if column B has USA in it....right?
Option Explicit
Sub CopyDataToAnotherSheet()
'
Dim mySheet As Worksheet
Dim i, j, cntRowsSource, cntRowsTarget As Integer
Set mySheet = ThisWorkbook.Sheets("Sheet1")
'Count the number of rows in the Master sheet i.e Sheet1
cntRowsSource = Cells.Find("*", _
SearchOrder:=xlByRows, LookIn:=xlValues, _
SearchDirection:=xlPrevious).EntireRow.Row
For i = 2 To cntRowsSource
For j = 1 To 12 ' Number of columns are 12.
If (ActiveSheet.Cells(i, 2).Value = "USA") Then
ThisWorkbook.Sheets("Sheet4").Cells(i, j).Value = (ActiveSheet.Cells(i, j).Value)
End If
Next
Next
MsgBox (cntRowsSource)
'
End Sub

[krishnak]

Thanks lucas - but the code does not work. I am still getting the error "Subscript out of range" at the code "Set mySheet = ThisWorkbook.Sheets("Sheet1")". Obviously it is not recognizing the "Sheet1".

[lucas]

Means you don't have a sheet tab named '"Sheet1" most likely.......If it's in parenthisis it reflects the actual name of the tab...not the code name...etc.

[lucas]

Brother Johnske has a terrific article dealing with referencing worksheets:
http://vbaexpress.com/forum/showthread.php?t=9771

[krishnak]

Yes, I named all my sheets with specific names so as to recognize them at whatever position they are in the workbook. In fact the Sheet1 is named "Master" and Sheet4 is "USA".

[lucas]

If you make those changes in the code it will work....let me know please

[lucas]

If you wish to look in the vbe and check the code name for the sheet...for instance if sheet1's tab name is Master you can use this:
Set mySheet = ThisWorkbook.Sheets(1)

Likewise if USA'S code name is sheet4...change this line

ThisWorkbook.Sheets(4).Cells(i, j).Value = (ActiveSheet.Cells(i, j).Value)

[krishnak]

Hi lucas, I think I got it. The following code works.


Option Explicit
Sub CopyDataToAnotherSheet()
'
Dim mySheet As Worksheet
Dim i, j, cntRowsSource, cntRowsTarget As Integer
'Set mySheet = ThisWorkbook.Sheets(Master)
Master.Activate
'Count the number of rows in the Master sheet i.e Sheet1
cntRowsSource = Cells.Find("*", _
SearchOrder:=xlByRows, LookIn:=xlValues, _
SearchDirection:=xlPrevious).EntireRow.Row
For i = 2 To cntRowsSource
For j = 1 To 12 ' Number of columns are 12.
If (ActiveSheet.Cells(i, 2).Value = "USA") Then
USA.Cells(i, j).Value = (Master.Cells(i, j).Value)
End If
Next
Next
MsgBox (cntRowsSource)
'
End Sub

Thanks for the tips. How can I attach a code to the message without pasting it in the main body?

[jdubya]

How can I attach a code to the message without pasting it in the main body?
Insert your code within the VBA tags. You should see an icon that says VBA when creating or replying to a message.

[lucas]

You can attach your code in the green background for easier reading by selecting your code while posting and hitting the green vba button on the editor.....it will be encased in bracketed tags and will look correct when submited.

You aren't serious about your code working are you?
Try this and I have attached a workbook with the code working......
Option Explicit
Sub CopyDataToAnotherSheet()
'
Dim mySheet As Worksheet
Dim i, j, cntRowsSource, cntRowsTarget As Integer
Set mySheet = ThisWorkbook.Sheets("Master")
'Count the number of rows in the Master sheet i.e Sheet1
cntRowsSource = Cells.Find("*", _
SearchOrder:=xlByRows, LookIn:=xlValues, _
SearchDirection:=xlPrevious).EntireRow.Row
For i = 2 To cntRowsSource
For j = 1 To 12 ' Number of columns are 12.
If (ActiveSheet.Cells(i, 2).Value = "USA") Then
ThisWorkbook.Sheets("USA").Cells(i, j).Value = (ActiveSheet.Cells(i, j).Value)
End If
Next
Next
MsgBox (cntRowsSource)
'
End Sub

[lucas]

krishnak,
If you wish to attach a file to your post you must hit the "Post Reply" button at the bottom left of the last post....in the new window scroll down till you find a button marked "manage attachments" that will open a dialog window that will allow you to attach your file to the post.

[krishnak]

Thanks for the information.