Copy/Paste Range (Macro)

[sweater_vests_rock]

The following code is coming up with a 1004 error (Application-defined or Object defined error). Why?

counter_rowX is a Long
worksheet_name(X) is a String array containing the names of the worksheets
column_name(X) is a String containing the name of the column used to find matches.
column_count(X) is an Integer array containing the name of the last column in the range in worksheet X
output_sheet_name is a String
row_count(X) is a Long array containing the name of the last row in worksheet X.

I've checked to make sure all of the values showing up in these variables are appropriate and logical.

Do
    counter_row1 = counter_row1 + 1
    counter_row2 = 0
    Do
        counter_row2 = counter_row2 + 1
        If Worksheets(worksheet_name(1)).Cells(counter_row1, column_name(1)) = Worksheets(worksheet_name(2)).Cells(counter_row2, column_name(2)) Then
            counter_row3 = counter_row3 + 1
            Worksheets(worksheet_name(1)).Range(Cells(counter_row1, 1), Cells(counter_row1, column_count(1))).Copy (Worksheets(output_sheet_name).Range(Cells(counter_row3, 1), Cells(counter_row3, column_count(1))))
          
            Worksheets(worksheet_name(2)).Range(Cells(counter_row2, 1), Cells(counter_row2, column_count(2))).Copy (Worksheets(output_sheet_name).Range(Cells(counter_row3, column_count(1) + 1), Cells(counter_row3, (column_count(1) + 1) + column_count(1))))
         
        End If
    Loop While counter_row2 < row_count(2)
Loop While counter_row1 < row_count(1)

 

[Tazguy37]

What is column_count()?

 

[sweater_vests_rock]

tazguy.

sorry, i got column_name(X) and column_count(X) confused. they are both arrays, but

column_name(X) is a String array containing the name of the column with ID data.
column_count(X) is an Integer array containing the last column with data

also, i guess i could say why i'm doing this. i'm looking to take an ID on sheet A, match it to the same ID on sheet B, and then copy a range containing this ID and some other information from sheet A to sheet C and from sheet B to sheet C.

 

[sweater_vests_rock]

another question.

Would this structure be faster than my loops?

1) Pick an ID off of Sheet A
2) .Find that ID on Sheet B
3) Create the range I want on Sheet A
4) Create the range I want on Sheet B
5) Paste these ranges onto Sheet C

Thanks for all of your help, to everyone. I teach myself VB as necessary, and I think I know just enough to make LOTS of mistakes...

 

[Tazguy37]

I guess my big question is: Why use all those arrays? All the data you're looking at is still on the sheet, just find it when you need it. There's no reason (that I can think of) to store all of that information in an array, when it's there on the sheet, waiting for you to find and use it. Make sense?

 

[Cbrine]

Sweater_Vests,

I'm not sure what you are trying to do, and the code you put together is rather difficult to read. I thought a few pointers might be in order.

Loop Through a range

Sub LoopThru()
Dim R as range

For each R in Range("A1:A52")
  Msgbox r.value 
Next
End sub

Looping through a dynamic Range in Column A.(From A1 to Last Filled cell in Column A.

Sub LoopThru()
Dim R as range

For each R in Range("A1",Range("A65535").end(xlup))
  Msgbox r.value 
Next
End sub

Assigning references for worksheets/workbooks. You will notice that you get a context sensitive drop down on the wb. , ws1. and ws2. that lists of methods and functions available to that object.

Sub AssignRef()
Dim wb as workbook,ws1 as worksheet, ws2 as worksheet
set wb = Activeworkbook
Set ws1 = Activesheet
Set ws2 = Sheets("Sheet2")

Msgbox wb.name & " " & ws1.name & " " & ws2.name
End sub

HTH
Cal

PS-If you can maybe explain what you are trying to accomplish, we should be able to give you some help.

 

[sweater_vests_rock]

tazguy & cbrine.

thanks for the general help. cbrine, i'm confused right now, but i'll pick it up. also, see my revisionary second post for a better description (or just read on)

tazguy, i'm not sure what you mean by "why the arrays?" i guess i don't know how to do what i want any other way.

since i've spent 6 hours on this, i probably should have just done this manually. but anyways.

Background:
i have 2 spreadsheets with sales data. the sales data is broken down (for the most part) by UPC codes; one UPC code per row. one spreadsheet contains volume data (how many units in a period), while another contains distribution (in how many stores in a period); one period per column.

Problem:
i need to find sales per point of distribution. This requires taking a UPC code from the volume sheet, matching it with a UPC code on the distribution sheet, and doing some math.

My ***** Session :
i didn't set up the sheets as they are now, so it is a big mess and ugly. this is the whole reason why i need to match this stuff up -- i am trying to create a clean worksheet that has the information i need readily aligned for the math to be cranked out.