VBA - copy same range of cells from every worksheet in workbook into list in another worksheet

[>>SleeB<<]

I have a workbook with a number of worksheets (the number may change over time). Within each worksheet I want to copy the range A17:N154 and paste it into one master worksheet.

In other words, taking this same range from each worksheet and compiling all of the data into one long "list" in another worksheet. It would cover columns A:N but would vary in row length based on how many sheets of data need to be copied into it.

Can anyone help me write some code that will automate this process for me?

Thanks!

 

[OAbbl]

VBA - copy some columns and post into invoice

Im am creating a catalog and i have 36 different categories. What i am looking to do is transfer the QTY, Description, ItemID and Unit Price to transfer over from sheets 2-36 to transfer over to sheet 1 (invoice). I had someone helping me with the macros code but i think it needs some tweaking for it to fully work. I would love any additional help. I am also unsure how to post by excel file to get assistance. anyone who can help would be great.

Thank you,
OAbbl

 

[MARK858]

Re: VBA - copy some columns and post into invoice

A few things.
a) to post screenshots of your files. See the links in my signature
b) post the code you currently have
c) start a new thread

 

[OAbbl]

Re: VBA - copy some columns and post into invoice

Mark858,

Here is the code i was using..

Option Explicit
Option Base 1




Sub Update_Invoice()
'
' this macro takes selected items from the catalog and transfers
' the data to the invoice form.
'
Dim current_row As Integer
Dim number_of_products As Integer
Dim catalog_array_number As Integer
Dim catalog_items()
Dim invoice_items


Dim sheet_name As String


'
ReDim amature_items(36, 501)
ReDim invoice_items(36, 501)


ActiveWorkbook.Save

sheet_name = "Invoice"
Sheets(sheet_name).Select
'
' clear out previous data - ship to info first


For current_row = 8 To 12
Cells(current_row, 2) = ""
Next current_row

' clear out previous data - Credit Card info next


For current_row = 15 To 16
Range("A" & current_row & ":F" & current_row).Select
Selection.Clear
Next current_row

' clear out previous data - invoice lines next


For current_row = 18 To 44
Range("A" & current_row & ":F" & current_row).Select
Selection.Clear
Next current_row
'
' Now we take the data from the amateur
'
sheet_name = "Amateur"
Sheets(sheet_name).Select

For current_row = 2 To 43
If Cells(current_row, 1) <> "" Then
catalog_items(current_row, 1) = Cells(current_row, 1)
catalog_items(current_row, 2) = Cells(current_row, 2)
catalog_items(current_row, 3) = Cells(current_row, 10)
catalog_items(current_row, 4) = Cells(current_row, 13)
End If

Next current_row
'
' Now we paste the data into the invoice
'
sheet_name = "Invoice"
Sheets(sheet_name).Select

' catalog_array_number = 1
current_row = 18

For catalog_array_number = 1 To 43
If catalog_items(catalog_array_number, 1) <> "" Then
Cells(current_row, 1) = catalog_items(catalog_array_number, 1)
Cells(current_row, 2) = catalog_items(catalog_array_number, 2)
Cells(current_row, 5) = catalog_items(catalog_array_number, 3)
Cells(current_row, 6) = catalog_items(catalog_array_number, 4)
current_row = current_row + 1
End If

Next catalog_array_number



'
End Sub

 

[OAbbl]

Re: VBA - copy some columns and post into invoice

MARK858,

Here is the file i was working with. I hope this works

file:///C:/Users/walid/Desktop/BBL DVD Catalog.xlsm

Thank you for your help,
OAbbl

 

[warrenzm]

Mark858 -

Thanks so much for the help and I apologize for not knowing/not following proper board etiquette. Your suggested code is perfect. Much appreciated.

TTFN.

wzm

 

[abdulmusawir]

Hi , Im trying to copy range of cells , in tis case range B1:B22 as transpose in a master sheet.
I have an excel sheet of around 800 Worksheets - Im able to record a macro for copying the cells as transpose in to the master sheet
The Problem is,i am not aware of the code which will copy subsequent worksheet data onto a fresh cell(One below the other)

This will save me 10 Hrs of manual data entry from my, plz help me