Copy/Paste Range (Macro)

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
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.

Code:
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)
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
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

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237

ADVERTISEMENT

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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

Code:
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.

Code:
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

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
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.
 

Forum statistics

Threads
1,147,681
Messages
5,742,587
Members
423,739
Latest member
plkdti

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top